ACC: Using Automation to Add Appointments to Microsoft Outlook

• Microsoft Access versions 7.0, 97

SUMMARY

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

This article describes a technique to create appointments in a Microsoft Access database and add them to the Microsoft Outlook calendar using Automation.

For information about using Automation to add an appointment to Microsoft Schedule+ for Windows 95, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q149078
   TITLE     : ACC: How to Add Appointments to Schedule+ using OLE
               Automation

MORE INFORMATION

The following example demonstrates how to create a form and a table to enter and store appointment information in a Microsoft Access database, and provides a sample Visual Basic for Applications procedure that uses Automation to add your appointments to Microsoft Outlook.

1. Start Microsoft Access and create a new database called Appt.mdb.

2. Create the following new table in Design view:

         Table: tblAppointments
         --------------------------
         Field Name: Appt
            Data Type: Text
            Field Size: 50
            Required: Yes
         Field Name: ApptDate
            Data Type: Date/Time
            Format: Short Date
            Required: Yes
         Field Name: ApptTime
            Data Type: Date/Time
            Format: Medium Time
            Required: Yes
         Field Name: ApptLength
            Data Type: Number
            Field Size: Long Integer
            Default Value: 15
            Required: Yes
         Field Name: ApptNotes
            Data Type: Memo
         Field Name: ApptLocation
            Data Type: Text
            Field Size: 50
         Field Name: ApptReminder
            Data Type: Yes/No
         Field Name: ReminderMinutes
            Data Type: Number
            Field Size: Long Integer
            Default Value: 15
         Field Name: AddedToOutlook
            Data Type: Yes/No
   
         Table Properties: tblAppointments
         ---------------------------------
         PrimaryKey: ApptDate;ApptTime
   
      NOTE: In this example, the primary key in the appointment table is the
      appointment date and time. You can remove or alter the primary key if
      you want to be able to add multiple appointments for the same date and
      time.
   
   

3. Create a reference to the Microsoft Outlook 8.0 Object Library. To do so, follow these steps:

       a. Create a new module.
   

       b. On the Tools menu, click References.
   

       c. Click Microsoft Outlook 8.0 Object Library in the Available
          References box. If that reference does not appear, click Browse
          to locate the Msoutl8.olb file, which is installed by default in the
          C:\Program Files\Microsoft Office\Office folder.
   

       d. Click OK in the Reference dialog box.
   

       e. Close the module without saving it.
   

4. Use the AutoForm: Columnar Form Wizard to create a new form based on the tblAppointments table. Save the form as frmAppointments.

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

         Form: frmAppointments
         -------------------------
         Caption: Appointment Form
   

         Form Header:
            Height: .5"
         Check Box: AddedToOutlook
            Enabled: No
   
   

6. Add a command button to the Form Header section, and set the following properties:

         Command Button:
            Name: AddAppt
            Caption: Send to Outlook
            OnClick: [Event Procedure]
   
   

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

         Private Sub AddAppt_Click()
            On Error GoTo AddAppt_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 already added to Microsoft Outlook"
               Exit Sub
            ' Add a new appointment.
            Else
               Dim outobj As Outlook.Application
               Dim outappt As Outlook.AppointmentItem
               Set outobj = CreateObject("outlook.application")
               Set outappt = outobj.CreateItem(olAppointmentItem)
               With outappt
                  .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
                  .Save
               End With
            End If
            ' Release the Outlook object variable.
            Set outobj = Nothing
            ' Set the AddedToOutlook flag, save the record, display a message.
            Me!AddedToOutlook = True
            DoCmd.RunCommand acCmdSaveRecord
            MsgBox "Appointment Added!"
         Exit Sub
         AddAppt_Err:
            MsgBox "Error " & Err.Number & vbCrLf & Err.Description
            Exit Sub
         End Sub
   
   

8. Save the form and open it in Form view. Add an appointment record, and then click the Send To Outlook button. Be sure you only enter minutes, not hours and minutes, in the ApptLength field.

9. Start Microsoft Outlook and click Calendar on the Go menu to view the appointments you added.