Email reports using 2 addresses for several individuals

[ItsMe]

Post your code that you used when the error occurred. Use "Go Advanced" within this forum's tools and choose the hashtag # to enclose your code with quote tags (retain formatting).



Indicate which line of code is causing the exception. You can see which line this is by choosing Debug when the error occurs.

[Baldeagle]

#Private Sub Command62_Click() Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strEmail As String

strDelim = """" 'Delimiter appropriate to field type.
strDoc = "July Cheque Email"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
strWhere = ""
strDescrip = ""

If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
strEmail = Column(1, varItem)

End If

'Remove trailing comma. Add field name, IN operator, and brackets.
' lngLen = Len(strWhere) - 1
' If lngLen > 0 Then
' strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Email: " & Left$(strDescrip, lngLen)
End If
' End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation. ", True
DoCmd.Close acReport, strDoc
Next

End With
DoCmd.Close acReport, strDoc


End Sub#
The error message is as described earlier. Thanks for your patience.

[ItsMe]

change
strEmail = Column(1, varItem)

to
strEmail = .Column(1, varItem)

or
strEmail = Me.lstCategory.Column(1, varItem)

[Baldeagle]

Progress!! Below is the code that I have used -
#Private Sub Command62_Click() Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
Dim strEmail As String

strDelim = """" 'Delimiter appropriate to field type.
strDoc = "July Cheque Email"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
strWhere = ""
strDescrip = ""

If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible column.
strDescrip = strDescrip & """" & .Column(0, varItem) & """, "
strEmail = .Column(1, varItem)

End If

'Remove trailing comma. Add field name, IN operator, and brackets.
' lngLen = Len(strWhere) - 1
' If lngLen > 0 Then
' strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Email: " & Left$(strDescrip, lngLen)
End If
' End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.SendObject acSendReport, strDoc, acFormatPDF, strEmail, , , "Payment from Bangor Worldwide Missionary Convention", "Attached is a Notice of money sent to the Bangor Worldwide Missionary Convention for your Organisation. ", True
DoCmd.Close acReport, strDoc
Next

End With
DoCmd.Close acReport, strDoc


End Sub#

I had to comment out the DoCmdOpenReport line as well. But it has worked and sent the email to both email addresses. However the attachment wasn't the selected one. Can we now try the final step and sort out the Mission Reports please?

[ItsMe]

I think I was confused earlier. I thought one of the string variables was assigning an alias to the Email address

so try un-commenting the code previously commented

'Remove trailing comma. Add field name, IN operator, and brackets.
                  lngLen = Len(strWhere) - 1
                  If lngLen > 0 Then
                  strWhere = "[Name of Mission] IN (" & Left$(strWhere, lngLen) & ")"
                  lngLen = Len(strDescrip) - 2
                  If lngLen > 0 Then
                  strDescrip = "Email: " & Left$(strDescrip, lngLen)
                  End If
                  End If

[Baldeagle]

Brilliant!! Problem solved. Thanks for your patience and help - very much appreciated.

[ItsMe]

Sorry I got side tracked but I am glad you were able to resolve the issue.