Pull Outlook shared calendars items from Excel

Status
Not open for further replies.

Chooriang

Member
Outlook version
Outlook 2010 64 bit
Email Account
Outlook.com (as MS Exchange)
I have the following function in Excel to access shared calendar folders in Outlook and list all certain appointments (identified from its subject) within specified date range. The code seems doesn't work as expected as Outlook is loaded from Citrix server. The function always returns "Calendar not shared".
I'm not so sure about this and need somebody's help on how to solve this.
Code:
Option Explicit
Function GetColleagueAppointments(dtStartAppt As Date, dtEndAppt As Date, strUserName As String) 'As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Purpose:      List down all colleague's client meetings between date range
'
' Inputs:       dtStartAppt         Start date to search
'               dtEndAppt           End date to search
'               strUserName         Colleague calendars to search
'
' Assumptions:  * User must have access to the appropriate shared calendars in
'                 Outlook
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim objOL As New Outlook.Application    ' Outlook
Dim objNS As NameSpace                  ' Namespace
Dim OLFldr As Outlook.MAPIFolder        ' Calendar folder
Dim OLAppt As Object                    ' Single appointment
Dim OLRecip As Outlook.Recipient        ' Outlook user name
Dim OLAppts As Outlook.Items            ' Appointment collection
Dim oFinalItems As Outlook.Items
Dim strRestriction As String                    ' Day for appointment
Dim strList() As String                 ' List of all available timeslots
Dim dtmNext As Date                     ' Next available time
Dim intDuration As Integer              ' Duration of free timeslot
Dim i As Integer                        ' Counter
Dim lr As Long, r As Long
Dim wb As Workbook
Dim ws As Worksheet

'FastWB True
Set wb = ThisWorkbook
Set ws = wb.Sheets("Meeting List")

Const C_Procedure = "GetColleagueAppointments"    ' Procedure name
'This is an enumeration value in context of getDefaultSharedFolder
Const olFolderCalendar As Byte = 9

strRestriction = "[Start] >= '" & _
                    Format$(dtStartAppt, "mm/dd/yyyy hh:mm AMPM") _
                    & "' AND [End] <= '" & _
                    Format$(dtEndAppt, "mm/dd/yyyy hh:mm AMPM") & "'"

' loop through shared Calendar for all Employees in array
Set objNS = objOL.GetNamespace("MAPI")

With ws
    On Error Resume Next
    Set OLRecip = objNS.CreateRecipient(strUserName)

    OLRecip.Resolve

    'If OLRecip.Resolved Then
        'Set olFolder = olNS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
        Set OLFldr = objNS.GetSharedDefaultFolder(OLRecip, olFolderCalendar)
    'End If

    ' calendar not shared
    If Err.Number <> 0 Then
        '#   Employee    Date    Start   End Client  Agenda  Location
        r = Last(1, .Columns("G")) + 1
        .Range("F" & r).Value = r - 1                           '#
        .Range("G" & r).Value = strUserName                       'Employee
        .Range("H" & r).Value = "Calendar not shared" 'Format(dtStartAppt, "d-mmm-yyyy")   'Date
        .Range("I" & r).Value = "Calendar not shared"           'Start
        .Range("J" & r).Value = "Calendar not shared"           'End
        .Range("K" & r).Value = "Calendar not shared"           'Client
        .Range("L" & r).Value = "Calendar not shared"           'Agenda
        .Range("M" & r).Value = "Calendar not shared"           'Location

        GoTo ExitHere
    End If

    'On Error GoTo ErrHandler
    Set OLAppts = OLFldr.Items

    ' Sort the collection (required by IncludeRecurrences)
    OLAppts.Sort "[Start]"

    ' Make sure recurring appointments are included
    OLAppts.IncludeRecurrences = True

    ' Filter the collection to include only the day's appointments
    Set OLAppts = OLAppts.Restrict(strRestriction)

    'Construct filter for Subject containing 'Client'
    Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
    strRestriction = "@SQL=" & Chr(34) & PropTag _
                        & "0x0037001E" & Chr(34) & " like '%Client%'"

    ' Filter the collection to include only the day's appointments
    Set OLAppts = OLAppts.Restrict(strRestriction)

    ' Sort it again to put recurring appointments in correct order
    OLAppts.Sort "[Start]"

    With OLAppts
        ' capture subject, start time and duration of each item
        Set OLAppt = .GetFirst

        Do While TypeName(OLAppt) <> "Nothing"
            r = Last(1, .Columns("G")) + 1

            '- Client - HSBC - Trade Reporting
            '#   Employee    Date    Start   End Client  Agenda  Location

            If InStr(LCase(OLAppt.Subject), "client") > 0 Then
                strList = Split(OLAppt.Subject, "-")
                .Range("F" & r).Value = r - 1
                .Range("G" & r).Value = strUserName
                .Range("H" & r).Value = Format(dtStartAppt, "d-mmm-yyyy")
                .Range("I" & r).Value = OLAppt.Start
                .Range("J" & r).Value = OLAppt.End
                .Range("K" & r).Value = Trim(CStr(strList(1)))
                .Range("L" & r).Value = Trim(CStr(strList(2)))
                .Range("J" & r).Value = OLAppt.Location

            End If
            Set OLAppt = .GetNext
        Loop
    End With
End With

ExitHere:
    On Error Resume Next
    Set OLAppt = Nothing
    Set OLAppts = Nothing
    Set objNS = Nothing
    Set objOL = Nothing
    Exit Function

ErrHandler:
    MsgBox Err.Number & ": " & C_Procedure & vbCrLf & Err.Description
    Resume ExitHere
End Function
 
Last edited by a moderator:
Diane,
Thank you for your response.

I change my approach and place modified version of your code in Outlook.
But I get "run time error: You don't have permission to perform this operation"

It highlights the following line and it fails to return all appointments.
Code:
Set CalFolder = objNavFolder.folder

So, what's wrong with the following complete code?
Code:
'Const intFolder As Integer = 2
'Const strGroup As String = "Shared Calendars"
Const strKeyword As String = "Client"

Dim CalFolder As Outlook.folder
Dim nameFolder
Dim strResults As String
Dim dStart As Date
Dim dEnd As Date

' Run this macro
Sub SEARCH_IN_SHARED_CALENDARS()
     Dim objPane As Outlook.NavigationPane
     Dim objModule As Outlook.CalendarModule
     Dim objGroup As Outlook.NavigationGroup
     Dim objNavFolder As Outlook.NavigationFolder
     Dim objCalendar As folder
     Dim objFolder As Outlook.folder
     Dim fName As String, strDate As String
     Dim varLine As Variant, varItems As Variant, varDate As Variant
    
     Dim i As Integer, r As Integer
     Dim g As Integer, x As Integer
     Dim valid As Boolean: valid = True
    
    'strKeyword
    Do
        strDate = InputBox("Enter a date range with format of" & vbCrLf & """m/d/yyyy-m/d/yyyy""", "Enter Date Range")
        varDate = Split(strDate, "-")
        If strDate = "" Or UBound(varDate) <> 1 Then
            MsgBox "Invalid date range!", vbCritical, "Process Failed"
            Exit Sub
        End If
        
        If IsDate(varDate(0)) And IsDate(varDate(1)) Then
            ' set dates
            dStart = CDate(varDate(0)) 'Date
            dEnd = CDate(varDate(1))
            valid = True
        Else
            MsgBox "Incorrect date range format!", vbExclamation, "Warning"
            valid = False
        End If
    Loop Until valid = True
    
    'On Error Resume Next
    Set objCalendar = Session.GetDefaultFolder(olFolderCalendar)
    Set Application.ActiveExplorer.CurrentFolder = objCalendar
    DoEvents
    Set objPane = Application.ActiveExplorer.NavigationPane
    Set objModule = objPane.Modules.GetNavigationModule(olModuleCalendar)
    
    'valid = False
    'With objModule.NavigationGroups
    '    For g = 1 To .Count
    '        Set objGroup = .Item(g)
    '        'fName = fName & objGroup.GroupType & ". " & objGroup.Name = strGroup& vbCrLf
    '        If objGroup.GroupType = intFolder And Trim(objGroup.Name) = strGroup Then
    '            valid = True
    '            x = g
    '            Exit For
    '        End If
    '    Next
    'End With
    
    If valid = False Then
        MsgBox "No shared calendars folder named with ""Shared Calendars""", vbExclamation, "No Shared Calendars"
        Exit Sub
    End If
    'On Error GoTo 0
    
        enviro = CStr(Environ("USERPROFILE"))
        'the path of the workbook
        strPath = enviro & "\Desktop\Meeting List (" & Format(Now(), "ddmmyy hhnn") & ").xlsx"
        
        On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
        If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        End If
        xlApp.Visible = True
        On Error GoTo 0
        
        On Error Resume Next
        ' Open the workbook to input the data
        ' Create workbook if doesn't exist
        Set xlWB = xlApp.Workbooks.Open(strPath)
        If Err <> 0 Then
        Set xlWB = xlApp.Workbooks.Add
        xlWB.SaveAs fileName:=strPath
        End If
        On Error GoTo 0
        Set xlSheet = xlWB.Sheets("Sheet1")
        
        If xlSheet.Range("A1") = "" Then
            xlSheet.Range("A1") = "#"
            xlSheet.Range("B1") = "UserName"
            xlSheet.Range("C1") = "Date"
            xlSheet.Range("D1") = "Start"
            xlSheet.Range("E1") = "End"
            xlSheet.Range("F1") = "Client"
            xlSheet.Range("G1") = "Agenda"
            xlSheet.Range("H1") = "Location"
            xlWB.Save
        End If
    
    Dim NS As Outlook.NameSpace
    Dim objOwner As Outlook.Recipient
    
     With objModule.NavigationGroups
        For g = 1 To .Count
            Set objGroup = .Item(g)
            'fName = objGroup.GroupType & ". " & Trim(objGroup.Name) & vbCrLf & _
                    intFolder & ". " & strGroup & vbCrLf & strKeyword
            'MsgBox fName
            If objGroup.GroupType = 1 Or objGroup.GroupType = 2 Then
                For i = 1 To objGroup.NavigationFolders.Count
                    Set objNavFolder = objGroup.NavigationFolders.Item(i)
                    'If objNavFolder.IsSelected = True Then
                         strResults = ""
                         Set CalFolder = objNavFolder.folder
                         Set nameFolder = objNavFolder
                         Set NS = Application.GetNamespace("MAPI")
                         Set objOwner = NS.CreateRecipient(nameFolder)
                        
                         objOwner.Resolve
                         If objOwner.Resolved Then
                            Set CalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
                         End If
                        
                        SearchSharedCalendar
                        
                        If strResults <> "" Then
                            varLine = Split(strResults, vbCrLf)
                            For r = LBound(varLine) To UBound(varLine) - 1
                                'Find the next empty line of the worksheet
                                rCount = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
                                rCount = rCount + 1
                                
                                varItems = Split(varLine(r), "~~")
                                xlSheet.Range("A" & rCount) = rCount - 1
                                xlSheet.Range("B" & rCount & ":" & "H" & rCount) = varItems
                                xlWB.Save
                            Next r
                        End If
                    'End If
                Next i
            End If
        Next g
     End With
    
     Set objPane = Nothing
     Set objModule = Nothing
     Set objGroup = Nothing
     Set objNavFolder = Nothing
     Set objCalendar = Nothing
     Set objFolder = Nothing
End Sub

Private Sub SearchSharedCalendar()
     Dim CalItems As Outlook.Items
     Dim ResItems As Outlook.Items
     Dim oFinalItems As Outlook.Items
     Dim sFilter As String
     Dim itm As Object
     Dim strAppt As String
     Dim strList() As String, strData(0 To 6) As String
    
     Set CalItems = CalFolder.Items
    
     ' Sort all of the appointments based on the start time
     CalItems.Sort "[Start]"
    
     ' body key word doesn't work if including recurring
     CalItems.IncludeRecurrences = True
    
     On Error Resume Next
     ' if you arent search subfolders, you only need parent name
     'strName = CalFolder.Parent.Name & " - " & CalFolder.Name
    
     ' filter by date first
     sFilter = "[Start] >= '" & dStart & "'" & " And [Start] < '" & dEnd & "'"
     'Debug.Print sFilter
    
     'Restrict the Items collection within date range
     Set ResItems = CalItems.Restrict(sFilter)
    
     ' Filter for Subject containing strKeyword '0x0037001E (body is 0x1000001f)
     Const PropTag  As String = "http://schemas.microsoft.com/mapi/proptag/"
     sFilter = "@SQL=(" & Chr(34) & PropTag _
            & "0x0037001E" & Chr(34) & " like '%" & strKeyword & "%')" ' OR " & Chr(34) & PropTag _
            & "0x1000001f" & Chr(34) & " like '%" & strKeyword & "%')"
     'Debug.Print sFilter
    
     'Restrict the last set of filtered items for the subject
     Set oFinalItems = ResItems.Restrict(sFilter)
    
     'Sort and collect final results
     oFinalItems.Sort "[Start]"
    
    strAppt = ""
    If oFinalItems.Count > 0 Then
        For Each OAppt In oFinalItems
          With OAppt
            If .Start >= dStart And .Start <= dEnd Then
                strList = Split(OAppt.Subject, "-")
                strData(0) = nameFolder
                strData(1) = Format(.Start, "d-mmm-yyyy")
                strData(2) = Format(.Start, "hh:nn AMPM")
                strData(3) = Format(.End, "hh:nn AMPM")
                strData(4) = Trim(CStr(strList(2)))
                strData(5) = Trim(CStr(strList(3)))
                strData(6) = .Location
                
                strAppt = Join(strData, "~~") & vbCrLf & strAppt
            End If
          End With
        Next
    Else
        strAppt = ""
    End If
    
    strResults = strAppt 'iNumRestricted & " matching Appointment found in " & strName & vbCrLf & strAppt
    
    Set itm = Nothing
    Set newAppt = Nothing
    Set ResItems = Nothing
    Set CalItems = Nothing
    Set CalFolder = Nothing
End Sub
 
Have not tested it yet... but is the calendar selected? Try selecting it -
set objNavFolder.IsSelected = True


Set objNavFolder = objGroup.NavigationFolders.Item(i)
'If objNavFolder.IsSelected = True Then
set objNavFolder.IsSelected = True
strResults = ""
Set CalFolder = objNavFolder.folder
 
Thanks, Diane, It works, but some items are duplicate because two or more shared calendars have the same item.
Could we identify if it's the same item even though they are in the different shared calendar folders?
Do the items share the same ID?
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
X Outlook automation pull from PDF Using Outlook 5
D Redemption? Need rapid pull of Outlook Contacts, email + notes for VBA Using Outlook 1
J Pull an email address from body and replace reply-to address Outlook VBA and Custom Forms 4
M rule to change subject, pull email addresses from body, and forward with templ Using Outlook 14
A Pull mail without marking and processing, only by selecting it Using Outlook 1
J multiple email accounts, but only want to pull from two Using Outlook 1
Commodore Back/Forward toolbar buttons with pull-down history? Using Outlook 5
E Pull Data From Non-Default Calendar Outlook VBA and Custom Forms 2
ughlook Open multiple contacts in NEW Outlook? Using Outlook 2
G Outlook translation feature is off Using Outlook 2
J Outlook 2010 does not let me put any account Using Outlook.com accounts in Outlook 3
P 3 of 5 PST files don't install from d:\outlook but only from D:\ Using Outlook 7
HarvMan January Windows 10 preview update force installs new Outlook Using Outlook 1
L Outlook 2010 - new installation on Windows 11 - aplzod32.dll is not a valid Add-in Using Outlook 12
J Outlook troubleshooting/logging - option grayed out Using Outlook 2
B Arrows missing from Outlook emails vertical scrollbar Using Outlook 0
G Outlook 2021 (New) doesn't respect default browser Using Outlook 8
B Outlook or iPhone turning tabs into spaces in Outlook Notes Using Outlook 1
P newly installed Office 365 includes OLD Outlook Using Outlook 6
R Outlook ribbon menu default? Using Outlook 7
H Spam email in Gmail not visible in Outlook Using Outlook 3
J How to transfer Win 10 Outlook to new Windows 11 pc? Using Outlook 11
J Renegade spam URL line displayed in old local Outlook 365 email title Using Outlook 3
G Reduce whitespace in Outlook desktop Contact Cards display Using Outlook 3
C Outlook classic via 365 Using Outlook 2
Dr. Demento Analogous Outlook code to read info into an array (or collection or whatever) Outlook VBA and Custom Forms 7
S Repair Outlook Using Outlook 8
V Outlook Form ListBox is not editable Outlook VBA and Custom Forms 2
F Outlook's contacts Using Outlook 1
D Outlook 2003 stopped dead Using Outlook 2
G Cannot receive emails from gmail account in Outlook 365 Using Outlook 1
E "Cannot display the folder. MS Outlook cannot access the specified file location" Using Outlook 8
P Outlook 2016 Working Offline Using Outlook 2
Rupert Dragwater Cannot reestablish gmail (email address) account in Outlook 365 Using Outlook 11
O Outlook 365 synchronisieren Exchange Server Administration 1
kburrows Outlook Classic - JPG files are corrupted when opened or saved Using Outlook 3
F Sync Outlook Calendar Using Outlook 0
G Change default font size in sticky notes - Outlook Desktop 2021 Using Outlook 2
C VBA in "New Outlook?" Using Outlook 0
D New Outlook with Business Basic Plans Using Outlook 0
D Outlook 2021 not working with Outlook 2003 installed Using Outlook 5
D Outlook 2003 stopped working - get they dialog box asking for username & Password Using Outlook 2
T Outlook 2021 hangs in close on taskbar occasionally Using Outlook 1
M Duplicate removal feature in Outlook 2021 is faulty Using Outlook 2
D.Moore Outlook COM addins source folder Using Outlook 12
P Removing Outlook 365 Account from Send/Receive Using Outlook 3
kburrows Outlook Automatically Merging Contacts Using Outlook 2
A Outlook 2016 Outlook 2016 vs. New Outlook Using Outlook 4
D Outlook Desktop App Email Software Using Outlook 0
efire9207 VBA Outlook Contacts Outlook VBA and Custom Forms 6

Similar threads

Back
Top