Skip to content

Exporting an Appointment from Access to Outlook

How to export information from a current record of an Access form to make a new Outlook appointment.

Q:  Martin Wichmand writes:  “In my planner I have a Subject, Startdate, StartTime and Endtime. Normally I import those to Outlook via the slow and manual way. But I wonder if it is possible to send those informations via a cmdButton and some code?”

A:  Yes, definitely.  Here is a procedure that will export these fields from the current record of an Access form to make a new Outlook appointment.  The test table I used had three fields:  Subject (Text), StartDateTime and EndDateTime (Date, formatted dd-mmm-yyyy hh:nn ampm).  Here is the command button Click event procedure:

Private Sub cmdExporttoOutlook_Click()

 

On Error GoTo ErrorHandler

  

   Dim appOutlook As Outlook.Application

   Dim strSubject As String

   Dim dteStart As Date

   Dim dteEnd As Date

   Dim appt As Outlook.AppointmentItem

  

   strSubject = Nz(Me![txtSubject].Value)

   If strSubject = “” Then

      GoTo ErrorHandlerExit

   End If

  

   If IsDate(Nz(Me![txtStartDateTime].Value)) = True Then

      dteStart = CDate(Me![txtStartDateTime].Value)

   Else

      GoTo ErrorHandlerExit

   End If

   

   If IsDate(Nz(Me![txtEndDateTime].Value)) = True Then

      dteEnd = CDate(Me![txtEndDateTime].Value)

   Else

      GoTo ErrorHandlerExit

   End If

  

   Set appOutlook = GetObject(, “Outlook.Application”)

   Set appt = appOutlook.CreateItem(olAppointmentItem)

   appt.Subject = strSubject

   appt.Start = dteStart

   appt.End = dteEnd

   appt.Display

  

ErrorHandlerExit:

   Set appOutlook = Nothing

   Exit Sub

 

ErrorHandler:

   ‘Outlook is not running; open Outlook with CreateObject

   If Err.Number = 429 Then

      Set appOutlook = CreateObject(“Outlook.Application”)

      Resume Next

   Else

      MsgBox “Error No: ” & Err.Number & “; Description: “

      Resume ErrorHandlerExit

   End If

 

End Sub

 

Note:  This procedure requires a reference to the Outlook object model.

About this author