What if you want to attach a report to an email that goes to all of your customers? You can write code to create the report and send it via the DoCmd.SendObject method. That will create the report and attach it to an email. But how do you get all the customers’ emails into the BCC line? (Always paste emails into the BCC or Blind Carbon Copy line, so you are not sharing email addresses.)
You can combine all the email addresses of your customers into one single string and pass that string to the email. Here’s my example. I have a table called Customers, and a field called EmailAddress. Each customer has a CustomerID.
This example is for demonstration purposes only.
Function CombineEmails(strTblQryIn As String, strFieldNameIn As String, _
varPKValue As Variant, Optional strDelimiter) As Variant
Set db = CurrentDb
Set qd = db.CreateQueryDef(“”)
If IsMissing(strDelimiter) Then strDelimiter = “; ”
strSQL = “SELECT [" & strFieldNameIn & "] FROM [" & strTblQryIn & "]”
qd.SQL = strSQL
Set rs = qd.OpenRecordset
Do Until rs.EOF
varResult = varResult & rs.Fields(strFieldNameIn).Value & strDelimiter
rs.MoveNext
Loop
rs.Close
If Len(varResult) > 0 Then varResult = Left$(varResult, Len(varResult) – 1)
CombineEmails = varResult
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function
Then, I use a query (we’ll call it “qryCombineEmails”) that passes my table name and my field name to the function and I get my resultant string:
SELECT Customers.EmailAddress, Customers.CustomerID, CombineEmails(“Customers”,”EmailAddress”,[PeopleID],”;”) AS EmailList
FROM Customers
WHERE (((Customers.EmailAddress) Is Not Null));
The CombineEmails field in this query is the concatenated string.
Now I can pass that string to my DoCmd line like this:
DoCmd.SendObject acSendReport, “MonthlyReport”, acFormatRTF, , , DLookup(“EmailList”, “qryCombineEmails”), “Here’s our monthly report.”
The dlookup finds the EmailList in qryCombineEmails and pastes it into the BCC line of the email. That way, I don’t have to type individual addresses into my email!
