How to Modify Properties (Time Zone) of Recurring Appointments in Outlook 2010 VBA

Question

I am writing a new and revised version of a VBA Outlook macro that will go through all appointment's in a user's calendar and change every recurring series' time zone to Central Standard. No matter what I do, however, I cannot seem to get it to access the recurrence properly. At present, when I debug the macro it will fail the first time a call is made to ex.AppointmentItem.Subject (in the Debug.Print statement after the recurrence pattern rPattern is defined). The central question here is: how can I access and modify an entire series of recurring appointments at one time? Can I a) modify the master appointment, b) iterate through all appointments in the series (which may not set all future occurrences as I need it to), or c) iterate through the exceptions collection and modify the recurrence from there? I have been told that iterating through the exceptions collection (although misleading in name) is the only way to modify the recurrence. Is this correct?

I deeply appreciate any help you can provide, thanks!

Note: The aItem<>Null was commented out as a test, for some reason checking for a Null item (even tried Nothing keyword) always caused an error.

    Public Sub IterateAll()

   Dim olApp As New Outlook.Application
   Dim aObject As Object
   Dim calCollection As Outlook.Items
   Dim tzs As Outlook.TimeZones
   Dim tzCentral As Outlook.TimeZone
   Dim tzUTC As Outlook.TimeZone
   Dim olNameSpace As Outlook.NameSpace
   Dim rPattern As Outlook.RecurrencePattern
   Dim ex As Outlook.Exception


   Dim s As Outlook.TimeZone
   Dim e As Outlook.TimeZone


   Set olNameSpace = olApp.GetNamespace("MAPI")
   Set calCollection = olNameSpace.GetDefaultFolder(olFolderCalendar).Items
   Set tzs = Application.TimeZones
   Set tzCentral = tzs("Central Standard Time")
   Set tzUTC = tzs("UTC")

   For Each aObject In calCollection

       If aObject.IsRecurring Then
           Set rPattern = aObject.GetRecurrencePattern


           Debug.Print ("Subject: " + aObject.Subject)
           Debug.Print ("Old Time Zone is " & aObject.StartTimeZone)
           aObject.StartTimeZone = tzCentral
           aObject.EndTimeZone = tzCentral
               Debug.Print ("New Time Zone is " & aObject.StartTimeZone)

           aObject.Save

        End If
       Next

    End Sub

Siddharth Rout · Answer 1 · 2012-08-03T16:40:37.530

2

I was able to access all recurring appointments. See this sample. I am using late binding with Outlook.

Option Explicit

Const olFolderCalendar = 9

Sub Sample()
    Dim oOlAp As Object, oOlns As Object, oOlfld As Object
    Dim colItems As Object, colFilteredItems As Object
    Dim oOlpatrn As Object, objItem As Object

    Set oOlAp = CreateObject("Outlook.Application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlfld = oOlns.GetDefaultFolder(olFolderCalendar)

    Set colItems = oOlfld.Items

    Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")

    For Each objItem In colFilteredItems
        Set oOlpatrn = objItem.GetRecurrencePattern
        If oOlpatrn.PatternEndDate > Now Then
            Debug.Print objItem.Subject
        End If
    Next
End Sub

FOLLOWUP

Const olFolderCalendar = 9

Sub Sample()
    Dim oOlAp As Object, oOlns As Object, oOlfld As Object
    Dim colItems As Object, colFilteredItems As Object
    Dim oOlpatrn As Object, objItem As Object
    Dim tzs As Object, tzCentral As Object

    Set oOlAp = CreateObject("Outlook.Application")
    Set oOlns = oOlAp.GetNamespace("MAPI")
    Set oOlfld = oOlns.GetDefaultFolder(olFolderCalendar)

    Set colItems = oOlfld.Items

    Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")

    Set tzs = Application.TimeZones
    Set tzCentral = tzs("Central Standard Time")

    For Each objItem In colFilteredItems
        Set oOlpatrn = objItem.GetRecurrencePattern
        If oOlpatrn.PatternEndDate > Now Then
            Debug.Print "Old Time Zone is " & objItem.StartTimeZone
            objItem.StartTimeZone = tzCentral
            Debug.Print "New Time Zone is " & objItem.StartTimeZone
            objItem.Save
        End If
    Next
End Sub

edited Aug 03 '12 at 16:40

answered Aug 03 '12 at 05:12

Siddharth Rout

147,039
17
206
250

I am not familiar with late binding, could you elaborate? – flyingscotsman74656 Aug 03 '12 at 13:27
The trouble that I am running into is that I am able to access all recurring appointments, I simply can't modify them as I truly need to. Is there a way to change the time of a recurring series? – flyingscotsman74656 Aug 03 '12 at 13:42
I just tested it and it works. Are you saving the appointment after changing the timezone? See the above code. – Siddharth Rout Aug 03 '12 at 16:40
Latebinding is where you don't need to set a reference beforehand. If you are doing it in outlook VBA then it doesn't matter. In late binding you declare your objects as "Objects" for example `Dim oOlAp As Object`. You can replace it with the declaration from your code. – Siddharth Rout Aug 03 '12 at 16:42
Would that also change the End Time Zone? – flyingscotsman74656 Aug 03 '12 at 16:47
I just tested it out, and it worked beautifully on the individual appointments. There is one caveat, however, I know that this changes all the appointment time zones, but what am I to do about the series? It seems that I cannot change the time zone for the series, and that even running your code will not change the appearance (on the calendar) of the appointment in question. It seems that the series overrides the individual appointment setting. One of the goals I would love to achieve is to make the appointments display correctly (show at the proper time, not just contain the proper time). – flyingscotsman74656 Aug 03 '12 at 17:01
Thank you for your efforts, I deeply appreciate them! – flyingscotsman74656 Aug 03 '12 at 17:01
No that will only change the start time zone. Similarly you can change the end time zone. After changing the timezones, try closing and restarting outlook. Does it reflect properly now? – Siddharth Rout Aug 03 '12 at 17:17
I added the code to change the end time zone of each appointment, and restarted Outlook, but unfortunately the appointment series remains at the wrong time zone even though each individual appointment was set correctly. I understand that wasn't the goal of the program, but even so I am curious if we can use this method to make that happen. My ultimate goal is to make each series display at the correct time, that is my goal above all else. I would be happy if even an arbitrary and finite number of appointments appeared at the correct time, and not necessarily the series. – flyingscotsman74656 Aug 06 '12 at 16:56
In doing all this, I find it strange that the GUI lets you do it easily, but it is well-nigh impossible to do this in VBA. – flyingscotsman74656 Aug 06 '12 at 16:56
Did you close and restart outlook after running the above code? – Siddharth Rout Aug 06 '12 at 23:23
I did indeed, even though the debug statements showed that time zones changed for each appointment the series remained the same time zone as before, and still displayed at the previous time zone's time slot. – flyingscotsman74656 Aug 07 '12 at 03:45
Can you update your question with the latest code that you are using? Let me test it for you :) – Siddharth Rout Aug 07 '12 at 04:11

How to Modify Properties (Time Zone) of Recurring Appointments in Outlook 2010 VBA

1 Answers1

Linked