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.