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)

Contents

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.

Download the odc_ac_olauto.exe sample file. (156 KB)

Introduction

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.

Send an Outlook Message Using Access

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.

Sending an Outlook Mail Message Programmatically

  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
    

Add Appointments to Outlook

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:

    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.

Add a Task or a Reminder to Outlook

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.

Create a Contact Item in Outlook

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.

Conclusion

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.

Additional Resources

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

© Microsoft Corporation. All rights reserved.