none
Outlook 2010 - restrict method RRS feed

  • Question

  • Hello, 

    I'm trying to restrict the below code to only export the data for the specified dates, however it doesn't work.

    Kindly advise...

    Sub RunExportCalendarsToExcel()
        'Change the name of the conference room on the next line.  The name must match the name of the mailbox.'
        ExportCalendarToExcel "Rutherford Cameron", True
        ExportCalendarToExcel "Jenkons Gillian", True
        ExportCalendarToExcel "Williams Clarie", True
        'ExportCalendarToExcel "Fiddler Hayley", True
    
        'Repeat the next line for each subsequent conference room.  Be sure to change the name.'
        'ExportCalendarToExcel "Fiddler Hayley"
    
    End Sub
    
    Sub ExportCalendarToExcel(strCalendarName As String, Optional bolClearWorksheet As Boolean)
        Dim olkFolder As Outlook.Folder, olkItems As Outlook.Items, olkAppt As Outlook.AppointmentItem, olkRecipient As Outlook.Recipient
        Dim excApp As Object, excWkb As Object, excSht As Object, excRng As Object, lngRow As Long, strDat As String, datBeg As Date, datEnd As Date, arrTmp As Variant
        Dim arrTitle As Variant
        
        strDat = InputBox("Enter the date range of the calendar to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", SCRIPT_NAME, Date & " to " & Date)
        arrTmp = Split(strDat, "to")
        datBeg = IIf(IsDate(arrTmp(0)), arrTmp(0), Date) & " 12:00am"
        datEnd = IIf(IsDate(arrTmp(1)), arrTmp(1), Date) & " 11:59pm"
        
        'Launch Excel and open the spreadsheet'
        Set excApp = CreateObject("Excel.Application")
        excApp.Visible = True
        'Change the name and path of the spreadsheet on the next line'
        Set excWkb = excApp.Workbooks.Open("U:\Calendar_Export.xlsx")
        Set excSht = excWkb.Worksheets(1)
        If bolClearWorksheet Then
            Set excRng = excSht.Range("A1").CurrentRegion
            lngRow = excRng.Rows.Count
            excApp.Rows(2 & ":" & lngRow).Delete
            lngRow = 2
        Else
            lngRow = excSht.UsedRange.Rows.Count + 1
        End If
        
        'Connect to and process the shared calendar'
        Set olkRecipient = Session.CreateRecipient(strCalendarName)
        Set olkFolder = Session.GetSharedDefaultFolder(olkRecipient, olFolderCalendar)
        Set olkItems = olkFolder.Items.Restrict("[Start] >= '" & Format(datBeg, "ddddd h:nn AMPM") & "' AND [Start] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
    
        olkItems.Sort "[Start]"
        olkItems.IncludeRecurrences = True
            
        For Each olkAppt In olkItems
            arrTitle = Split(olkAppt.Subject, "-")
            excSht.Cells(lngRow, 1) = strCalendarName
            excSht.Cells(lngRow, 2) = olkAppt.Categories
            excSht.Cells(lngRow, 3) = olkAppt.Start
            excSht.Cells(lngRow, 4) = olkAppt.End
            excSht.Cells(lngRow, 5) = olkAppt.Subject
            excSht.Cells(lngRow, 6) = Format(olkAppt.Start, "hh:nn ampm")
            excSht.Cells(lngRow, 7) = Format(olkAppt.End, "hh:nn ampm")
            excSht.Cells(lngRow, 8) = DateDiff("n", olkAppt.Start, olkAppt.End) / 60
            lngRow = lngRow + 1
        Next
        
    
        
        excSht.Columns("A:H").AutoFit
         
        'Save the spreadsheet and exit Excel'
        Set excRng = Nothing
        Set excSht = Nothing
        'excWkb.Save
        Set excWkb = Nothing
        excApp.Quit
        Set excApp = Nothing
        
        'Clean-up the Outlook objects'
        Set olkFolder = Nothing
        Set olkItems = Nothing
        Set olkAppt = Nothing
    End Sub
    


    Thursday, February 25, 2016 3:16 PM

All replies

  • Try the code from https://msdn.microsoft.com/en-us/library/office/ff866969.aspx?f=255&MSPPError=-2147217396

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Thursday, February 25, 2016 4:10 PM
  • Nope doesn't work...
    Thursday, February 25, 2016 4:20 PM
  • Hello Cameron,

    Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Outlook expects, use the Format function in VBA and ToString in .Net based add-ins. The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M (VBA):

    sFilter = "[LastModificationTime] > '" & Format("1/15/99 3:30pm", "ddddd h:nn AMPM") & "'"

    In managed applications I used the following format:

    string dateTimeStart = DateTime.Now.ToString("MM/dd/yyyy hh:mm tt");

    Take a look at the following articles for the sample code:

    How To: Use Restrict method in Outlook to get calendar items

    How To: Retrieve Outlook calendar items using Find and FindNext methods


    Thursday, February 25, 2016 5:08 PM
  • What exactly does not work? Do you get an error? Or just no results returned? Please show exact query that you pass to Restrict()/

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Thursday, February 25, 2016 9:25 PM
  • Hi Cameron1994,

    Based on my test in Outlook 2013 and Outlook 2010, it works fine in Outlook 2013, but doesn’t work in Outlook 2010 (result is 0). If I removed the time, just use date (e.g. 02/25/2016), it can get the correct result.

    I suggest that you could filter the records by the date, then filter the result with other conditions.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 26, 2016 7:13 AM
    Moderator
  • Starain chen, can you share what works for you in 2010? If i remove the time it still doesn't work.

    thanks

    Friday, February 26, 2016 9:11 AM
  • Hi,

    This is the code:

    Sub RestrictTest()
    Dim objNS As NameSpace
    Dim objItems As Outlook.Items
    Dim appointment As Outlook.AppointmentItem
    Dim strWhere As String
    Dim objFolder As MAPIFolder
    Set objNS = Application.GetNamespace("MAPI")
    Set objFolder = objNS.GetDefaultFolder(olFolderCalendar)
    strWhere = "[Start]>='" & Format(Now, "ddddd") & "'"
    'strWhere = "[Start]>='2/25/2016 10:01:00 AM'"
    'strWhere = "[Subject]='test'"
    Debug.Print objFolder.Items.Count
    Set appointment = objFolder.Items(1)
    Debug.Print appointment.Subject & appointment.Start
    Set objItems = objFolder.Items.Restrict(strWhere)
    objItems.Sort ("[Start]")
    Debug.Print objItems.Count
    
    End Sub

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 29, 2016 3:24 AM
    Moderator
  • that doesn't work for me either...

    the code i originally posted does everything i need with the exception of exporting between the two dates. Instead it ignores it and does everything up until the first date i specify.

    Cheers,

    Cam 

    Monday, February 29, 2016 9:27 AM
  • Hi Cam,

    Do you mean it works fine if you just specify the first date or it ignores the second date? What’s the result if you just specify the second date (< second date)?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 1, 2016 3:03 AM
    Moderator