Reputation: 1

Correct filter for search folder programming in Outlook (AdvancedSearch)

I often need a search for all emails of a specific day. In order not to change the criteria of a search folder every time, I wrote a macro which creates a suitable search folder after asking for a date and displaying this folder.

Works fine, but whereas the search folder created the manual way within Outlook only lists the mails of this day, the programmed version also displays appointments of calendars of colleagues who shared their calendars with me - appointments and meetings which don't relate to me at all but were sent on that specific day.

The second thing, but a not important one is, that when displaying the properties of the created folder in Outlook the button for changing the criteria is disabled.

I think I need some additional filter criteria for method AdvancedSearch, but which ones?

At the moment, my code is as follows:

Sub CreateSearchFolderForDate()

    'Creates a search folder for a specific date. Only the primarey exchange mailbox will be considered
    '(no offline folders, no shared folders).
    'The folder is displayed afterwards
    
    Dim oSearch       As Search
    Dim oSearchFolder As Object
    Dim strScope      As String
    Dim strFilter     As String
    Dim strDate1      As String
    Dim strDate2      As String
    Dim strInput      As String
    
    varInput = InputBox("Date?", "Create search order for a specific date", Date)
    If Not IsDate(varInput) Then
        Exit Sub
    End If
    
    'Delete existing folder first, otherwise there is a runtime error
    Set oSearchFolder = GetSearchFolderByName("Mails for day X")
    If Not oSearchFolder Is Nothing Then
        oSearchFolder.Delete
    End If
        
    strScope = "'" & Application.Session.GetDefaultFolder(olFolderInbox).Parent.FolderPath & "'"
    strFilter = "urn:schemas:mailheader:date >= '" & CDate(varInput) & "' AND urn:schemas:mailheader:date < '" & CDate(varInput) + 1 & "'"
    Set oSearch = Application.AdvancedSearch(Scope:=strScope, Filter:=strFilter, SearchSubFolders:=True, Tag:="Mails of a specific date")
    oSearch.Save ("Mails for day X")
    
    Set oSearchFolder = GetSearchFolderByName("Mails for day X")
    oSearchFolder.Display
    
End Sub


Function GetSearchFolderByName(strSearchFolderName As String) As Object

    'Returns the search folder with the display name specified. Only the primarey exchange mailbox will be considered
    '(no offline folders, no shared folders).

     Dim oStore As Outlook.Store
     Dim oFolder As Outlook.folder
      
     On Error Resume Next
     Set GetSearchFolderByName = Nothing
     For Each oStore In Application.Session.Stores
         If oStore.ExchangeStoreType = olPrimaryExchangeMailbox Then
            For Each oFolder In oStore.GetSearchFolders
                If oFolder.Name = strSearchFolderName Then
                    Set GetSearchFolderByName = oFolder
                    Exit Function
                End If
            Next
        End If
     Next

End Function

My idea was to use '''urn:schemas:calendar:dtstart'' as additional AND as for "normal" emails that should be empty and messed around a little bit with it - but either it had no effect or it resulted in a list containing only the undesired elements and no "normal" mails at all.

Attempts like IS NULL or IS NOT NULL in the filter caused VBA runtime errors.

In column "folder" the created search folder displays either the folder/subfolder my mails are stored in or for the unwanted entries a certain common part like Doe, Jane common_part and Doe, John common_part. But I didn't find a property which I could use as part of my filter ('''AND property NOT LIKE %common_part%''').

Any hint would be very much appreciated.

Regards,

Bootes

Update 2023-02-08: Before refactoring my problem using the hints and answers by @niton (thanks a lot for the patience) I will start a few more trys with AdvancedSearch, based on an analysis of a manually created search folder using the Redemption-Tool as developed by @Dmitry Streblechenko and described in his posting in How to get a search folder criteria in Outlook. The tool provided the following SQL-Statement:

((NOT (MessageClass LIKE 'IPM.Appointment%')) AND (NOT (MessageClass LIKE 'IPM.Contact%')) AND (NOT (MessageClass LIKE 'IPM.DistList%')) AND
(NOT (MessageClass LIKE 'IPM.Activity%')) AND
(NOT (MessageClass LIKE 'IPM.StickyNote%')) AND (NOT (MessageClass = 'IPM.Task'))
AND (NOT (MessageClass LIKE 'IPM.Task.%'))) AND
((("http://schemas.microsoft.com/mapi/proptag/0x0E090102" <> EF0000004B1E3AD5164F3F459BFE6A913D00E89042800000')
AND ("http://schemas.microsoft.com/mapi/proptag/0x0E090102" <> EF0000004B1E3AD5164F3F459BFE6A913D00E89022800000'))
AND ((SentOn < '2022-12-20') AND (SentOn >= '2022-12-19')))

I tried to translate this into VBA, but had no real success: If I use just the active lines, there is no effect at all, if I add the last two ones (formatted as comments below), I get error "Runtime error -2147023281 (8007064f) - Error during execution of operation" (re-translated from German to English):

    strF = "urn:schemas:mailheader:date >= '" & CDate(strInput) & "' AND urn:schemas:mailheader:date < '" & CDate(strInput) + 1 & "' AND "
    strF = strF & "NOT (urn:schemas:mailheader:content-class LIKE 'IPM.Appointment%') AND NOT (urn:schemas:mailheader:content-class LIKE 'IPM.Contact%') AND "
    strF = strF & "NOT (urn:schemas:mailheader:content-class LIKE 'IPM.DistList%') AND NOT (urn:schemas:mailheader:content-class LIKE 'IPM.Activity%') AND "
    strF = strF & "NOT (urn:schemas:mailheader:content-class LIKE 'IPM.StickyNote%') AND NOT (urn:schemas:mailheader:content-class = 'IPM.Task') AND "
    strF = strF & "NOT (urn:schemas:mailheader:content-class LIKE 'IPM.Task.%')" ' AND "
    'strF = strF & Chr(34) & "http://schemas.microsoft.com/mapi/proptag/0x0E090102" & Chr(34) & " <> 'EF0000004B1E3AD5164F3F459BFE6A913D00E89042800000'" ' AND "
    'strF = strF & "(" & Chr(34) & "http://schemas.microsoft.com/mapi/proptag/0x0E090102" & Chr(34) & " <> 'EF0000004B1E3AD5164F3F459BFE6A913D00E89022800000')) AND "

The second approach could be the folder of the item as the unwanted ones are listed in column "In folder" with the folder name containing the a common part that is not in the folder name of the wanted items.

Upvotes: 0

Correct filter for search folder programming in Outlook (AdvancedSearch)

Answers (2)

Related Questions