Multi list to select reports send by email

[Mark0123]

Hi all,

I am trying to create a Multi list to select reports to send by email. I am able to select one report to send but when I change the list box to multi select it doesn't work.

The codes I have been using are;

Private Sub Command2_Click()
On Error GoTo ErrorHandler
Dim sAddr As String, sSubj As String, sFor As String
If IsNull(ReportList) Then Exit Sub
Again:
sAddr = InputBox("What is the e-mail address?")
If InStr(sAddr, "@") = 0 Or InStr(sAddr, ".") = 0 Then GoTo Again
sSubj = "Report"
sFor = Left(sAddr, InStr(1, sAddr, "@") - 1)
If MsgBox("Email a TXT version (Y) or PDF version (N)?", vbYesNo) = vbYes Then
DoCmd.SendObject acSendReport, ReportList, acFormatTXT, _
sAddr, , , sSubj, "Attached is a copy of " & ReportList
DoEvents
Else
DoCmd.SendObject acSendReport, ReportList, acFormatPDF, _
sAddr, , , sSubj, "Attached is a copy of " & ReportList
DoEvents
End If
If MsgBox("Make your own PDF copy?", vbYesNo) = vbYes Then
DoCmd.OpenReport ReportList, acViewPreview
SendKeys "+{F10}", False 'Close menu
SendKeys "E", False 'calls Export menu
SendKeys "P", True 'calls PDF file option
End If
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 2501
MsgBox "Email message was Cancelled."
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select

End Sub

Private Sub Form_Load()
Dim i As Integer, sName As String
ReportList.RowSourceType = "Value List"
ReportList.RowSource = ""
For i = 0 To CurrentProject.AllReports.Count - 1
sName = CurrentProject.AllReports(i).Name
If Left(sName, 3) = "rem" Then ReportList.AddItem sName
Next i
End Sub

Thank you in advance
Mark

 

[shoji]

You should do something like this:

Dim I As Integer
For I = 0 To ReportList.ListCount - 1
    If ReportList.Selected(I) = True Then

        DoCmd.SendObject acSendReport, ReportList, acFormatTXT, _
                      sAddr, , , sSubj, "Attached is a copy of " & ReportList(I)
        DoEvents
    Else
        DoCmd.SendObject acSendReport, ReportList, acFormatPDF, _
                      sAddr, , , sSubj, "Attached is a copy of " & ReportList(I)
        DoEvents
    End If
Next I

You can take a look at the following page:
https://msdn.microsoft.com/en-us/library/office/ff823015.aspx

On a different note, isn't it a bit messy to ask those questions by InputBox and MsgBox? Why can't you ask them on the form with Checkboxes, Textbox, etc.? Just a thought.

Good luck

Shoji

 

[pr2-eugin]

Please for the love of Pete, Please use Code Tags when posting VBA Code

 

[shoji]

Thanks. This is exactly what I needed.

 

[Mark0123]

thank you very much