Already on ArticleSlash?

Forgot your password? Sign Up

From Access to Outlook - Adding Your Custom Access Data to Your Outlook Calendar

Patrick A Wood
 


Visitors: 205
 1 vote

Probably the most used Microsoft Office Application is Outlook. Often Outlook is open all day to receive emails, schedule appointments, and manage Contact information. 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. You will learn how to add data from an Access Form to an Outlook Calendar Appointment with just one mouse click!

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.

After setting up the Form and adding code we can add an Appointment to the Outlook Calendar with just one click on a Form button.

Let us look at the code behind that button. First we need to make sure the Current Record is saved. When a Form is “Dirty" it means a value has been changed on the Form but not yet saved. Changing the form record to Me. Dirty=False forces Access to save the current Record.

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 added to Microsoft Outlook", vbCritical

’ Exit the procedure

Exit Sub

Else

’ 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 objOutlook As Object ‘ Outlook. Application
Dim objAppointItem As Object ‘ olAppointmentItem

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

Dim outobj As Outlook. Application

Dim outappt As Outlook. AppointmentItem

Set outobj = CreateObject("Outlook. Application")

Set outappt = outobj. 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 will share with you. 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 objOutlook = CreateObject("Outlook. Application")

Else

’ Outlook is already open-use this method

Set objOutlook = 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 objAppointItem = objOutlook. CreateItem(1) ‘ olAppointmentItem = 1

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. Because our Form has a Validation Rule that requires a Start Date so there is no need to check for a Start Date in our code here.

With objAppointItem

If Len(Me. txtStartTime & vbNullString) = 0 Then

’ There is no Start Time on the Form

’ Add vbNullString ("") to avoid an error

Me. txtStartTime = vbNullString

End If

’ Set the Start Property Value

Start = Me. txtStartDate & " " & Me. txtStartTime

"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:

Me. txtStartDate & " " & Me. txtStartTime

In order to avoid errors, we check for nulls and zero lenth strings before we add any values to Outlook. If there is no Values entered in our Form, we skip them or set a default value for Outlook. The Len Function is used with strings and the Nz Function with Integers and Variants. We add vbNullString when using the Len Function because it adds a zero length string to the Len value of but requires no extra memory. Inserting vbNullString ensures that the Len value is 0 rather than null, which would produce an error.

Outlook does not require that the time be added to the Start Property. So you could, for example, just add the due date for a project.

The Outlook Appointment Property “End" is not required so it could be skipped, but we are using it here.

’ If there is no End Date on the Form just skip it

If Len(Me. txtEndDate & vbNullString) > 0 Then

If Len(Me. txtEndTime & vbNullString) = 0 Then

’ There is no end time on the Form

’ Add vbNullString ("") to avoid an error

Me. txtEndTime = vbNullString|

End If

’ Set the End Property Value

End = Me. txtEndDate & " " & Me. txtEndTime

End If

Here we are using the Nz Function to provide a 0 value in case the txtApptLength TextBox is Null.

Duration = Nz(Me. txtApptLength, 0)

If Len(Me. cboApptDescription & vbNullString) > 0 Then

Subject = Me. cboApptDescription

End If

If Len(Me. txtApptNotes & vbNullString) > 0 Then

Body = Me. txtApptNotes

End If

We are using 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.

If Len(Me. cboLocation & vbNullString) > 0 Then

Location = Me. cboLocation

End If

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 IsNull(Me. txtReminderMinutes) 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:

Save

End With

End If

’ Release the Outlook object variables.

Set objAppointItem = Nothing

Set objOutlook = 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

We have shown you how you can transfer Calendar data from Access to Outlook with just one mouse click using the Appointments Database. You can do it!

You can download the code used here at our Gaining Access Website: http://gainingaccess.net - While you are there download the easy to use free Access Pop-Up Calendarand other free code samples and downloads.

For free Church Management Software visit my other site at http://www.churchmanagesoftware.com

Happy Computing,
Patrick A. Wood

(1625)

Article Source:


 
Rate this Article: 
 
Outlook Express or MS Outlook – Which is Best?
Rated 1.0 / 5
based on 1 vote
ArticleSlash

Related Articles:

Massive PST Files Can Lower Outlook’s Speed – Split Outlook File for a ..

by: Ben Gates (March 06, 2010) 
(Computers and Technology/Software)

Merge Outlook PST Files To Bring In Betterment Within Outlook Management

by: Lisa Malkin (October 20, 2012) 
(Computers and Technology/Software)

Split PST File Outlook – Always Keep Outlook In Healthy Condition

by: Lisa Malkin (February 05, 2013) 
(Computers and Technology/Software)

Prepare Your Outlook Calendar Items For Daylight Saving Time Changes In 2007

by: Ryan Corey (March 01, 2007) 
(Computers and Technology)

Outlook... Not Just for Email! Using Your Outlook Tasks

by: Janet Barclay (March 02, 2007) 
(Computers and Technology)

How Outlook Stores Your Data

by: Jason Perry (January 15, 2007) 
(Computers and Technology)

Notes Better Than Outlook in Mail Routing Aspect (How to Convert MS Outlook ..

by: Ben Gates (March 19, 2010) 
(Computers and Technology/Software)

A Tool to merge two PST Files Outlook Data Anytime Anywhere

by: Lisa Malkin (October 11, 2012) 
(Computers and Technology/Software)

Restore lost Microsoft Outlook data from encrypted OST/PST files

by: Ralph Miller (May 06, 2019) 
(Computers and Technology/Data Recovery)

Outlook Express or MS Outlook – Which is Best?

by: Aanchal V (March 28, 2007) 
(Computers and Technology)