Tech Networking
  |  Home |  Articles |  Free Downloads |  Free Code |  Access Links |  SQL Azure |  Products |  Get Premium Software |  Login  |  Register |  Contact Us |   
Skip Navigation LinksHome Page > Articles By Patrick Wood > From Access to Outlook Calendar

From Access to Outlook-Add Your Custom Data to the Outlook Calendar

One of the most used Microsoft Office Application is Outlook. Often Outlook is open all day to receive emails, schedule Appointments, manage Contacts, and schedule Tasks. Unfortunately, it is not always easy to share data between Outlook and Access.

If you use an Access Database that stores almost any kind of dates such as shipping dates, appointments, service scheduling, and maintenance schedules you may wish you could save that information to the Outlook Calendar with just the click of a button.

In this article, we will show you how to do just that. We will show you how to add data from an Access Form to an Outlook Calendar Appointment with just one mouse click.

We specialize in helping businesses sync their data with Outlook Calendars.
Call us today at or contact us at for a free consultation.

One of the best things about this method is its flexibility. You can create an Access Form that holds the precise data you want to save to Outlook. We have set up a Table and Form to hold the Appointment Information we want. Yours can be set up differently. You may use a maintenance schedule, client appointments, project progress dates, or almost any kind of information with a date.

The Access and Outlook Appointment Manager

By double-clicking on an Appointment in the Apointment Manager Main Menu we can manage the Database Appointment Details in Access.

The Access Appointment Details

We can add an Appointment to the Outlook Calendar with just one click on our Form's "Save in Outlook" button.

Let us look at the code behind that button. First we need to make sure the Current Record is saved. If a Form is "Dirty" that means there has been a change made to the data on the Form, but it has not yet been saved. In order for Access to make the Form "Dirty = False" it has to save the current data on the Form.

Private Sub btnAddApptToOutlook_Click()   
    If Me.Dirty Then
        Me.Dirty = False
    End If

Next, we check to make sure the appointment has not already been added to Outlook. On our form we have a checkbox that indicates if that has been done. If the appointment already exists, we inform the user and Exit the Sub.

    If Me.chkAddedToOutlook = True Then
        MsgBox "This Appointment has already been added to the Outlook Calendar." _
            & vbCrLf & vbCrLf & "To save this Appointment in Outlook" & vbCrLf _
            & "uncheck the ""Appointment in Outlook"" checkbox" & vbCrLf _
            & "and click the ""Save in Outlook"" button.", vbCritical
        ' Exit the procedure
        Exit Sub
        'Add a new appointment.

We are using "late binding" so our code works in almost all versions of Outlook. This is crucial if you distribute your Database or take it with you in your thumb drive to use on other computers. However, one drawback of using late binding is that it is slower than early binding. With late binding we use an Object variable:

        ' Use late binding to avoid the "Reference" issue
        Dim olapp As Object ' Outlook.Application
        Dim olappt As Object ' olAppointmentItem  

This is how we would do it if we were using "early binding":

        Dim olApp As Outlook.Application
        Dim olappt As Outlook.AppointmentItem
        Set olapp = CreateObject("Outlook.Application")
        Set olappt = olapp.CreateItem(olAppointmentItem)

We need to know if Outlook is already open because there are two methods of instantiating Outlook and other Office Applications. You use the CreateObject Function if Outlook is not open, and you use the GetObject Function if Outlook is already open.

To determine if Outlook is open we are using the isAppThere Custom Function which we have included at the end of this article. This Function returns True if the application is open, and False if it is not open.

    If isAppThere("Outlook.Application") = False Then
        ' Outlook is not open, create a new instance
        Set olApp = CreateObject("Outlook.Application")
        ' Outlook is already open--use this method
        Set olapp = GetObject(, "Outlook.Application")
    End If

An Outlook Appointment is called an Item, and is referenced in Outlook as olAppointmentItem; its constant value is 1. We must use the value 1 in order to create the Appointment Item so we put the 1 in parentheses after "CreateItem". With late binding, we must use the numbers. In early binding, we could have put olAppointmentItem in the parentheses.

To create a new Appointment Item we use the CreateItem Function instead of the CreateObject Function.

        Set olAppt = olApp.CreateItem(1) ' olAppointmentItem

Our example adds data from the Form to Outlook Appointment Properties. Your Fields and Form Controls probably will not have the same names as the Outlook Appointment Properties. This is no problem because you can easily manage that difference in code. For example, if you have a TextBox named "txtProjectDueDate". You can use that as your Outlook "Start" Property like this:

        .Start = "#" & Me.txtProjectDueDate & "#" 

You may need to use more Appointment Properties than we have in our example. You can use the Object Browser to see the complete list of Appointment Properties. It is a long list with more than enough Properties to meet almost anyone's needs.

Now let us examine how to add our data to the Appointment Properties:

        With olAppt
            ' Surround the Date and time with "#" characters and
            ' format the Start Date and Time using FormatDateTime
            ' Set the Start Property Value
            .Start = "#" & FormatDateTime(Me.txtStartDate, vbShortDate) & " " & FormatDateTime(Me.txtStartTime, vbShortTime) & "#"  

"Start" is the first Appointment Property we are setting. Outlook uses both the Date and Time in the Start Property, but our Form has the date and time in seperate TextBoxes. To handle this, we just concatenate the date and time together like this:

            "#" & FormatDateTime(Me.txtStartDate, vbShortDate) & " " & FormatDateTime(Me.txtStartTime, vbShortTime) & "#"

Outlook does not require that the time be added to the Start Property. However, to avoid errors, if you leave the Start Time blank, then you need to leave the End Time blank also. You can do this if you just want to add the due date for a project without the Time.

The Outlook Appointment Property "End" is not always required but you may get an error if you do not supply any value.

            ' Set the End Property Value
            .End = "#" & FormatDateTime(Me.txtEndDate, vbShortDate) & " " & FormatDateTime(Me.txtEndTime, vbShortTime) & "#"  

In order to avoid errors, we often use the Nz Function to handle nulls and zero length strings in our TextBoxes or ComboBoxes. The Nz Function gets the value you designate if the value is Null or a Zero Length String after the comma like this: Nz(Me.myTextBox, 0). This will not change the value in the TextBox, but it will change the value used by the code. Here we are using the Nz Function to provide a 0 value in case the txtApptLength TextBox is Null. Then we use vbNullString to provide a zero length String value.

            .Duration = Nz(Me.txtApptLength, 0)
            .Subject = Nz(Me.cboApptDescription, vbNullString) ' vbNullString uses a little less memory than "" 
            .Body = Nz(Me.txtApptNotes, vbNullString) 

We have provided a Combo box to select Locations from a Table to add to our Form along with other information about the location in unbound TextBoxes. Here we add the Outlook Location Property.

            .Location = Nz(Me.txtLocation, vbNullString) 

If we want Outlook to Remind us when our appointment is near we check the chkAppointReminder Checkbox on our Form and enter the number of minutes we want to be reminded before our Appointment. If the chkAppointReminder Checkbox is checked but we forget to enter our reminder minutes it will cause an error. To avoid that error we use code to change no minutes to 30 minutes.

            If Me.chkApptReminder = True Then
                If Len(Me.txtReminderMinutes & "") = 0 Then
                    Me.txtReminderMinutes.Value = 30
                End If
                .ReminderOverrideDefault = True
                .ReminderMinutesBeforeStart = Me.txtReminderMinutes
                .ReminderSet = True
            End If

Now we can save the Appointment Properties and start cleaning up:

        End With
    End If 
    ' Release the Outlook object variables.
    Set olAppt = Nothing
    Set olApp = Nothing
    ' Set chkAddedToOutlook to checked
    Me.chkAddedToOutlook = True
    ' Save the Current Record because we checked chkAddedToOutlook
    If Me.Dirty Then
        Me.Dirty = False
    End If
    ' Inform the user
    MsgBox "Appointment Added!", vbInformation
End Sub

Here is the code by Rick Dobson that checks if Outlook is open.

' Procedure : isAppThere
' Author    : Rick Dobson, Ph.D - Programming Microsoft Access 2000
' Purpose   : To check if an Application is Open
' Arguments : appName The name of the Application
' Example   : isAppThere("Outlook.Application")
Public Function isAppThere(appName As StringAs Boolean
    On Error Resume Next
    Dim objApp As Object
    isAppThere = True
    Set objApp = GetObject(, appName)
    If Err.Number <> 0 Then isAppThere = False             
End Function

We have shown you how you can transfer Calendar data from Access to Outlook with just one mouse click using the Appointments Database. With just a little modification of the code, you can use your present Forms to send information with Dates to the Outlook Calendar.

Get the Code

You can download the code used in this article from our Free Code Samples page or you can purchase the Access and Outlook Appointment Manager.

We specialize in helping businesses sync their data with Outlook Calendars.
Call us today at or contact us at for a free consultation.