VBA Outlook with MS Access -Determine Changes to Appointment

Posted on 2009-12-28
Last Modified: 2013-11-27
Hi, I am using this code to add appointments in Access 2003 to an Outlook Calendar

  ' 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
                With objAppt
                    .Subject = Me.fullname.Column(1)
                    .Start = Me.date1.Value
                    .End = Me.date2.Value
                End With
                Exit For
                MsgBox ("For an unknown reason, Access could not create an appointment in this calendar at this time.")
            End If
        End If
  End If
  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.

Question by:jfer0x01
    LVL 38

    Accepted Solution

    The  attached code from this link might be what you need:
    Sub SetControlItemPropertyExample()
        Dim myInspector As Outlook.Inspector
        Dim myAppt As Outlook.AppointmentItem
        Dim ctrl As Object
        Dim ctrls As Object
        Dim myPages As Outlook.Pages
        Dim myPage As Object
        Set myAppt = Application.CreateItem(olAppointmentItem)
        Set myInspector = myAppt.GetInspector
        myAppt.MeetingStatus = olMeeting
        myAppt.Subject = "Test Appointment"
        Set myPages = myInspector.ModifiedFormPages
        Set myPage = myPages.Add("Binding Example")
        Set ctrls = myPage.Controls
        Set ctrl = ctrls.Add("Forms.TextBox.1")
        ctrl.Top = 10
        ctrl.Left = 10
        myInspector.SetControlItemProperty ctrl, "To"
    End Sub

    Open in new window

    LVL 38

    Expert Comment

    From this link, use the following command line switch:


    Starts Outlook and forces a detection of new meeting requests in the Inbox, and then adds them to the calendar.
    LVL 9

    Author Comment

    I have not had any time to attempt this

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now