Using the SendObject method in Access to email individual reports
This is a short, sweet “tip” article.
I wrote it because of the number of times a related question is asked in the public newsgroups. The question usually goes something like this:
“I have a report which I email out to our department managers using the SendObject command. The report has a different header for each manager's department. How can I get the SendObject command to read email addresses from a query, and send each manager only his section of the report?”
Comment about SendObject
SendObject has its limitations. There are some potential emailing scenarios within Access where other approaches are called for. For example, sending file attachments. There is a fuller discussion at http://www.granite.ab.ca/access/email/sendobject.htm.
And for functionality such as that being sought here, SendObject does not support any type of Where Condition argument, such as is available, for example, with the OpenReport method. So, the object sent is the object sent.
If Outlook is your default email client, a security alert when trying to use SendObject can also be an impediment at times. However, I understand that some people have found ClickYes, a third-party utility available from http://www.contextmagic.com/express-clickyes/, has helped with this problem.
On the other hand, it is a relatively simple method to implement, and it is more than adequate to meet many requirements.
In this instance, though, we are not talking about the SendObject macro action. Cycling through a series of records is generally awkward in a macro, whereas looping through a recordset in a VBA procedure is much smoother.
So this is how I do it
So, without further ado, this is the skeleton of the idea…
So the code will look something like this:
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT ManagerID, Email FROM ManagersTable")
Set qdf = dbs.QueryDefs("YourReportQuery")
BaseSQL = qdf.SQL
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID =" & !ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport", "Snapshot Format", !Email
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing
It is fairly simple to use the SendObject method to email separate groupings of a report to the appropriate individual recipients.