none
Restrict Outlook items with Categories RRS feed

  • Question

  • Is it possible to use items.Restrict (filter) when the filter uses a string to be found within the categories field?

    I've done some searching and found some sites that say it's not possible.  However I made this filter work in an appointment custom view;  The SQL tab showed my filter as

    "urn:schemas-microsoft-com:office:office#Keywords" LIKE '%ABC%' 

    (where ABC is the string I'm looking for in the categories.)

    But I can't get this to work from within a VBA subroutine.  I've tried filtering on the subject field using the same technique.  That worked ok, so I'm confident that I have constructed the filter correctly, ie correct quotes, etc.

    If restrict doesn't work, how do I remove unwanted appointments from the collection based on categories?  I'm trying to make an extraction of certain appointments to support time reporting.  When I use item.Remove(index) the appointment is deleted (not just removed from the collection).  That is not the behaviour I'm after.

    Any help is appreciated.  Thanks. 

    Tuesday, October 2, 2012 4:08 PM

Answers

  • I remembered that categories can't be used in a filter or restriction, reviewing the Object Browser help for Items.Restrict confirmed that.
     
    How about a workaround? An AdvancedSearch can use Categories and return a result, I just tested that here in Outloook VBA and it worked. I used this test code, searching for appointments in the default Calendar folder where Categories included "Personal":
     
    Private Sub InitSearch()
        Dim objSch As Outlook.Search
        Const strF As String = "urn:schemas-microsoft-com:office:office#Keywords LIKE '%Personal%'"
       
        Const strS As String = "Calendar"
        Const strTag As String = "CatsSearch"
        Set objSch = Application.AdvancedSearch(Scope:=strS, Filter:=strF, Tag:=strTag)
    End Sub

    Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
        If SearchObject.Tag = "CatsSearch" Then
            Debug.Print SearchObject.Results.Count
        End If
    End Sub
     
    The search complete handler must be in ThisOutlookSession or in an instantiated class that has scope while the search is running.
     
    To exclude items from any type of returned collection you'd want to create a new Collection object and populate it with items that meet your filter, such as this snippet shows:
     
    Dim oCol As New Collection
    For i = 1 To whateverCollection.Count
        If whateverCollection.Item(i).Location = "Office" Then
            oCol.Add whateverCollection.Item(i)

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Steve Hoppett" <=?utf-8?B?U3RldmUgSG9wcGV0dA==?=> wrote in message news:b706ff55-bfb0-453a-b63e-5230689e740c...

    Thanks for your help!

    Yes, I do have the "@SQL=" at the beginning of the filter.

    The filter for categories (which doesn't work)

        Const PropTag  As String = "urn:schemas-microsoft-com:office:office#Keywords"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    The filter for subject (which does work):

        Const PropTag  As String = "urn:schemas:httpmail:subject"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    In both cases I got the property tag from Outlook via the filter dialog, SQL tab.  So this seems to be what Outlook uses itself (successfully), so I don't know why it doesn't work in my subroutine.

    BRgds,

    Steve


    Ken Slovak MVP - Outlook
    Wednesday, October 3, 2012 2:37 PM
    Moderator

All replies

  • When one uses a DASL filter such as those used in views for a filter/restriction you need to add "@SQL=" to the beginning of the filter.
     
    So in VBA it would look something like this:
     
    Dim sFilter As String
    sFilter = "@SQL=" & CHR(34) & "urn:schemas-microsoft-com:office:office#Keywords" & CHR(34) & "LIKE '%ABC%'"
     
    See if that does what you want.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Steve Hoppett" <=?utf-8?B?U3RldmUgSG9wcGV0dA==?=> wrote in message news:77964fc4-b6b4-4a78-998c-42018ab165ab...

    Is it possible to use items.Restrict (filter) when the filter uses a string to be found within the categories field?

    I've done some searching and found some sites that say it's not possible.  However I made this filter work in an appointment custom view;  The SQL tab showed my filter as

    "urn:schemas-microsoft-com:office:office#Keywords" LIKE '%ABC%' 

    (where ABC is the string I'm looking for in the categories.)

    But I can't get this to work from within a VBA subroutine.  I've tried filtering on the subject field using the same technique.  That worked ok, so I'm confident that I have constructed the filter correctly, ie correct quotes, etc.

    If restrict doesn't work, how do I remove unwanted appointments from the collection based on categories?  I'm trying to make an extraction of certain appointments to support time reporting.  When I use item.Remove(index) the appointment is deleted (not just removed from the collection).  That is not the behaviour I'm after.

    Any help is appreciated.  Thanks. 


    Ken Slovak MVP - Outlook
    Tuesday, October 2, 2012 5:19 PM
    Moderator
  • Thanks for your help!

    Yes, I do have the "@SQL=" at the beginning of the filter.

    The filter for categories (which doesn't work)

        Const PropTag  As String = "urn:schemas-microsoft-com:office:office#Keywords"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    The filter for subject (which does work):

        Const PropTag  As String = "urn:schemas:httpmail:subject"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    In both cases I got the property tag from Outlook via the filter dialog, SQL tab.  So this seems to be what Outlook uses itself (successfully), so I don't know why it doesn't work in my subroutine.

    BRgds,

    Steve

    Wednesday, October 3, 2012 7:12 AM
  • I remembered that categories can't be used in a filter or restriction, reviewing the Object Browser help for Items.Restrict confirmed that.
     
    How about a workaround? An AdvancedSearch can use Categories and return a result, I just tested that here in Outloook VBA and it worked. I used this test code, searching for appointments in the default Calendar folder where Categories included "Personal":
     
    Private Sub InitSearch()
        Dim objSch As Outlook.Search
        Const strF As String = "urn:schemas-microsoft-com:office:office#Keywords LIKE '%Personal%'"
       
        Const strS As String = "Calendar"
        Const strTag As String = "CatsSearch"
        Set objSch = Application.AdvancedSearch(Scope:=strS, Filter:=strF, Tag:=strTag)
    End Sub

    Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
        If SearchObject.Tag = "CatsSearch" Then
            Debug.Print SearchObject.Results.Count
        End If
    End Sub
     
    The search complete handler must be in ThisOutlookSession or in an instantiated class that has scope while the search is running.
     
    To exclude items from any type of returned collection you'd want to create a new Collection object and populate it with items that meet your filter, such as this snippet shows:
     
    Dim oCol As New Collection
    For i = 1 To whateverCollection.Count
        If whateverCollection.Item(i).Location = "Office" Then
            oCol.Add whateverCollection.Item(i)

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Steve Hoppett" <=?utf-8?B?U3RldmUgSG9wcGV0dA==?=> wrote in message news:b706ff55-bfb0-453a-b63e-5230689e740c...

    Thanks for your help!

    Yes, I do have the "@SQL=" at the beginning of the filter.

    The filter for categories (which doesn't work)

        Const PropTag  As String = "urn:schemas-microsoft-com:office:office#Keywords"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    The filter for subject (which does work):

        Const PropTag  As String = "urn:schemas:httpmail:subject"
        strRestriction = "@SQL=" & Chr(34) & PropTag & Chr(34) & " LIKE '%ABC%'"

    In both cases I got the property tag from Outlook via the filter dialog, SQL tab.  So this seems to be what Outlook uses itself (successfully), so I don't know why it doesn't work in my subroutine.

    BRgds,

    Steve


    Ken Slovak MVP - Outlook
    Wednesday, October 3, 2012 2:37 PM
    Moderator
  • I had a similar requirement.  Given an appointment in one folder with one or more categories I wanted to find the appointments in another folder which matched either of the categories, with one category taking precedence over others.

    I came up against the difficulty of using Restrict  with Keyowrd fields and ended up using two nested Fors.  The outer one looped through the categories on the selected appointment and the inner one looped through the appointments in the other folder looking for appointments with that category.

    Not very elegant but it works.


    Mike VE

    Thursday, October 4, 2012 8:04 AM
  • In this case using AdvancedSearch would also be far faster than iterating items in for loops. You can set the search to run from the root folder down including subfolders, that would be probably the fastest way to go when using the Outlook object model.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Mike VE" <=?utf-8?B?TWlrZSBWRQ==?=> wrote in message news:fca9df5e-d421-4f32-a5fd-ae5f84079a11...

    I had a similar requirement.  Given an appointment in one folder with one or more categories I wanted to find the appointments in another folder which matched either of the categories, with one category taking precedence over others.

    I came up against the difficulty of using Restrict  with Keyowrd fields and ended up using two nested Fors.  The outer one looped through the categories on the selected appointment and the inner one looped through the appointments in the other folder looking for appointments with that category.

    Not very elegant but it works.


    Mike VE


    Ken Slovak MVP - Outlook
    Thursday, October 4, 2012 2:45 PM
    Moderator