VBA Outlook with MS Access -Determine Changes to Appointment
Posted on 2009-12-28
Hi, I am using this code to add appointments in Access 2003 to an Outlook Calendar
''UPDATE CALENDAR SECTION'''
' strFolderPath needs to be something like
' "Public Folders\All Public Folders\Workgroup Folders\A - Ae\ACCESS TEAM TWO" or
' "Personal Folders\Inbox\My Folder", etc
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim colFolders As Outlook.Folders
Dim objFolder As Outlook.MAPIFolder
Dim arrFolders() As String
Dim strFolderPath As String
Dim objAppt As Outlook.AppointmentItem
Dim jobrole As String
Dim qrySQL As String
Dim i As Long
On Error Resume Next
'Access the appropriate calendar
strFolderPath = "Public Folders\All Public Folders\Workgroup Folders\A - Ae\" & jobrole ' "A - Ae", not "A-AE"!!!
arrFolders() = Split(strFolderPath, "\") 'Notice the delimiter. Splits the folder heirarchy into an array.
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objFolder = objNS.Folders.Item(arrFolders(0))
If Not objFolder Is Nothing Then
For i = 1 To UBound(arrFolders) 'Scroll through the folders array
Set colFolders = objFolder.Folders
Set objFolder = Nothing
Set objFolder = colFolders.Item(arrFolders(i)) 'And we're scrolling....
If (objFolder = jobrole) Then 'We found it!
Set objAppt = objFolder.Items.Add()
If Not objAppt Is Nothing Then 'If it is Nothing, Outlook couldn't substantiate the appt
.Subject = Me.fullname.Column(1)
.Start = Me.date1.Value
.End = Me.date2.Value
MsgBox ("For an unknown reason, Access could not create an appointment in this calendar at this time.")
Set GetFolder = objFolder
Set colFolders = Nothing
Set objNS = Nothing
Set objApp = Nothing
I also have a variable that reads the GUID created when I add the record from Access to Outlook Calendar.
What I need it do to is determine if the record has already been added, if so, instead of simply re-adding the appointment in Outlook, I want to have the code check if any changes have been made since it was added in Calendar, and Update the Calendar Appointment Record.
Any suggestions are welcome.