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

Article
09/18/2009

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.

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:

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

Sending an Outlook Mail Message Programmatically

Create a sample text file named Customers.txt in the My Documents folder.
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.
Create a module and type the following line in the Declarations section if it is not already there:
```
Option Explicit
```
On the Tools menu, click References.
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.
Click OK to close the References dialog box.

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

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

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

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

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.
Use the AutoForm: Columnar Wizard to create a form that is based on tblAppointments table, and then save the form as frmAppointments.
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
Add a command button to the form header section, and then set the following properties:
- Name: cmdAddAppt
- Caption: Send to Outlook
- Width: 2"

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

On the Tools menu, click References.
In the References dialog box, click to select the Microsoft Outlook 11.0 Object Library check box.
Click OK to close the References dialog box.
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
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:

Open the sample database Automation.mdb created earlier, and then create an additional module.
On the Tools menu, click References.
In the Available References dialog box, click to select the Microsoft Outlook 11.0 Object Library check box, and then click OK.
Click OK to close the Available References dialog box.

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

To test this function, type the following line in the Immediate Window, and then press ENTER:
```
?fncAddOutlookTask()
```
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:

Open the sample database Automation.mdb created earlier.
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]
On the View menu, click Code to open the Visual Basic Editor.
On the Tools menu, click References.
Click Microsoft Outlook 11.0 Object Library in the Available References list.
Click OK to close the References dialog box.

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

On the File menu, click Close and Return to Microsoft Access, and switch the form to Form View.
Click Start Outlook. Notice that Outlook displays a new contact screen.
Fill in the contact information for a new contact and click Save and Close.
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:

Share via