Home

Using the SendObject method in Access to email individual reports

Introduction

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…

1. Produce your report for all members.
2. Hold the query that the report is based on (‘BaseSQL’ in the code below).
3. Open a recordset to provide the recipients.
4. Loop through this recordset, and for each recipient:
1. Change the query that the report is based on to include an Where clause, to individualise it.
2. Email the individualised report to that recipient.
5. Return the query to its previous state (without Where clause).

So the code will look something like this:

Dim dbs As DAO.Database
   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
  With rst
      Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID =" & !ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport", "Snapshot Format", !Email
          .MoveNext
      Loop
      .Close
  End With
qdf.SQL = BaseSQL
  Set qdf = Nothing
  Set rst = Nothing
  Set dbs = Nothing

Summary

It is fairly simple to use the SendObject method to email separate groupings of a report to the appropriate individual recipients.

Steve Schapel