I've taken this task almost all the way to the finish line, but I've hit a snag at the end. Here is the code I am using to build my list.
Code:
Function BuildEmailList()
Dim strSQL As String, rst As Recordset, strEmailList As String
BuildEmailList = ""
strSQL = "Select * from qryContactOrganization WHERE Closed = 0 AND (Email <> '' or not isnull(Email))"
Set rst = CurrentDb.OpenRecordset(strSQL)
With rst
.MoveFirst
Do While Not .EOF
strEmailList = strEmailList & !EMail & "; "
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
BuildEmailList = strEmailList
End Function
I have printed this list in the immediate window and copied it into an email. It appears to recognize most addresses as valid email addresses, but for a couple of exception. Spaces are showing up in my string about every 40 email addresses. There are around a 1,000 total addresses. When I paste it into a word document it breaks the list up into similar groups with carriage returns between them. Pasting into Excel puts the same groups in different rows. I hoping you can tell by these clues what the problem might be. When I use this string in a sendObject command I get an error.
Runtime error 2295:
Unknown message recipients(s): the message was not sent.
Code:
DoCmd.SendObject acSendReport, strReportName, acFormatPDF, strEmailList, , , strReportName & _
"_" & Me.txtOrganizationName & "_" & Format(Date, "yyyymmdd") & ".pdf" 'Me.Name & " - " & Me.Name
This is the first time I've tried to build an address list like this. I'm hoping you can find the fly in my ointment.