Lookup email addresses for programmed email report

[Ken Smith]

I have an application that uses the following Code to send an email to
several people. For security purposes their email addresses have been
replaced by EmailAddr1, etc.

DoCmd.SendObject acSendReport, "Surety Release Request", acFormatSNP, _
"EmailAddr1", "EmailAddr2", "EmailAddr3", _
"Surety Release Request" & Forms("Request Letter of Credit
Release").ProjectRef, _
"Facilities covered by the surety for the subject project
have been final inspected and found satisfactory. Please release the surety
instrument described in the attachment.", intSeeOutlook


Rather than use hardcoded email addresses in the Code, I want to be able to
manage the email address collection saved in a single record table that uses
three text fields corresponding to Outlook's "To", "CC" and "BCC" fields. I'd
like the Code to lookup the email address string from the table.

Presuming the table is named tblEmail with fields named "EmailAddr1",
"EmailAddr2", "EmailAddr3",
what would the code look like where I now use email addresses instead of:

"EmailAddr1", "EmailAddr2", "EmailAddr3",


Thanks

 

[Brian]

Set up a variable to hold the string containing all the e-mail addresses,
replete with the commas. BTW, if you use commas to separate the list, you are
actually getting one "To" e-mail address, one CC, and one BCC. If you instead
want three "To" and no CC/BCC, use semicolons between the addresses and two
extra commas as placeholders for the CC & BCC arguments (that is how I have
done it below).


Dim EMailList as String
EMailList = DFirst("[EmailAddr1]","[tblEmail ]") & ";" &
DFirst("[EmailAddr2]";"[tblEmail ]") & ";" & DFirst("[EmailAddr3]","[tblEmail
]")
DoCmd.SendObject acSendReport, "Surety Release Request", acFormatSNP, _
EMailList, ,,_
"Surety Release Request" & Forms("Request Letter of Credit
Release").ProjectRef, _
"Facilities covered by the surety for the subject project
have been final inspected and found satisfactory. Please release the surety
instrument described in the attachment.", intSeeOutlook

I used DFirst assuming that there is only a single record in the tblEmail
table. If you need to find a correct record, you will need a WHERE clause in
the DFIRST to get to the correct record. If instead of FIELDS in a single
record, you want to pull mutliple RECORDS, open a recordset and loop through
it to populate the EMailList string.