Parsing appointments from shared Outlook calendars

Question

I am trying to create an anonymized schedule for the conference rooms on our floor via Excel VBA.

The source data is stored in different Outlook calendars (every room has its own) and I got access to them via the "shared calendar" functionality of Outlook.

The problem I am currently having is fully automating the data retrieval process. I got everything else working fine, however, it requires the user to manually select the (currently) five different calendars from a small pop-up window, which is rather tiresome.

I have tried using the GetDefaultFolder(olFolderCalendar).Items approach, but this only seems to work for the local calendar not shared ones from the network (Exchange).

Is there a way to address these room calendars directly so that I can automate my process?

best regards,

daZza

Here's my current code, cut down to the relevant parts:

Set olNS = olApp.GetNamespace("MAPI")

For x = 1 To 5

    Set myCalItems = olNS.PickFolder.Items

    With myCalItems
        .Sort "[Start]", False
        .IncludeRecurrences = True
    End With

    StringToCheck = "[Start] >= " & Quote(StartDate & " 12:00 AM") & " AND [End] <= " & Quote(EndDate & " 11:59 PM")

    Set ItemstoCheck = myCalItems.Restrict(StringToCheck)

    ' Do stuff with every item in "ItemstoCheck"
    ' ...
    ' ...
    ' ...

Next

Dmitry Streblechenko Dmitry Streblechenko · Accepted Answer · 2014-05-19T13:44:31

0

votes

Have you looked into the Namespace.GetSharedDefaultFolder method?

Parsing appointments from shared Outlook calendars

1 Answers