Updating shared calendar

Question

I am trying to update a shared calendar from an Excel sheet. The code works for the owner of this shared calendar, but it fails for me. The calendar was shared to me and I have full owner permissions.

I can edit the calendar manually, but the idea is that anyone will be able run the macro from this Excel sheet to update the shared calendar.

The relevant code, up to the failure point:

Sub UpdateSched()
    
    Dim olApp As Outlook.Application
    Dim olNameSpace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olFldrOwner As Outlook.Recipient
    
    On Error Resume Next
    ' check if Outlook is running
    Set olApp = GetObject("Outlook.Application")
    If Err <> 0 Then
        'if not running, start it
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0 
    
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olFldrOwner = olNameSpace.CreateRecipient("ownrAlias")
    olFldrOwner.Resolve

    Set olFolder = Nothing  
  
    If olFldrOwner.Resolved Then
        Set olFolder = olNameSpace.GetSharedDefaultFolder(olFldrOwner, olFolderCalendar)

    '    If olFolder Is Nothing Then
    '        Debug.Print "Nothing"
    '    Else
    '        Debug.Print olFolder.Name  '<-Error here if the if-block is run
    '    End If
    
        '******************************
        Set olFolder = olFolder.Folders("Transport Sched") '<-Object Not Found Error
        '******************************
    End If

    'Code below updates appointments on the shared calendar

The full error

'The attempted operation failed. An object could not be found'

For testing, I added the commented out block. This made me think that the error might be in the previous line. When this block is un-commented then the code errors out on the line after Else (same error). So the olFolder object is not nothing, but it can't be found.

It's under "Shared Calendars" I get the error. It updates a calendar I created, that's under "My Calendars."

Is it a problem with finding the correct folder for the shared calendar?
The path to the folder shouldn't change, so I could hard-code it so it works for everyone, is that possible?

Try this `Set olFolder = olNameSpace.GetSharedDefaultFolder(olFldrOwner, olFolderCalendar).Folders("Transport Sched")` let me know — 0m3r, Feb 16 '17 at 20:13
When I try as you suggested it now fails on that line, with the same 'object not found' error. That's actually how the line was originally written, after reading another answer here I had broken it up into 2 lines to help debugging. — jvarnerus, Feb 17 '17 at 14:44

score 1 · Accepted Answer · edited May 23 '17 at 12:31

I came up with a solution to my problem but in a completely different way than I was trying with the code listed in the question. In case anyone else needs it, here is the solution:

Sub ListCalendars()
Dim olApp As Outlook.Application
Dim olPane As Outlook.NavigationPane
Dim olModule As Outlook.CalendarModule
Dim olGroup As Outlook.NavigationGroup
Dim olNavFolder As Outlook.NavigationFolder
Dim olFolder As Folder
Dim i As Integer, j As Integer

On Error Resume Next
' check if Outlook is running
Set olApp = GetObject("Outlook.Application")
If Err <> 0 Then
    'if not running, start it
    Set olApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set olPane = olApp.ActiveExplorer.NavigationPane
Set olModule = olPane.Modules.GetNavigationModule(olModuleCalendar)
Set olGroup = olModule.NavigationGroups.GetDefaultNavigationGroup(olMyFoldersGroup)

'Dummy Do loop allows exit from within nested For-Next loops
Do
For i = 1 To olModule.NavigationGroups.Count        'Cycle through all Nav Groups
    Set olGroup = olModule.NavigationGroups.Item(i)
    Debug.Print olGroup.Name

    For j = 1 To olGroup.NavigationFolders.Count    'Cycle through all calendars in group
        Set olNavFolder = olGroup.NavigationFolders.Item(j)
        Debug.Print " - " & olNavFolder.DisplayName

        'Un-comment If-block below if searching for a particular calendar:
            'CalendarName is the name of the calendar,as listed in your navigation pane
'            If olNavFolder.DisplayName = "CalendarName" Then
'            Debug.Print "Found it!"
'            Set olFolder = olNavFolder.Folder 'To get folder object from NavigationFolder
'            Exit Do
'        End If

    Next
Next
Exit Do 'To prevent endless loop
Loop While True

'If-block below displays results if looking for matching calendar name
'If olFolder Is Nothing Then
'    Debug.Print vbNewLine & "No match found"
'Else
'    Debug.Print vbNewLine & "Matching calendar found: " & olFolder.Name
'End If

End Sub

This code was modified from this page here. Basically, accessing someone else's calendar folder object directly gave me issues, even if the calendar had been shared. By using the various navigation objects though, I was able to cycle through all the calendars listed in my navigation pane, including all the shared calendars.

As provided this routine merely lists the main folders and one level of subfolders. If the two if-blocks are uncommented then the routine will search for the calendar with a given name(just replace CalendarName) and will display whether or not a match was found.

The dummy Do loop was one way to break out of nested loops. There's multiple other ways to accomplish this listed in this SO question.

One other tricky thing is that NavigationFolder objects are NOT the same as Folder objects. This seemingly inconsequential line:

Set olFolder = olNavFolder.Folder

is actually very important if you want to make changes to the calendar folder, since these two object types have different properties and methods.

Updating shared calendar

1 Answers1