Article ID: 209963 - Last Review: June 23, 2005 - Revision: 4.0

ACC2000: How to Use Automation to Add Appointments to Microsoft Outlook

View products that this article applies to.

This article was previously published under Q209963

This article applies only to a Microsoft Access database (.mdb).

Advanced: Requires expert coding, interoperability, and multiuser skills.

Expand all | Collapse all

SUMMARY

This article shows you how to create appointments in a Microsoft Access database and how to use Automation to add the appointments to a Microsoft Outlook calendar.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following example demonstrates how to create a table and a form to enter and store appointment information in a Microsoft Access database. It then provides a sample Visual Basic for Applications procedure that uses Automation to add the appointments to Microsoft Outlook.

Start Microsoft Access, and then create a new database named Appt.mdb.

Use the following information to create a new table named tblAppointments:

Collapse this tableExpand this table

Field Name	Data Type	Field Size	Format	Default	Required
Appt	Text	50			Yes
ApptDate	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 ApptTime 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 new 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"
   OnClick: [Event Procedure]

Set the OnClick property 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
                .PatternStartDate = #7/9/2003# 
                'You could get these values 
                'from new text boxes on the form.
                .PatternEndDate = #7/23/2003#
            End With

            .Save
            .Close (olSave)
            End With
            'Release the AppointmentItem object variable.
            Set objAppt = Nothing
    End If

    'Release the Outlook object variable.
    Set objOutlook = 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 9.0 Object Library check box. If a reference for this library does not appear in the list, click Browse to locate the Msoutl9.olb file. This file is installed by default in the C:\Program Files\Microsoft Office\Office folder. Click OK to close the References dialog box.

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

    Appt: Budget Meeting
    ApptDate: <enter 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: Enter ApptLength in minutes, not in hours. Note that in this example, ApptLength is set to 120 minutes instead of to 2 hours.