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