How to Use Items.Restrict(Filter) to filter a collection of items with conditions applied on Custom Made Fields RRS feed

  • Question

  • Hello,

    My functionality aim is to filter the Items in a collection of Custom Made Forms and export the filtered result to Excel Workbook, by writing each field of the Outlook Form to a Excel Worbook Cell.

    The Custom Outlook Form I am talking about is a specially designed Task Form, and it holds the Message Class=IPM.Task.ReklamationFormular_NEW.

    My issue concerns filtering applied to a custom made field in the Outlook Form.

    I managed to make it work. I used it  successfully with standard fields for the TaskItem type like [StartDate],[Status], but I can not make it run with the custom field called [valueDatenLaconTeamBereich].

    What am I missing? Thank you so much in advance, for this and for helping me with the previous instances (and guiding me to progress with Outlook VBA)

    See detailed code below:

    I am appealing modules after firing the click event of a button in a user form:

    Private Sub cmdSelectDepartment_Click()
    ' to filter by custom made field called [valueDatenLaconTeamBereich]
        Dim objOl As Outlook.Application
        Dim strDepartment As String
        strDepartment = cboSelectDepartment.Value
        sFilter = "[valueDatenLaconTeamBereich]='" & strDepartment & "'"
        Me.Caption = "You have selected to export items to Excel Workbook filtered by Department."
         On Error Resume Next
        Set objOl = GetObject(, "Outlook.Application")
        '*** consider 2 cases: when Outlook Application it is not open (and open it through scripting) and when Outlook Application is open ***
        If objOl Is Nothing Then  '*** case when Outlook Application is not open
            'MsgBox "Outlook is not open"
            Call OpenOutlook_WriteToExcel
            'Msgbox "Outlook is open"
            Call WriteToExcel
        End If
    End Sub

    and the module

    Option Explicit
    'SECTION : declare public variables which will be shared from the module to the user forms
    Public longMonthStartDate_OutlookIsOpen As Integer
    Public sFilter As String
    Public Sub WriteToExcel()
        Dim objOl As Outlook.Application
        Dim objNS As Outlook.Namespace
        Dim objFolder As Outlook.Folder
        Dim objSourceFolder As Outlook.Folder
        Dim objSaveFolder As Outlook.Folder
        Dim objSourceItems As Outlook.Items
        Dim objSourceItemsRestrict As Object
        Dim objSourceTask As Outlook.TaskItem
        Dim objShell As Object
        Dim objExcel As Excel.Application
        Dim objWkbk As Excel.Workbook
        Dim objWksht As Excel.Worksheet
        Dim strToday As String
        Dim strSaveFileName As String
        Dim strDefaultProfilePath As String
        Dim strFilename As String
        Dim strMsg1 As String, strMsg2 As String
        Set objShell = CreateObject("WScript.Shell")
        strDefaultProfilePath = objShell.ExpandEnvironmentStrings("%USERPROFILE%")
        strFilename = strDefaultProfilePath & "\Documents\Export_Reklamation _TEMPLATE.xls"
        If Dir(strFilename) = "" Then
            MsgBox "I could not find the Excel Workbook Template to write data. Please make sure you save the right template in your \Documents\ folder"
        End If
        Set objOl = Outlook.Application
        Set objNS = objOl.GetNamespace("MAPI")
        Set objFolder = objNS.GetDefaultFolder(18)
        '*** version for production ***
        Set objSourceFolder = objFolder.Folders("someUpperSourceFolder").Folders("SomeFolder")
        Set objSourceItems = objSourceFolder.Items
                'MsgBox longMonthStartDate_OutlookIsOpen
        Set objSourceItemsRestrict = objSourceItems.Restrict(sFilter)
        strMsg1 = MsgBox("There exist " & objSourceItemsRestrict.Count & " items in the source folder " & vbCrLf & objSourceFolder _
        & vbCrLf & " according with your filtering criteria.", vbInformation, "Hello!")
        strToday = Format(Now(), "yyyymmdd") & "_at_" & Format(Now(), "HHmmss")
        strSaveFileName = "Export_from_Folder_" & objSourceFolder.Name & "_timestamp_" & strToday
        Set objExcel = Excel.Application
        Set objWkbk = Excel.Workbooks.Open(strFilename)
        For Each objSourceTask In objSourceItemsRestrict
            '***** Section to write in Excel Workbook *****
        objWkbk.SaveAs Filename:=strDefaultProfilePath & "\Documents\" & strSaveFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        strMsg2 = MsgBox("Filtered content of folder """ & objSourceFolder.Name & """ was saved in " & strSaveFileName, vbInformation, "Done!!")
        Set objWksht = Nothing
        Set objWkbk = Nothing
        Set objExcel = Nothing
        Set objOl = Nothing
        Set objNS = Nothing
        Set objFolder = Nothing
        Set objSourceFolder = Nothing
        Set objSaveFolder = Nothing
        Set objSourceItems = Nothing
        Set objSourceItemsRestrict = Nothing
        Set objSourceTask = Nothing
        Set objShell = Nothing
    End Sub

    The custom made field is defined at the folder level, not only at item's level:

    • Edited by cgeorgec Wednesday, March 14, 2018 4:24 PM
    Wednesday, March 14, 2018 4:14 PM


  • You will need to use the @SQL query format and specify the custom property name in the DASL format (with the property name quoted):

    sFilter = "@SQL=""{00020329-0000-0000-C000-000000000046}/ThePropertyName"" = 'SomeValue' "
    You can see the DASL property names in OutlookSpy (click IMessage button, select the property in question, look at the DASL edit box).

    Dmitry Streblechenko (MVP)
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Wednesday, March 14, 2018 4:47 PM

All replies