Sample Access application for creating appointments in Outlook

[Tieme Woldman]

Hello everyone,

I received several requests for my sample Access application for creating appointments in Outlook, so I am sharing it here.

The application

The application automatically opens the Action form. When you complete this form and click Add to Outlook, some VBA code in the application creates an appointment in Outlook based on the action data and checks the Added to Outlook setting.

The VBA code

Select Database Tools > Visual Basic to open the underlying VBA code. If needed, expand the Project folder:

And double click the Form_Actions object to display the VBA code:

Private Sub cmdAddToOutlook_Click()

    ' Only add an appointment to Outlook if it has not been done before.
    If Me.AddedToOutlook = False Then
        ' Declare objects for the Outlook application and an appointment that you can address programmatically
        Dim xOL As Outlook.Application, itmAppoint As Outlook.AppointmentItem
    
        ' Create the actual Outlook object and an object for the appointment to be added
        Set xOL = New Outlook.Application
        Set itmAppoint = xOL.CreateItem(olAppointmentItem)
        
        ' Fill the appointment details and send it to Outlook
        With itmAppoint
            .Subject = Me.ActionDescription
            .Start = Me.ActionDate + Me.ActionTime
            .End = Me.ActionDate + Me.ActionTime + Me.ActionLength
            .Location = Me.ActionLocation
            .Save ' <- this actually sends the appointment to Outlook
        End With
        
        ' Remove the objects to keep memory clean
        Set itmAppoint = Nothing
        Set xOL = Nothing
        
        ' Set the checkbox
        Me.AddedToOutlook = True
        
        ' Requery the form to show all data changes
        Me.Requery
    End If
End Sub

Note

I set a reference to the Outlook Object Library from VBA, but if you receive an error that the reference is missing, follow these steps from VBA:

1. Select Tools > References...
2. Scroll to the Microsoft Outlook 16.0 Object Library item and select its checkbox. Your version may be different from 16.0 when you work with another Access version.
3. Click OK.
4. Try to add to Outlook again.

[#2]

Thank you so much for taking the time to share this :smiling_face_with_smiling_eyes:

[#3]

@Tieme Woldman 

Forgive the rookie question. Once I save this, how do I add to my existing database?

Thanks!

[#4]

Not at all. The following steps are a way to add it to an existing database:

1. Open the OutlookAppoint database.
2. If needed, expand the Shutter bar/Navigation pane.
3. Right-click the Actions form and select Export > Access.
4. Browse to and select the existing database where you want to add to.
5. Clik OK.
6. Open the existing database and optionally modify the (imported) Action Form.

[#5]

Great, thank you!

[#6]

@Tieme Woldman Thanks so much for the VBA code to add to Outlook, it works great. I dont suppose you have the code to remove an appointment from Outlook using access do you?

[#7]

@craigjacks - a good ideea, I am intereted too!

[#8]

Not to put words in Tieme's mouth, but couldn't you invest a bit of learning effort in seeing if you couldn't reverse the code to remove an an appointment? You have the starting point and the concept. Perhaps it's as straightforward as searching out the appropriate action (remove or delete instead of create) for what you need to do.

It's a way not only to achieve the end goal, but to learn more about the tools you are using for your own benefit.

[#9]

Thank @Tieme Woldman , great help is this code !

[#10]

I've also added functionality to delete an appointment from Outlook. In the sample application, you'll now find a delete button in addition to the add button.

This is the underlying code, which deletes an appointment if the subject and start date/time match:

Private Sub cmdDeleteFromOutlook_Click()
    Dim xOL As Outlook.Application, itmAppoint As Outlook.AppointmentItem, colAppoint As Outlook.Items
    Dim nmsOL As Outlook.NameSpace, fldOL As Outlook.MAPIFolder
    Dim i As Integer
    
    ' Create the actual Outlook object and an object for the appointment to be deleted
    Set xOL = New Outlook.Application
    Set itmAppoint = xOL.CreateItem(olAppointmentItem)
    Set nmsOL = xOL.GetNamespace("MAPI")
    Set fldOL = nmsOL.GetDefaultFolder(olFolderCalendar)
    
    ' Loop over all appointments
    Set colAppoint = fldOL.Items
    For i = colAppoint.Count To 1 Step -1
        Set itmAppoint = colAppoint.Item(i)
        ' Delete appointment if description and start data/time match
        If Me.ActionDescription = itmAppoint.Subject And Format(Me.ActionDate, "yyyy-mm-dd") & " " & Format(Me.ActionTime, "hh:mm") = Format(itmAppoint.Start, "yyyy-mm-dd hh:mm") Then
            itmAppoint.Delete
    
            ' Reset the checkbox
            Me.AddedToOutlook = False
            
            ' Requery the form to show all data changes
            Me.Requery
        End If
    Next
  
    ' Remove the objects to keep memory clean
    Set itmAppoint = Nothing
    Set colAppoint = Nothing
    Set nmsOL = Nothing
    Set fldOL = Nothing
    Set xOL = Nothing
End Sub

This code also resets the 'Added to Outlook' flag in the Access database.

I hope this helps. 

Tieme

[#11]

I added a new sample application that also deletes appointments.

[#12]

I added a new sample application that also deletes appointments.