Last reviewed on March 11, 2015   —  11 Comments

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 messages from 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 on the default Inbox (and subfolders) and Sent folder. It can easily be tweaked to use a selected contact:
Dim oMail As Outlook.ContactItem
strFilter = oMail.Email1Address

Updated March 11 2015: Results include messages sent to and from the sender which are in the Inbox (and subfolders) and Sent Items folder. The From filter looks for messages from the sender's email address; the To filter checks for both the email address and the sender's display name. The Search folder uses the Sender's name, not the email address.


On Error GoTo Err_SearchFolderForSender

Dim propertyAccessor As Outlook.propertyAccessor
Dim strFrom As String
Dim strTo As String

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

strFrom = oMail.SenderEmailAddress
strTo = oMail.SenderName

If strFrom = "" Then Exit Sub

Dim strDASLFilter As String

' From & To fields
Const From1 As String = "http://schemas.microsoft.com/mapi/proptag/0x0065001f"
Const From2 As String = "http://schemas.microsoft.com/mapi/proptag/0x0042001f"
Const To1 As String = "http://schemas.microsoft.com/mapi/proptag/0x0e04001f"
Const To2 As String = "http://schemas.microsoft.com/mapi/proptag/0x0e03001f"

strDASLFilter = "((""" & From1 & """ CI_STARTSWITH '" & strFrom & "' OR """ & From2 & """ CI_STARTSWITH '" & strFrom & "')" & _
" OR (""" & To1 & """ CI_STARTSWITH '" & strFrom & "' OR """ & To2 & """ CI_STARTSWITH '" & strFrom & "' OR """ & To1 & """ CI_STARTSWITH '" & strTo & "' OR """ & To2 & """ CI_STARTSWITH '" & strTo & "' ))"

Debug.Print strDASLFilter

Dim strScope As String
strScope = "'Inbox', 'Sent Items'"
    
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 (strTo)

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.

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.SenderName

        
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 & "%' "

Comments

    • Diane Poremsky says

      You'd change strFilter = oMail.SenderEmailAddress to
      strFilter = oMail.Categories
      and strDASLFilter = "(""" & From1 & """ CI_STARTSWITH '" & strFilter & "' OR """ & From2 & """ CI_STARTSWITH '" & strFilter & "')"
      to strDASLFilter = "(""" & Categories & """ CI_STARTSWITH '" & strFilter & "')" (I don't test it, so it might not be exactly correct.)

      Note that if an item has multiple categories, this will use both and only items in all categories will be found.

  1. Larry in AZ says

    Hello Diane.

    I am using the code that you posted above. Thanks for the information. It has caused me to try to expand upon it, but I am having troubles trying to make my ideas work.

    I am trying to create a Search Folder where any of the following are true:

    1) keyword matches found in my "Inbox" by "from", "subject" or found in the message body
    2) keyword matches found in my "Sent Items" by "to", "subject" or found in the message body
    3) keyword matches found in my archived rec'd messages by "from", "subject" or found in the message body
    4) keyword matches found in my archived sent messages by "to", "subject" or found in the message body

    I guess my trouble is building the query.

    Any assistance that you (or anyone else) can provide would be greatly appreciated!

    Thanks in advance.

    • Diane Poremsky says

      Create the query in a search folder and get the sql from the SQL tab - then put it in the code. The sample query at the bottom of the page have some of the fields you'll need, they just need to be put together.

    • Larry in AZ says

      Thanks Diane. However I do not understand your reply. I do not have an SQL tab that I can find. I'm using Outlook 2010.

      Thanks.

  2. Andreas says

    Hi
    I'm using Outlook 2013 ann VB for applications 7.1

    the script works fine but if I change the DASL Filter to the SQL Output of query builder I don't get it to work at all

    VBA does not accept the Syntax of the query builder SQL window

    I would like to have a query like this:

    ("urn:schemas:httpmail:fromname" LIKE '%sender1%' OR ("urn:schemas:httpmail:displayto" LIKE '%sender1%' AND "urn:schemas:httpmail:fromname" LIKE '%sender2%') OR ("urn:schemas:httpmail:displaycc" LIKE '%sender1%' AND "urn:schemas:httpmail:fromname" LIKE '%sender2%'))

    Sender1 is the Person I want to check for
    Sender2 is fix (me)

    overall the script should emulate something like the All by Person from Notes.

    any help or hint is highly welcome

    Kind regards
    Andreas

    • Diane Poremsky says

      I assume you put it together like this (but a lot more complicated :)) With sender1 and sender2, you need a strFilter2.
      strDASLFilter = """urn:schemas:httpmail:fromname"" LIKE '%" & strFilter & "%' " + _
      "OR ""urn:schemas:httpmail:displaycc"" LIKE '%" & strFilter & "%' " + _
      "OR ""urn:schemas:httpmail:displayto"" LIKE '%" & strFilter & "%' " +

    • Andreas says

      Thank you for the prompt reply

      I need the nested conditions with AND because I check inbox and sent items and I only want to see my conversation with this Person and not all the mails sent by others to where this Person is also in to or CC

      It works perfectly as a predefined search Folder using query builder but I don't get it to work in vba

      this is the changed Version but as said the nested condition does not work for me.

      Sub SearchFolderForSender()
      On Error GoTo Err_SearchFolderForSender
      Dim strFilter As String
      Dim strFilter2 As String
      Dim oMail As Outlook.MailItem
      Set oMail = ActiveExplorer.Selection.Item(1)
      strFilter = InputBox("Name for all person")
      strFilter2 = "my Name"

      If strFilter = "" Then Exit Sub

      Dim strDASLFilter As String

      strDASLFilter = ("urn:schemas:httpmail:fromname" LIKE '% strFilter %' OR ("urn:schemas:httpmail:displayto" LIKE '% strFilter %' AND "urn:schemas:httpmail:fromname" LIKE '% strFilter2 %') OR ("urn:schemas:httpmail:displaycc" LIKE '% strFilter %' AND "urn:schemas:httpmail:fromname" LIKE '% strFilter2%'))

      Dim strScope As String
      strScope = "'Inbox', 'Sent Items'"

      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

    • Diane Poremsky says

      This: ("urn:schemas:httpmail:fromname" LIKE '% strFilter %' OR ("urn:schemas:httpmail:displayto" LIKE '% strFilter %' AND "urn:schemas:httpmail:fromname" LIKE '% strFilter2 %') OR ("urn:schemas:httpmail:displaycc" LIKE '% strFilter %' AND "urn:schemas:httpmail:fromname" LIKE '% strFilter2%'))

      needs a lot more double quotes and ampersands. "(" & """urn:schemas:httpmail:fromname""" & "LIKE '%" & strFilter & "%' OR (" &... and so on.

Leave a Reply

Please post long or more complicated questions at OutlookForums by Slipstick.com.

If the Post Comment button disappears, press your Tab key.