DoCmd.sendObject - Recipient Email Address Format

[michellecole]

I am sending a report from a form in Access via email to a distribution list that will be built each time the report is run based on the data of the report - the distribution list can exceed over 100 email addresses. I am using MS Outlook 9.0 from MS Access 2000.

My first question is: Is there a specific email address format required? i.e. "Michelle.cole@businessname.com" vs. "Cole, Michelle". If the address is formatted as "michelle.cole@businessname.com; mcole@businessname.com; etc.", all is good. But if the address is formatted as "Cole, Michelle; Gray, Sabrina", I receive the following MS error msg, "Unknown Message Recipient(s); Message was not sent." If I go directly to Outlook, open a new mail message and plug in "Cole, Michelle; Gray, Sabrina" the message will send with no issue.

Here is my code:
*
*
Dim strTo 'The "To" recipients of the email message, separated by semicolon (
Dim strSubject 'The "Subject" line of the email message
Dim strText 'The "Text" of the email message

'Build the "To" distribution List...
'strTo = "Michelle.Cole@test1.com; mcole2@test2.com" 'this format works!
strTo = "Cole, Michelle; Gray, Sabrina"

strSubject = "Subject Line Info"
strText = "Body of Email"

DoCmd.SendObject acReport, stDocName, "SnapshotFormat(*.snp)", strTo, "", "", strSubject, strText, False, ""
*
*

(I am using .snp because the report has lines, shading, etc. and this format holds these elements.) I don't want to plug in "True" and allow the user to Edit the Message in production, but when I did it temporarily to see what was being plugged into the recipient line of the email when I was using "Cole, Michelle; Gray, Sabrina" - "Cole; Michelle; Gray; Sabrina" was what populated in the Recipient line (notice that the commas were converted to semicolons) causing the error.

Question #2: Is there a limit as to how many email addresses (or charachters) I can place in the variable "strTo"?

Thank you,
Michelle

 

[DoubleD]

I don't know about Outlook, but with Lotus the list of email addresses needs to be an array.

I am what I am based on the decisions I have made.

DoubleD

 

[michellecole]

Am I understanding you to say that I can't generate one email and send it to multiple (200) email addresses at one time, but that I have to generate an email and send it to only one email address then loop to the next email address record and generate an email and send it to that email address and so on and so on for up to 200 records?

Michelle

 

[DoubleD]

Sorry I wasn't clear. You can generate one email, but the email addresses are stored in an array, then the array is passed to the SendTo portion of the call.

I am what I am based on the decisions I have made.

DoubleD

 

[grande]

DoubleD, can you tell me how to send email to Lotus? Is it different? Do I need a different dll?

 

[DoubleD]

I have a separate module to send emails through Lotus Notes, it doesn't use the DoCmd.SendObject function.

Do you have both email programs available? I only ask because it's unusual for a company to use both.

I am what I am based on the decisions I have made.

DoubleD

 

[hermanlaksko]

Hi Michelle
I just happend to find your Q and as I have been looking for the same.... and found a/the solution :-D I decided to write an FAQ on the matter. I incl. this FAQ here for your convinience. Let me know how it turnes out.

Herman

How to send multiple mails:
The whole thing is controled via a form, one table and the below incl. code. You do not need to make any refrences to run the code.

The "Private Sub SendPost_Click()" is only incl. as an explanation of how to feed data into the function "SendMailMB".

I drop all recipients into my table and mail from this table: MergeMail.

First make a form in order to control the mail. The form should be made as a continues form, showing all recipiants. The form header should incl. a button to enable users to browse for attachments, choose importance of the mail and how to show the "To"-names in the actual mail i.e. to/cc/bcc. And last but not least the "SendPost"-button.

Last I have incl. the function IsBlank, this is a function that I use frequently in all my apps it checks for missing/isnull/isempty etc. and with success .... so far

If the whole thing blows up in your face, drop me a line and I will mail you an example base with the whole thing.

Private Sub SendPost_Click()
On Error GoTo Err_cmdSend_Click
Dim strTo As String, strCC As Boolean, strBCC As Boolean, CountOf As Integer, Frm As Form
Dim strFrom As String, strReplyTo As String, strAttachment As String, SRecept As String
Dim bOK As Boolean, Re As DAO.Recordset, strAttachment2 As String, ReAtt As DAO.Recordset

If DLookup("E_Mail", "Company") = "" Then
MsgBox "Sender not found.", vbCritical + vbOKOnly, "YrAppName"
DoCmd.OpenForm "Company"
Exit Sub
Else
strFrom = DLookup("Hoved1", "Company") & "<" & DLookup("E_Mail", "Firma") & ">"
strReplyTo = DLookup("E_Mail", "Company")
End If
Set Frm = Forms!Merge!MergeMail.Form 'If you decide to use a subform
strCC = Me!CC
strBCC = Me!BCC
If IsBlank(Frm.Subject) Then Frm.Subject = "News from" & DLookup("Hoved1", "Company")

If IsBlank(Frm.EM_Text) Then Frm.EM_Text = "Please find attached file from " & DLookup("Hoved1", "Company")

Set Re = CurrentDb.OpenRecordset("Select * From MergeMail Where Email Is not null")
If Re.RecordCount = 0 Then
MsgBox "No recipients was found.", vbCritical + vbOKOnly, "YourAppName"
Exit Sub
End If
Do While Not Re.EOF
SRecept = SRecept & Re!PName & "<" & Re!Email & ">; "
CountOf = CountOf + 1
Re.MoveNext
Loop
SRecept = Left(SRecept , Len(SRecept ) - 2)
bOK = SendMailMB(SRecept , strFrom, Frm.Subject, Frm.EM_Text, strCC, strBCC, strReplyTo, strAttachment, "", Me!Priority, Me!HTML)
If bOK Then
MsgBox CountOf & " Email(s) was sendt", vbInformation, "YrAppName"
Else
MsgBox "Error: Email could not be sendt to selected recipients", vbCritical, "YrAppName"
End If
Exit_cmdSend_Click:
Exit Sub
Err_cmdSend_Click:
MsgBox Err.Description
Resume Exit_cmdSend_Click
End Sub

'This does the actual mailing
Function SendMailMB(sTo As String, _
sFrom As String, _
sSubject As String, _
sBody As String, _
Optional sCC As Boolean, _
Optional sBCC As Boolean, _
Optional sReplyTo As String = "", _
Optional sAttachment As String = "", _
Optional sAttachmentAlias As String = "", _
Optional sPriority As Integer = 1, _
Optional sHTML As Boolean) As Boolean
On Error GoTo Fejl 'Resume Next
Dim CCOk As Boolean, objEmail As Object
Set objEmail = CreateObject("CDO.Message")

objEmail.From = sFrom
If sCC Then
objEmail.CC = sTo
CCOk = True
ElseIf sBCC Then
objEmail.BCC = sTo
CCOk = True
End If
If Not IsBlank(sTo) And Not CCOk Then objEmail.To = sTo
objEmail.Fields("urn:schemas:httpmail:importance").Value = sPriority
objEmail.Fields.Update 'opdates priority Hmmm objEmail.Subject = sSubject
If sHTML Then objEmail.HTMLBody = sBody Else objEmail.Textbody = sBody

Set Re = CurrentDb.OpenRecordset("SELECT * FROM AttFiles Where FileID <> '001'", dbOpenDynaset)
If Re.RecordCount > 0 Then
Do While Not Re.EOF
If Not IsBlank(Re!FilName) Then objEmail.AddAttachment Re!FilName
Re.MoveNext
Loop
End If
objEmail.Send
If Err Then SendMailMB = False Else SendMailMB = True
FejlExit:
Exit Function
Fejl:
MsgBox Err.Description, , "YrAppName"
Resume FejlExit
End Function

Function IsBlank(V As Variant) As Boolean
On Error Resume Next
V = "" & V
If Len(V) = 0 Then IsBlank = True
End Function


Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[michellecole]

Herman, thank you for your response. I had actually developed the following solution after trying several test scenarios; I'll add my findings below for you or anyone else who may find them useful. My goal was to send only one single email to many recipients. The recipients are determined by the user on a form - None, ALL or Some are checked (some being only those who are linked for a cost center actually appearing on the report that is being attached to the email).

Thank you,
Michelle

Sub cmdMail_Click()

'***********************************************************
/*02/2005 mcole - Automated Distrubution Functionality added to utility....
This event will create the report selected by user, attach it to a single email and send it to the recipients selected by user. The DoCmd "Edit Msg" is set to "False" (NOT allowing the user to edit messages before they are sent). Therefore, email addresses need to be formatted "michelle.cole@commercebank.com" rather than "Cole, Michelle", because if the email address is formatted as: "Cole, Michelle", then if only one name in
the email distribution list is not recognized by Outlook (i.e. "Col, Michelle"), the email will not be sent to anyone and the incorrect name that prevented the email from being sent does not display. However if the email address is formatted as: "michelle.cole@commercebank.com", then
as long as an "@" and a "." are present in the email address, the email will be sent and the System Administrator will advise the sender of unsent messages.
This is the methodology selected by the Project Manager.

Email testing results...
successfully sent over 400 email addresses, ";" ok on the end of final email address, "@" ok at the
beginning of an email address, multiple "."'s and "@"'s in a single email address, multiples of
same email address ok - only one actually sends.*/
'*********************************************************
On Error GoTo Err_cmdMail_Click

If MsgBox("Are you sure you want to email this report to all Recipients indicated?", "4", "Confirm Report Distribution...") = vbYes Then

Dim stDocName As String
Dim strText As String 'The "Text" of the email message
Dim strSubject As String 'The "Subject" line of the email message
Dim intCostCenter As Long

stDocName = cboReports
strTo = ""
strSubject = ""
strText = ""
strNotSentList = ""
sql = ""

intCostCenter = 0

dteStartDate = CDate("01/01/2004") 'date of first record in tblErrors
dteEndDate = Date

If [Forms]![frmReporting]![txtStartDate].Visible = True Then
dteStartDate = CDate([Forms]![frmReporting]![txtStartDate])
dteEndDate = CDate([Forms]![frmReporting]![txtEndDate])
End If

If [Forms]![frmReporting]![cboCostCenter].Visible = True Then
intCostCenter = ([Forms]![frmReporting]![cboCostCenter].Value)
End If

'*********************************************************
'*
'* Build the "To" and "NotSent" Distribution Lists.
'*
'***********************************************************

If Forms!frmReporting!frameTellerSupervisor.Value = 1 Then 'Send To All Teller Supervisors
sql = "SELECT DISTINCTROW lkpTellerSupervisor.EmployeeName, lkpTellerSupervisor.EmailAddress FROM lkpTellerSupervisor GROUP BY lkpTellerSupervisor.EmployeeName, lkpTellerSupervisor.EmailAddress;"
ScoopTheLoop
ElseIf Forms!frmReporting!frameTellerSupervisor.Value = 2 Then 'Send only to those with costcenter on report
If [Forms]![frmReporting]![cboCostCenter].Visible = True Then 'A single CostCenter was selected
sql = "SELECT DISTINCTROW lkpTellerSupervisor.EmailAddress, lkpTellerSupervisor.EmployeeName FROM tblErrors INNER JOIN lkpTellerSupervisor ON tblErrors.CostCenter = lkpTellerSupervisor.TellerCC WHERE (((tblErrors.Date) Between #" & dteStartDate & "# And #" & dteEndDate & "# )) AND (tblErrors.CostCenter = " & intCostCenter & ") GROUP BY lkpTellerSupervisor.EmailAddress, lkpTellerSupervisor.EmployeeName;"
Else 'All CostCenters were selected...
sql = "SELECT DISTINCTROW lkpTellerSupervisor.EmailAddress, lkpTellerSupervisor.EmployeeName FROM tblErrors INNER JOIN lkpTellerSupervisor ON tblErrors.CostCenter = lkpTellerSupervisor.TellerCC WHERE (((tblErrors.Date) Between #" & dteStartDate & "# And #" & dteEndDate & "# )) GROUP BY lkpTellerSupervisor.EmailAddress, lkpTellerSupervisor.EmployeeName;"
End If
ScoopTheLoop
ElseIf Forms!frmReporting!frameTellerSupervisor.Value = 3 Then 'Do Not Send
'do nothing
End If

'Build the Subject Line...
strSubject = "Proof Error Report: " + CStr([Forms]![frmReporting].[txtStartDate].Value) + " - " + CStr([Forms]![frmReporting].[txtEndDate].Value)

'Build the Body of the email...
strText = "You are receiving the attached Proof Department Error report because you " & _
"have a branch cost center that has submitted work to the Proof Department " & _
"that included one or more errors. These errors can lead to processing " & _
"delays and unnecessary adjustments to our customers. Please review the " & _
"attached report for your cost center(s) and address the errors as necessary."

'Send the Email...
DoCmd.SendObject acReport, stDocName, "SnapshotFormat(*.snp)", strTo, "", "", strSubject, strText, False, ""

MsgBox ("The report has been sent."), vbOKOnly, "Send Confirmation."

If Len(strNotSentList) > 1 Then
MsgBox ("Due to a missing or unacceptable emailaddress, the report was not sent to the following individuals: " + strNotSentList), vbOKOnly, "Not Sent List:"
End If
Else
MsgBox ("Action was cancelled."), vbOKOnly, "Action was Cancelled."
End If

Exit_cmdMail_Click:
Exit Sub

Err_cmdMail_Click:
MsgBox Err.Description
Resume Exit_cmdMail_Click

End Sub
Sub ScoopTheLoop()

Set con = Application.CurrentProject.Connection
Set rs = con.Execute(sql)

If Not rs.EOF Then 'Loop through rs, check for correct email format and build the distribution lists...
Do Until rs.EOF
If IsNull(rs("EmailAddress")) Or (rs("EmailAddress")) = "" Then 'Email Address is blank
strNotSentList = strNotSentList + rs("EmployeeName") + "; "
Else 'is there an "@" and a "." in email address? if not, the email address will cause the email to not be sent
If (rs("EmailAddress")) Like "*@*" Then 'found at least one "@" in the string
If (rs("EmailAddress")) Like "*.*" Then 'found at least one "." in the string, make sure the . is not the first character of the emailaddress...
If Mid(rs("EmailAddress"), 1, 1) = "." Then
strNotSentList = strNotSentList + rs("EmployeeName") + "; "
Else 'Good email address format, add it to the "To" list
strTo = strTo + Trim(rs("EmailAddress")) + ";"
End If
Else
strNotSentList = strNotSentList + rs("EmployeeName") + "; "
End If
Else
strNotSentList = strNotSentList + rs("EmployeeName") + "; "
End If
End If
rs.MoveNext
Loop
End If
Set rs = Nothing
End Sub

 

[hermanlaksko]

Hi Michelle

Ok - fine its working for you, good stuff

Looking at your code I stumpled over one thing that could make the code run a little faster:

Change the: SELECT DISTINCTROW ....
To: SELECT ....

The "DISTINCTROW" selecetion is looking for distinct values, but since you are using the "Where" clause the desintct can be omitted, hence making the code run a bit faster.

Herman

They say that crime doesn't pay... does that mean my job is a crime?