Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003

Frank Rice
Microsoft Corporation

February 2004

Applies to:
    Microsoft® Office Access 2003
    Microsoft Office Outlook® 2003

Summary: Automation is the process of controlling one program from another program. Learn how to use automation to use Microsoft Office Outlook 2003 in your Microsoft Office Access 2003 applications. (11 printed pages)

Introduction
Send an Outlook Message Using Access
Add Appointments to Outlook
Add a Task or a Reminder to Outlook
Create a Contact Item in Outlook
Conclusion

Download odc_ac_olauto.exe.

Automation is the process of controlling one product from another product with the result that the client product can use the objects, methods, and properties of the server product. For example, in the Microsoft® Office System, with automation, you use Microsoft Office Access 2003 to gain access to the methods in Microsoft Office Outlook® 2003 to perform such actions as sending an e-mail message, creating an appointment, or setting up a reminder. This article discusses these scenarios in more detail providing Microsoft Visual Basic® for Applications (VBA) code to illustrate the different techniques.

Note   This article consolidates material from Microsoft Knowledge Base articles. For more information, search the Microsoft Knowledge Base.

Sending an e-mail from Outlook is one of the fundamental actions that you may need to accomplish for your application. This section shows you how to use automation to create and send an Outlook message from Access.

You can use the SendObject method to send a MAPI mail message programmatically in Access. However, the SendObject method does not give you access to complete mail functionality, such as the ability to attach an external file or set message importance. The example that follows uses automation to create and send a mail message that you can use to take advantage of many features in Outlook that are not available with the SendObject method.

There are six main steps to sending an Outlook mail message by using automation, as follows:

1. Initialize the Outlook session.
2. Create a message.
3. Add the recipients (To, CC, and BCC) and resolve their names.
4. Set valid properties, such as the Subject, Body, and Importance.
5. Add attachments (if any).
6. Display and send the message.

1. Create a sample text file named Customers.txt in the My Documents folder.

2. Start Access, and create a database named Automation.mdb.

   Note   The samples in this article use the Automation.mdb database that is included as a downloadable file with this article.

3. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit
   

4. On the Tools menu, click References.

5. In the References box, click to select the Microsoft Outlook 11.0 Object Library, and then click OK.

   Note   If the Microsoft Outlook 11.0 Object Library does not appear in the Available References box, do the following:

   • In Windows Control Panel, double-click Add or Remove Programs.
   • In the list of installed programs, select Microsoft Office 2003, and then click Change. Microsoft Office 2003 Setup starts in maintenance mode.
   • Click Reinstall or Repair, and then click Next.
   • Click Detect and Repair errors in my Office installation, and then click Install.

6. Click OK to close the References dialog box.

7. Type or paste the following VBA procedure in the new module:

   Sub sbSendMessage(Optional AttachmentPath)
      Dim objOutlook As Outlook.Application
      Dim objOutlookMsg As Outlook.MailItem
      Dim objOutlookRecip As Outlook.Recipient
      Dim objOutlookAttach As Outlook.Attachment
   
      On Error GoTo ErrorMsgs
   
      ' Create the Outlook session.
      Set objOutlook = CreateObject("Outlook.Application")
      ' Create the message.
      Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
      With objOutlookMsg
         ' Add the To recipient(s) to the message. Substitute
         ' your names here.
        Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
         objOutlookRecip.Type = olTo
         ' Add the CC recipient(s) to the message.
         Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
         objOutlookRecip.Type = olCC
         ' Set the Subject, Body, and Importance of the message.
         .Subject = "This is an Automation test with Microsoft Outlook"
         .Body = "Last test." & vbCrLf & vbCrLf
         .Importance = olImportanceHigh  'High importance
         ' Add attachments to the message.
         If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
         End If
         ' Resolve each Recipient's name.
         For Each objOutlookRecip In .Recipients
            If Not objOutlookRecip.Resolve Then
               objOutlookMsg.Display
   .........End If
         End If
         Next
         .Send
      End With
      Set objOutlookMsg = Nothing
      Set objOutlook = Nothing
      Set objOutlookRecip = Nothing
      Set objOutlookAttach = Nothing
   ErrorMsgs:
      If Err.Number = "287" Then
         MsgBox "You clicked No to the Outlook security warning. " & _
         "Rerun the procedure and click Yes to access e-mail" & _
         "addresses to send your message. For more information, & _
         "see the document at http://www.microsoft.com/office" & _
         "/previous/outlook/downloads/security.asp. " "
      Else
         Msgbox Err.Number, Err.Description
      End If
   End Sub
   

   Note   Because of the Outlook E-Mail Security Update, when you run the following procedures, you will be prompted several times for permission to access your e-mail addresses and one prompt to send your message (see the following screen shots). This is expected behavior. For more information, see Security Features for Outlook 2002 and Previous Versions.

   

   Figure 1. Prompt to access e-mail addresses

   

   Figure 2. Prompt to send e-mail message

8. To test this procedure, type the following line in the Immediate window in the Visual Basic Editor, and then press ENTER:

   sbSendMessage "C:\Documents and Settings\UserName\My Documents\Customers.txt"
   

9. To send the message without specifying an attachment, omit the argument when calling the procedure, as follows:

   sbSendMessage
   

This section shows you how to create appointments in an Access database and then how to use automation to add the appointments to an Outlook calendar.

1. Start Access, and open the sample database Automation.mdb created earlier.

2. Use the following information to create a table named tblAppointments:

   Expand table

   Field Name	Data Type	Field Size	Size	Format	Default	Required
   Appt	Text		50			Yes
   ApptStartDate	Date/Time			Short Date		Yes
   ApptEndDate	Date/Time			Short Date		Yes
   ApptTime	Date/Time			Medium Time		Yes
   ApptLength	Number	Long Integer			15	Yes
   ApptNotes	Memo					No
   ApptLocation	Text		50			No
   ApptReminder	Yes/No					No
   ReminderMinutes	Number	Long Integer			15	No
   AddedToOutlook	Yes/No					No

3. Set the ApptDate and ApptStartTime fields as the composite primary key. Close and save the table as tblAppointments.

   Note   In this example, the primary key in the appointment table is made up of the appointment date and time fields. You can remove or change the primary key if you want to be able to add multiple appointments for the same date and time.

4. Use the AutoForm: Columnar Wizard to create a form that is based on tblAppointments table, and then save the form as frmAppointments.

5. Open the frmAppointments form in Design view, and then change the following form properties:

   • Form property - Caption: Appointment Form
   • Form Header - Height: .5"
   • AddedToOutlook Checkbox - Enabled: No

6. Add a command button to the form header section, and then set the following properties:

   • Name: cmdAddAppt
   • Caption: Send to Outlook
   • Width: 2"

7. Set the OnClick event of the command button to the following event procedure:

   Private Sub cmdAddAppt_Click()
       On Error GoTo Add_Err
       'Save record first to be sure required fields are filled.
       DoCmd.RunCommand acCmdSaveRecord
       'Exit the procedure if appointment has been added to Outlook.
       If Me!AddedToOutlook = True Then
           MsgBox "This appointment is already added to Microsoft Outlook"
           Exit Sub
       'Add a new appointment.
       Else
           Dim objOutlook As Outlook.Application
           Dim objAppt As Outlook.AppointmentItem
           Dim objRecurPattern As Outlook.RecurrencePattern
           Set objOutlook = CreateObject("Outlook.Application")
           Set objAppt = objOutlook.CreateItem(olAppointmentItem)
           With objAppt
               .Start = Me!ApptDate & " " & Me!ApptTime
               .Duration = Me!ApptLength
               .Subject = Me!Appt
               If Not IsNull(Me!ApptNotes) Then .Body = Me!ApptNotes
               If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
               If Me!ApptReminder Then
                   .ReminderMinutesBeforeStart = Me!ReminderMinutes
                   .ReminderSet = True
               End If
               Set objRecurPattern = .GetRecurrencePattern
   
               With objRecurPattern
                   .RecurrenceType = olRecursWeekly 
                   .Interval = 1
                   'Once per week
                   'You can hard-wire in these dates or get the 
                   'information from text boxes, as used here.
                   '.PatternStartDate = #12/1/2003#
                   .PatternStartDate = Me!ApptStartDate
                   '.PatternEndDate = #12/30/2003#
                   .PatternEndDate = Me!ApptEndDate
               End With
               .Save
               .Close (olSave)
               End With
               'Release the AppointmentItem object variable.
               Set objAppt = Nothing
       End If
       'Release the object variables.
       Set objOutlook = Nothing
   ....Set objRecurPattern = Nothing
       'Set the AddedToOutlook flag, save the record, display 
       'a message.
       Me!AddedToOutlook = True
       DoCmd.RunCommand acCmdSaveRecord
       MsgBox "Appointment Added!"
       Exit Sub
   Add_Err:
       MsgBox "Error " & Err.Number & vbCrLf & Err.Description
       Exit Sub
   End Sub
   

8. On the Tools menu, click References.

9. In the References dialog box, click to select the Microsoft Outlook 11.0 Object Library check box.

10. Click OK to close the References dialog box.

11. Save the form as frmAppointments, open it in Form view, and then add the following information to create a new appointment record:

    • Appt: Budget Meeting
    • ApptStartDate: <add tomorrow's date>
    • ApptEndDate: <add tomorrow's date>
    • ApptTime: 2:00 PM
    • ApptLength: 120
    • ApptNotes: To begin discussion of next year's budget.
    • ApptLocation: Conference Room
    • ApptReminder: <check the box>
    • ReminderMinutes: 15

    NOTE   Add ApptLength in minutes, not in hours. Note that in this example, ApptLength is set to 120 minutes instead of to 2 hours.

    

    Figure 3. The frmAppointments form

12. Click Send To Outlook, start Outlook, and view your calendar for tomorrow's appointments.

At times, you may want to add a task or a reminder programmatically to Outlook from Access. This section provides sample code that enables you to create a task and play a .wav file as a reminder

Note   In the following code, you must point the .ReminderSoundFile property to a valid sound file on your hard disk. This sample uses the file C:\WINNT\Media\Ding.wav.

To add a task or a reminder to Outlook, follow these steps:

1. Open the sample database Automation.mdb created earlier, and then create an additional module.

2. On the Tools menu, click References.

3. In the Available References dialog box, click to select the Microsoft Outlook 11.0 Object Library check box, and then click OK.

4. Click OK to close the Available References dialog box.

5. Type or paste the following VBA procedure:

   Option Compare Database
   Option Explicit
   Function fncAddOutlookTask()
       Dim OutlookApp As Outlook.Application
       Dim OutlookTask As Outlook.TaskItem
       Set OutlookApp = CreateObject("Outlook.Application")
       Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
       With OutlookTask
           .Subject = "This is the subject of my task"
           .Body = "This is the body of my task."
           .ReminderSet = True
           'Remind 2 minutes from now.
           .ReminderTime = DateAdd("n", 2, Now)
           'Due 5 minutes from now.
           .DueDate = DateAdd("n", 5, Now) 
           .ReminderPlaySound = True
           'Modify path.
           .ReminderSoundFile = "C:\WINNT\Media\Ding.wav"
           .Save
       End With
   End Function
   

6. To test this function, type the following line in the Immediate Window, and then press ENTER:

   ?fncAddOutlookTask()
   

7. Start Outlook to view the new task.

This section shows you how to use automation from an Access form to start Outlook and to display a new contact screen for input. You can change just one line of code to make this example apply to a new Outlook appointment, journal entry, mail message, note, post, or task.

The following sample shows you how to create a form in Access that starts Outlook from a command button. Then the automation code opens a new contact screen for input in Outlook. After you add the contact, save, and close the contact form, the automation code quits Outlook and returns to the Access form.

To create a contact item in Outlook from an Access form, follow these steps:

1. Open the sample database Automation.mdb created earlier.

2. Create a form that is not based on any table or query, add a command button the form, and make the following property assignments:

   • Form: (save as frmOutlook) - Caption: Add to Outlook Form

   • Command button -

     Name: cmdOutlook

     Caption: Start Outlook\

     OnClick: [Event Procedure]

3. On the View menu, click Code to open the Visual Basic Editor.

4. On the Tools menu, click References.

5. Click Microsoft Outlook 11.0 Object Library in the Available References list.

6. Click OK to close the References dialog box.

7. Type or paste the following procedure in the OnClick event of the command button:

   Option Compare Database
   Option Explicit
   Public Sub cmdOutlook_Click ()
       On Error GoTo StartError
       Dim objOutlook As Object
       Dim objItem As Object
       'Create a Microsoft Outlook object.
       Set objOutlook = CreateObject("Outlook.Application")
       'Create and open a new contact form for input.
       Set objItem = objOutlook.CreateItem(olContactItem)
       'To create a new appointment, journal entry, email message, note, post,
       'or task, replace olContactItem above with one of the following:
       '
       '  Appointment = olAppointmentItem
       'Journal Entry = olJournalItem
       'Email Message = olMailItem
       '         Note = olNoteItem
       '         Post = olPostItem
       '         Task = olTaskItem
   
       objItem.Display
       'Quit Microsoft Outlook.
       Set objOutlook = Nothing
   
       Exit Sub
   StartError:
       MsgBox "Error: " & Err & " " & Error
       Exit Sub
   End Sub
   

8. On the File menu, click Close and Return to Microsoft Access, and switch the form to Form View.

9. Click Start Outlook. Notice that Outlook displays a new contact screen.

10. Fill in the contact information for a new contact and click Save and Close.

11. Start Outlook and click the Contacts in the Navigation pane. Notice the contact shows up in the list of contacts.

This article presented different techniques for using automation to work with Outlook from Access. Automation is a powerful technology that provides a mechanism for one program to control another by setting and reading properties on objects and by invoking methods on them. Using automation allows you to combine programs in your applications to provide much more diversity for your customers.

For more information on automating Outlook, see the following resources:

• Using Automation to Send a Microsoft Outlook Message
• VBA: How to Create a New Contact Item in Outlook with Automation
• ACC: Using Automation to Add a Task/Reminder to MS Outlook
• ACC: Using Automation to Add Appointments to Microsoft Outlook
• OL2002: How to Automate Outlook from Another Program
• HOWTO: Automate Outlook Using Visual Basic

© Microsoft Corporation. All rights reserved.