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.
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.
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.