Outlook 2007/2010 Search Folders using email address domain

As of May 2010, the Beta of Outlook 2010 still hasn’t overcome this problem. I’m surprised this glaring omission has been left unfixed. Maybe Outlook is maintained by contractors?

I have what I consider to be a simple need. I want a “Search Folder” that shows me all the email related to a particular client. What works well for me is a query that finds any email with the client name in the subject, or any email that involves an email address (from/to/cc) from the client's email domain. Back in Thunderbird, it was simple to setup a rule for this. Outlook can't do it. (I'd go back to Thunderbird, but I have to get calendaring working first.)

What doesn't work

When you edit the criteria for a Search Folder, on the “Messages” tab, the fields you want appear to be represented, but the way things work is wrong. All the criteria specified must be true, not any; they went with “AND” where I need “OR”. The other problem is that the “From…” and “Sent To…” fields use a “starts-with” match, not a “contains” or “ends-with”. So that’s useless.

The next possibility is the “Advanced” tab. Unfortunately, this contains the key mistake that the Outlook development team made. The “From”, “To”, and “Cc” fields use some sort of “display” versions of the data; they often don’t have the address, so they’re also worthless. And the hidden "Query Builder" tab has the exact same problem. The system in Thunderbird is clearly superior---both in terms of usability and functionality. Outlook is confused and crippled.

There is only one option left: the “SQL” tab available when applying a filter. The problems here are these:

• You have to add a "Filter..." button to the button-bar to get to the dialog box that has the "SQL" tab needed. (You can't get to it from the Search Folder's "Criteria..." dialog.)
  
• Documentation for the "SQL" tab is almost non-existent.
• In the end, it's still not totally possible.
  

Searching for help

The first place I found help was a blog post by Andrew Delin (Doing more with Outlook filter and SQL DASL syntax) and another by John Wood (Using an SQL Filter in Outlook 2002/2003). The next thing I did was open up the filter dialog and setup criteria on the "Messages" tab, then switch to the "SQL" tab to see what SQL got auto-generated. That actually made things worse for me since the blogs all talked about this "urn:" stuff and Outlook generated "http:" items with hexadecimal numbers.

Subsequent searching turned up these key pages:

• A namespace reference by Microsoft. This gives a starting point.
• A listing of properties that "messages" have. This revealed a "mailheader" in addition to "httpmail".
• A (good enough) reference on the filter SQL syntax by Microsoft. This reveals why "LIKE" is the only operator for me.
  
• A listing of MAPI property IDs at wireshark.org. This gave me descriptions of the hex numbers that Outlook auto-generated.

Sent Items

During my searching I stumbled upon a significant caveat that hasn't been disclosed anywhere except on this thread here. To quote:

Messages in your own Sent Items folder won't have any Internet headers. You should be able to see that with Outlook Spy or MFCMAPI.exe.

What does that mean? No filters properly work on the "Sent Items" folder when it comes to email addresses (or anything else that depends on header/metadata information); you're stuck with only subject and body. Apparently messages I send aren't "real" emails; the people who designed Outlook think people couldn't possibly want to know about email they've sent to others. I have not been able to find a workaround.

Solution

The only way to remain sane is to concede that the "Sent Items" folder is largely a lost cause. After that, it's "easy". Here's the query I use for a client:

"http://schemas.microsoft.com/mapi/proptag/0x0037001f" LIKE '%clientname%'
OR
"http://schemas.microsoft.com/mapi/proptag/0x0065001f" LIKE '%@emaildomain.com'
OR
"http://schemas.microsoft.com/mapi/proptag/0x0e04001f" LIKE '%@emaildomain.com'
OR
"http://schemas.microsoft.com/mapi/proptag/0x0e03001f" LIKE '%@emaildomain.com'

If this turns up items in "Sent Items", there are 2 reasons: First, it matched the subject. Second, you sent the email via IMAP and the client you used put a copy of the message you sent into "Sent Items" (e.g. Thunderbird in my case).

I used these properties:

• PR_SUBJECT_UNICODE (0x0037001f)
  
• PR_SENT_REPRESENTING_EMAIL_ADDRESS (0x0065001f)
• PR_DISPLAY_TO_UNICODE (0x0e04001f)
• PR_DISPLAY_CC_UNICODE (0x0e03001f)

Here are a couple others that looked useful, but I didn't end up using them for one reason or another:

• PR_SENDER_EMAIL_ADDRESS_UNICODE (0x0c1f001f)
• PR_SENT_REPRESENTING_EMAIL_ADDRESS_UNICODE (0x0065001f)
• PR_TRANSPORT_MESSAGE_HEADERS_UNICODE (0x007d001f)