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
H Force Outlook 2019 with GMail 2-Step to Require Login? Using Outlook 0
G Retaining Tabs in outlook body Using Outlook 2
V Setting up Outlook 2021 on new computer Using Outlook 2
G Add Map It button to Custom Contacts Form in Outlook Outlook VBA and Custom Forms 1
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
Victor_50 Problem - Google Workspace will stop "unsafe" access to Outlook end 2024 Using Outlook 3
C New pc, new outlook, is it possible to import auto-complete emailaddress Using Outlook 4
T Outlook 365 won't take new working password Using Outlook 0
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
P Can't add custom field to custom Outlook form, it always adds to the Folder instead Outlook VBA and Custom Forms 2
B Sync Outlook Public Folders to Contacts Using Outlook 2
D Delete Outlook emails from MS server Using Outlook 9
B Outlook tasks and PDF Using Outlook 4
D Outlook 2019 is no longer asking for password ... Using Outlook 5
Kika Melo How to mark as Junk any message not from Contacts (in Outlook.com) Using Outlook 3
L Outlook attachments from OneDrive as links Using Outlook 0
G Outlook 365 My iCloud Outlook doesn’t work after reinstalling Microsoft365 on Windows 10 PC – now I get error message on contacts and calendar Using Outlook 1
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
M Synchronization and backup of Outlook from local to server. Using Outlook 8
T How to get an EVENT COLOR option in Outlook 2021 ? Using Outlook 0
K How can I delete an e-mail from Outlook Using Outlook 1
V Outlook Error The Attempted operation Failed. An Object Could Not be found Outlook VBA and Custom Forms 0
P Yahoo/IMAP folder rename by Outlook desktop 365 Using Outlook 0
A Outlook 2019 folder counter Using Outlook 0
A Relocate Search Bar in Outlook Using Outlook 2
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 10
L Opening People Outlook 2021 Using Outlook 2
e_a_g_l_e_p_i Outlook 2021 not letting me setup my Gmail using pop Using Outlook 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
P Can no longer sync Outlook with iPhone calendar after iPhone update to 17.1.1 Using Outlook 7
O Outlook - Switch from Exchange to IMAP Using Outlook 2
e_a_g_l_e_p_i Is it possible to have a reminder in Outlook 2021 for every 90 days Using Outlook 3
farrissf Outlook 2016 Optimizing Email Searches in Outlook 2016: Seeking Insights on Quick Search vs Advanced Search Features Using Outlook 0
C Advanced search terms for "Outlook Data File" Using Outlook 1
N Reply to Outlook messages by moving messages to a specific Outlook folder Outlook VBA and Custom Forms 1
O How to find out the domain and server settings that my Outlook is using? Using Outlook 2
A Outlook 365 (OutLook For Mac)Move "On My Computer" Folder Items From Old To New Mac Computer Using Outlook 3
H Integrating Alexa & Outlook Pro 2021 Using Outlook 2
Z Automatically adjust Outlook Reading Pane from bottom to right depending on portrait or landscape window Using Outlook 1
Rupert Dragwater Background colors not saving in Outlook 365 Using Outlook 15

Similar threads

Back
Top