Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Sample Access application for creating appointments in Outlook

Iron Contributor

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.

 

TiemeWoldman_1-1681472244916.png

 

The VBA code

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

 

TiemeWoldman_2-1681472667391.png

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.

 

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

@Tieme Woldman 

 

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

 

Thanks!

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.


@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?

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

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.

Thank @Tieme Woldman , great help is this code !