Hack 69. Send Access Data Through Outlook Automatically

Implement bulk emailing of your data by tapping into Outlook objects.

The purpose of most databases is to store and report information. Often, it is necessary to send the reports that are generated by a database to multiple users. This doesn't have to be a manual process. By automating Microsoft Outlook from Access VBA, it is possible to automatically generate reports and send them via email.

The first item you need to determine is whether you are going to send emails only through your address book. If you decide to do that, you don't need to adjust any of the default settings in Outlook. If, however, you want to send to any address through your application, you need to make a change in Outlook.

By default, Outlook automatically checks the email addresses when you send an email. When you are doing this in an automated fashion, you will have errors to deal with if an email address doesn't exist in your address book. To shut off this feature in Outlook, go to the Tools Options dialog.

On the Options dialog, shown in Figure 7-46, click the E-mail Options button in the Preferences tab, and then click the Advanced E-mail Options button shown in Figure 7-47.

This action brings up a dialog box with three sections: "Save messages," "When new items arrive in my Inbox," and "When sending a message," as shown in Figure 7-48.

The "When sending a message" section contains a checkbox for "Automatic name checking," as shown in Figure 7-48. Check the box if you want Outlook to check addresses, and uncheck it if you want to simply send the messages without checking.

Now that you have determined how you want Outlook to handle addresses, you are ready to build email functionality into your application. Although you will eventually want to have reports based on parameterized queries that go to different users, this example shows how to send individual reports to multiple recipients.

Figure 7-46. Outlook's Options dialog

It should be noted that to deal with the increasing number of problems with viruses, Outlook prompts the user to allow access to the address book and to send the messages. Although this prevents you from sending email unattended, it is certainly much easier than doing everything manually every time. In older versions of Outlook, you can send multiple emails unattended.

To accomplish the email task, create a table called tbl_Email with two text fields: Email_Address (50 characters) and Report_Name (25 characters). You can make the fields larger if it is warranted. If you use automatic name checking, you just need to put in the display name of the people you want to send the messages to in the Email_Address field. If you aren't using automatic name checking, you need to enter the full email address. Put in two or three records for your test.

In a normal application environment, you would want this to be driven from a form; however, this example simply sends all the emails through a procedure.

Figure 7-47. The Advanced E-mail Options dialog

To create the procedure, go to the Modules tab in Access, and click New. Once you are in a blank module, go to Insert Procedure, make sure the radio boxes for Sub and Public are selected, and fill in SendOutlookEmail in the Name text box. This creates the shell for your procedure.

Now you need to create a reference to Microsoft Outlook. Do this by going to Tools References and checking the box for the version of Outlook that you have. Now you can reference the Outlook object model. If you use a version of Access other than Access 2003, you might need to check the box for Microsoft Data Access Objects (mine is Microsoft DAO 3.6 Object Library). Now you are ready to begin coding.

7.12.1. The Code

The code is shown in Example 7-10.

Figure 7-48. Changing how Outlook handles names and email addresses

Example 7-10.Access VBA code to send email

  Public Sub SendOutlookEmail() 
Dim db As DAO.Database 
Dim ReportRs As DAO.Recordset 
Dim EmailRS As DAO.Recordset

Dim olApp As Outlook.Application 
Dim olMail As Outlook.MailItem

Dim EmailColl As Collection 
Dim varEmail As Variant 
Dim FileName As String

' Outlook only allows one instance to be open at a time, 
' so you can call it with New and it will use the instance 
' that you already have open. I suggest having Outlook open 
' already so you are not prompted for user name or password. 
Set olApp = New Outlook.Application
Set db = CurrentDb
Set ReportRs = db.OpenRecordset( _
     "Select Report_Name from tbl_Email Group by Report_Name")

ReportRs.MoveFirst

While Not ReportRs.EOF 
  Set EmailColl = New Collection 
  Set EmailRS = db.OpenRecordset( _
    "Select Email_Address from tbl_Email Where Report_Name = " & """" & _
      ReportRs.Fields(0).Value & """" & ";")
  EmailRS.MoveFirst
  While Not EmailRS.EOF
    EmailColl.Add EmailRS.Fields(0).Value
    EmailRS.MoveNext
  Wend

  EmailRS.Close
  Set EmailRS = Nothing

  Set olMail = olApp.CreateItem(olMailItem)
  olMail.subject = "Monthly Report"
  For Each varEmail In EmailColl
    olMail.Recipients.Add varEmail
  Next
  olMail.Body = "Your Monthly Report is attached"
  FileName = "C:\Reports\" & ReportRs.Fields(0).Value & ".rtf"
  DoCmd.OutputTo acReport, ReportRs.Fields(0).Value, _
     acFormatRTF, FileName 
  ' If you had multiple attachments, you could add them one at a time 
  olMail.Attachments.Add FileName, olByValue, 1, "Monthly Report" 
  olMail.Send

  Set olMail = Nothing
  Set EmailColl = Nothing

  ReportRs.MoveNext 
Wend
  
  ReportRs.Close
  Set ReportRs = Nothing

  Set olApp = Nothing
  ' You can close Outlook with olApp.Quit - but since I suggested
  ' that you keep it open I am not closing it here

  Set db = Nothing 
End Sub

When you run the code, you will quickly become annoyed at the number of prompts you receive. As stated earlier, this is much better than doing it manually, but there has to be a better way.

The code requires several variables for the Outlook objects and data objects; see Example 7-10 for these items. This example also takes advantage of the Collection object; however, you can skip that step and just use the recordset. The main reason the code uses the Collection object is that, in my production-automated email applications, I pass Collections to the email procedure for the report names and the email addresses. This lets me use that same procedure in other Microsoft Office applications such as Excel or Word, where I might not be using recordsets. The procedure saves the reports in a directory called C:\Reports; if this directory doesn't exist on your system, you can create the directory, or you can place the reports in a different directory.

7.12.2. An Easier Way

Now that you are familiar with the items to send emails through Outlook, here is an easier way to handle it. Most likely this will be helpful only for large jobs because it requires two-steps.

The adjusted Access procedure in Example 7-11 changes the original code from Example 7-10 to save the email instructions in an ADO.Recordset XML file. Outlook then processes this file. You will need to create a reference to ADO in both the Outlook and Access VBA environments.

Example 7-11. Creating an XML file from an ADO recordset

Public Sub CreateOutlookXML()
Dim db As DAO.Database 
Dim ReportRs As DAO.Recordset 
Dim EmailRS As DAO.Recordset

Dim saveRS As ADODB.Recordset 
Set saveRS = New ADODB.Recordset

saveRS.Fields.Append "Email_Address", adVarChar, 50, adFldFixed 
saveRS.Fields.Append "File_Name", adVarChar, 50, adFldFixed 
saveRS.Open 
Dim FileName As String

Set db = CurrentDb 
Set ReportRs = db.OpenRecordset( _ 
     "Select Report_Name from tbl_Email Group by Report_Name")

ReportRs.MoveFirst
While Not ReportRs.EOF 
  FileName = "C:\Reports\" & ReportRs.Fields(0).Value & ".rtf"

Set EmailRS = db.OpenRecordset( _
  "Select Email_Address from tbl_Email Where Report_Name = " & """" & _
    ReportRs.Fields(0).Value & """" & ";")
EmailRS.MoveFirst
While Not EmailRS.EOF
  saveRS.AddNew
    saveRS.Fields(0).Value = EmailRS.Fields(0).Value
    saveRS.Fields(1).Value = FileName
  saveRS.Update
  EmailRS.MoveNext
Wend

EmailRS.Close
Set EmailRS = Nothing

DoCmd.OutputTo acReport, ReportRs.Fields(0).Value, _
   acFormatRTF, FileName

  ReportRs.MoveNext 
Wend

  saveRS.Save "C:\Reports\EmailFile.xml", adPersistXML
  saveRS.Close
  Set saveRS = Nothing
  ReportRs.Close
  Set ReportRs = Nothing

  Set db = Nothing 
End Sub

This procedure takes advantage of a disconnected ADO recordset. With ADO, you can create a recordset on-the-fly without connecting to a database. In addition, you might also notice that this procedure creates all the files Outlook will send later. If you want to, you can have a step that runs at the beginning of the process to create the XML file with no records and then have multiple procedures run that continue to add to the XML file to be processed by Outlook at a particular time.

7.12.3. Macros in Outlook

Next, you need to create the Outlook procedure. To make this work, you need to add a macro to your Outlook environment. In Outlook, select Tools Macros Visual Basic Editor, and click the ThisOutlookSession object in the Project Explorer. Once there, enter the code in Example 7-12.

Example 7-12. Processing the ADO recordset in Outlook

Public Sub EmailTest() 
Dim mi As MailItem 
Dim varitm As Variant 
Dim adors As ADODB.Recordset 
Set adors = New ADODB.Recordset 
adors.Open "C:\Reports\EmailFile.xml" 
adors.MoveFirst 
While Not adors.EOF 
Set mi = Application.CreateItem(olMailItem) 
mi.Recipients.Add adors.Fields(0).Value 
mi.Subject = "Monthly Report" 
mi.Body = "Your monthly report is attached." 
mi.Attachments.Add adors.Fields(1).Value, olByValue, 1, "Monthly Report" 
mi.Send 
Set mi = Nothing 
adors.MoveNext
Wend
adors.Close 
Set adors = Nothing 
End Sub

This sends all your emails without prompting you each time. Although it creates a two step process, you will appreciate not having to click through each message. This is particularly useful if you have a significant number of emails to send. If necessary, you can store additional fields for Subject and Body in the recordset and have those also become dynamic.

The one downside of this procedure is that it sends an individual email for each record. You can update it to go through the recordset and determine if emails can be grouped; however, this is unlikely to be necessary. In addition, you can also create multiple XML files for each email to be sent and have the procedure cycle through all the XML files and then move them when it is completed (I implemented such a procedure for a client once).

You will need to save this procedure using the Save icon from the Visual Basic Environment if you want to use it again. Also, depending on your security settings, you might be prompted to enable this macro each time you open Outlook and attempt to use it.

Using either approach will certainly help you tackle your Access projects and help automate sending emails. If you need to send just a message to users, you can use the first procedure and eliminate the lines related to attachments. In either case, the power of using VBA in Microsoft Office applications should be evident.

Michael Schmalz