I'm using this code provided by Daniel Pineault to automate the sending of emails with attachments.
When I use the following:
Code:
Dim varRec As Variant
Dim strSubject As String
Dim strBody As String
Dim varAttachment As Variant
varRec = ""
varRec = Array("Recipient1@Somewhere.org", "Recipient2@Somewhere.org","Recipient3@Somewhere.org","Recipient4@Somewhere.org")
varAttachment = Array("C:\Users\whammett\Documents\TestAttachment.xlsx")
strSubject = "TEST"
strBody = "This email is testing new report distribution automation. Please just delete, no other action is required."
Call SendOutlookEmail(varRec, strSubject, strBody, False, , varAttachment)
All email addresses resolve and are accepted by outlook.
However, when I tried to do this:
Code:
Public Function GetEmailDistribution(strFacility As String, intReportID As Integer) As Variant
Dim strSQL As String
Dim rstRecipients As DAO.Recordset
Dim strRec As String
strSQL = ""
strSQL = strSQL & "SELECT ReportUserEMail FROM tblReportUsers INNER JOIN tbxPFSReportUser ON tblReportUsers.ReportUserID = tbxPFSReportUser.ReportUserID"
strSQL = strSQL & " WHERE [tbxPFSReportUser].[PFSReportID] = " & intReportID & " And [tbxPFSReportUser].[" & strFacility & "] = True"
strSQL = strSQL & " ORDER BY tblReportUsers.ReportUserEMail;"
Set rstRecipients = CurrentDb.OpenRecordset(strSQL, dbReadOnly)
strRec = ""
If Not rstRecipients.EOF And Not rstRecipients.BOF Then
Do Until rstRecipients.EOF
strRec = strRec & rstRecipients(0) & "; "
rstRecipients.MoveNext
Loop
End If
GetEmailDistribution = Left(strRec, Len(strRec) - 2)
And I change the code to:
Code:
varRec = Array(GetEmailDistribution,”SHB”,1)
Outlook doesn’t seem to resolve the email addresses by the time the .Send command is initiated as I get a message box saying “Microsoft Outlook Does not Recognize” and then lists the email addresses until it runs out room for the characters. I click cancel and then tab out of the To field of the email and the addresses are recognized.
If I step through the code and wait a moment or two before allowing the .Send commend to resume, the addresses resolve as well.
Any help would be appreciated.