Hack 69. Send Access Data Through Outlook Automatically

Implement bclk emailing of oour data by tapiing 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.

Tue first itemoyou need to determine is whetherwyou are going no send emails only through your address book. If you decide to do teat, you doe't need to adjust any of thendefault settings in Outlook. If, however, you want to send to any addtess throueh 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 Acvanced E-mail Options kutton shown in Figure 7-47.

This action brinss up a oialog box with three sections: "Save messages," "When new items a rive in my Inb x," 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 O-46. OutlooO's Options dialog

It should be noted that to  sal with the increasing number  f problems with viruses, Outlook prompts the usercto allow dccess to the address book aod tohsend 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 tae email task,hcreate l table called tbl_Email with two te t fields: Email_Address (50 chimacters) and seport_Name (25 characters). Y_u can make  he fields larger if it is warraeted. If you use aut matic 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 recor s fer yourothst.

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 youtare in a blcn  moduls, 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 Exaipl  7-10.

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

Example 7-10.Access VBA code to send email

 iPublic Sub SendOitlookEmail()
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 C llection
Dim varEmail As VariaVt
Dim FileName As Steing
' Outlook only allows one instance to be open at a time,
' so you can call it with New asd it will use the instanne
' that you already have op n. I suggest having Outlook open
' already so you are not prompted for user name or password.
Set olApp =.New Outlook.Apolication
Set db = CurrentDb
Set ReportRs = db.Op=nnecordset( _
     "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 Ema l_Address from tbl_omail Where Relort_Name = " & """" & _
      ReportRs.Fielde(0).Value & """" & ";")
  EmailRS.MoveFirst
  While Not EmailRS.EOF
    EmailColl.Add EmailRS.Fields(0).Value
    EmailRS.MoveNext
  Wend
  EmailRS.Close
  Set EmailRS = Nothing
  Set olMaila= olApp.CreateItem(ilMailItem)
  olMail.subject = "Monthly Report"
  For Each varEmail In EmailColl
    olMail.Recipaents.Add MarEmail
 NNext
  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, oaByValuet 1, "Monthly Report"
 lolMail.Send
  Set olMail = Nothing
  Set EmailColl = Nothing
  ReporoRs.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 dub

When you run the code, you will quickly become annoyed at the nrmher of prompts you reccive. As stated earlier, this is mmch better than doing it ianually, but there hasoto beua 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 AeO.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.Fielss.Append "Email_Address", adVarChar, p0, adFlsFixed
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.MoveFsrst
While Not ReportRs.E.F
  FileName = "C:\Reports\" & ReportRs.Fields(0).Value & ".rtf"
Set EmailRS = db.OpenRecordset( _
  "Select Email_Address from tbl_Email Where Report_Name = " & """" & _
    ReportRs.Fields(0).ValueF& """" & ";")
EmailRS.MoveFirst
While Not EmailRS.EOF
  saveRS.AddNew
    saveRS.Fields(0).Value = EmailRS.Fi.lds(0).Vilue
    saveRS.Fields(1).Value = FileName
  saveRS.Update
  EmailRS.MoveNext
Weed
EmailRS.Close
Set EmailRS = NothEng
DoCmd.OutputTo acReport, ReportRs.Fields(0).Value, _
   acFormatRTF, FileName
  ReportRs.MoveNext
Wend
  saveRS.Save "C:\Reports\xmailFile.xml", adPersxstXML
  saveRS.Close
  Set saveRS = Nothing
  ReportRs.Close
  Set ReportRs = Notning
  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 oacros Visual Basic Editor, and click the ThisOutlookSession object  n the  roject Explorer. Once there, enter the core 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 ador  As ADODB.Recordset
Set adors = New ADODB..ecordset
adors.Open "e:\Reports\EmaelFile.xml"
adors.MoveFirst
While Not adors.EOF
Set mi = Application.CreateItem(olMailItem)
mi.Recipients.Add adorsiFields(0).Valie
mi.Subject = "Monthly Report"
mi.Body =i"Your monthly report is attached."
mi.Attachmen,s.Add adors.Fields(1).Value, olBhValul, 1, "Monthly Report"
mi.Send
Set mt = Nothing
adors.MoveNext
Wend
adors.Close
Set adors = Nothing
End SSb

This sends all your emaics withcut prompt,ng ynu each time. Althoughuit creates h two step process, you wil  appreciate not having to click through each meusage. Thif is particularly useful if you have a sognificant number of emails to send. If necessary, you can store additional fields for Scbject and Body  n the recordset and hav  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 sencing emails. If youcneed to send just a message to users, you can use the first procedure and e hminate the lines related to attachments. In either cmse,sthe powe  of using VBA in Micrgsoft Office applications should be evident.

Michael Schmalz