How to create an Outlook search folder using VBA

One thing that really annoys me about Outlook is that I can right click on a message and do pretty much anything I might want to do, except create a search folder.

I like to create search folders for people who hire me then when the project is finished, I delete the search folder.

Find Related is slow and not persistent. Plus, looking over the list of search folders helps me remember who I'm looking for. :)

Use VBA to create an Instant search for messagesfrom the selected contact.

VBA to the rescue (as always!) The following code creates a search folder using either the sender's display name or email address. It's an amazingly fast search. Next on my to-do list: use VBA to replace the Activities tab.

Error # -2147219964 : Cannot create folder. is trying to tell you that the folder name exists. Use the DeleteSearchFolder macro to delete it, then recreate it.

Create search folder for message sender macro

Open the VBA editor using Alt+F11, paste the code into the editor. Select a message and run the macro.

The current iteration works only on the default Inbox (and subfolders). It can easily be tweaked to use a selected contact:
Dim oMail As Outlook.ContactItem
strFilter = oMail.Email1Address

Sub SearchFolderForSender()
On Error GoTo Err_SearchFolderForSender

Dim strFilter As String

' lets get the email address from a selected message
Dim oMail As Outlook.MailItem
Set oMail = ActiveExplorer.Selection.Item(1)
strFilter = oMail.SenderEmailAddress


If strFilter = "" Then Exit Sub

Dim strDASLFilter As String

' From email address
Const From1 As String = "http://schemas.microsoft.com/mapi/proptag/0x0065001f"
Const From2 As String = "http://schemas.microsoft.com/mapi/proptag/0x0042001f"
strDASLFilter = "(""" & From1 & """ CI_STARTSWITH '" & strFilter & "' OR """ & From2 & """ CI_STARTSWITH '" & strFilter & "')"


' From Display name
'strDASLFilter = """urn:schemas:httpmail:fromname"" LIKE '" & strFilter & "' "

Dim strScope As String
strScope = "Inbox"

Dim objSearch As Search
Set objSearch = Application.AdvancedSearch(Scope:=strScope, Filter:=strDASLFilter, SearchSubFolders:=True, Tag:="SearchFolder")

'Save the search results to a searchfolder
objSearch.Save (strFilter)

Set objSearch = Nothing

Exit Sub

Err_SearchFolderForSender:
MsgBox "Error # " & Err & " : " & Error(Err)

End Sub

Delete the Search folder

To delete the search folder you created using the code above, you can use a macro such as this one.

This is a tweaked version of the macro at (Less Than) Portable Search Folders. I tweaked it to look for a search folder that uses the sender's address (oMail.SenderEmailAddress) from a selected message.

Sub DeleteSearchFolder()

    Dim CommonViewsEIDBin As String
    Dim CommonViewsEIDString As String
    Dim CommonViewsFolder As Folder
    Dim ACTable As Table
    Dim oRow As Row
    Dim SFDefinitionEID As String
    Dim SFDefinitionItem As StorageItem


Dim strFilter As String
Dim oMail As Outlook.MailItem
 
Set oMail = ActiveExplorer.Selection.Item(1)
strFilter = oMail.SenderEmailAddress

        
CommonViewsEID = Session.DefaultStore.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x35E60102")
    CommonViewsEIDString = Session.DefaultStore.PropertyAccessor.BinaryToString(CommonViewsEID)
    Set CommonViewsFolder = Session.GetFolderFromID(CommonViewsEIDString)
    
    Set ACTable = CommonViewsFolder.GetTable("[Subject] = '" & strFilter & "'", olHiddenItems)
    
    Set oRow = ACTable.GetNextRow()
    
    If (Not (oRow Is Nothing)) Then
        SFDefinitionEID = oRow("EntryID")
        Set SFDefinitionItem = Session.GetItemFromID(SFDefinitionEID)
        SFDefinitionItem.Delete
    End If

End Sub

Searching other fields

You can search any Outlook field if you know the field name and filter code to use. The easiest way to find out what you need to replicate a filter is to create a filter in Outlook then check the SQL tab.

You can use the httpmail and mailheader namespace schemas for some fields, but some search queries, such as the sender's actual email address, need the mapi property tag.

The httpmail and mailheader schemas are listed on the following pages at MSDN: urn:schemas:httpmail: Namespace and urn:schemas:mailheader: Namespace.

To pick up a value from a different Outlook field, change the field name in
strFilter = oMail.SenderEmailAddress and change the search string used in strDASLFilter

This long filter covers the common search fields.

strDASLFilter = """urn:schemas:httpmail:fromname"" LIKE '%" & strFilter & "%' " + _ 
"OR ""urn:schemas:httpmail:textdescription"" LIKE '%" & strFilter & "%' " + _ 
"OR ""urn:schemas:httpmail:displaycc"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""urn:schemas:httpmail:displayto"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""urn:schemas:httpmail:subject"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""urn:schemas:httpmail:thread-topic"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/received_by_name"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/id/{00062008-0000-0000-C000-000000000046}/8586001f"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/id/{00062008-0000-0000-C000-000000000046}/85a4001f"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/id/{00062041-0000-0000-C000-000000000046}/8904001f"" LIKE '%" & strFilter & "%' " + "OR ""http://schemas.microsoft.com/mapi/proptag/0x0e03001f"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/proptag/0x0e04001f"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/proptag/0x0042001f"" LIKE '%" & strFilter & "%' " + "OR ""http://schemas.microsoft.com/mapi/proptag/0x0044001f"" LIKE '%" & strFilter & "%' " +  _ 
"OR ""http://schemas.microsoft.com/mapi/proptag/0x0065001f"" LIKE '%" & strFilter & "%' "

Related Posts:

1. 

   Use Instant search to find messages from a contact
2. 

   Reply using the address a message was sent to
3. How to use SQL filters to create a filter to show the next 21 days (or

   How to create a "Next 21 days" Task filter
4. This macro demonstrates how to pick up the sender's address and use it

   New Message From Sender