Outlook 2011 Smart Folder with Raw Query

There’s a big feature missing from Outlook 2011 for the Mac. Okay, a number of big features, including being able to edit server-side rules. But the one that’s been affecting me most is the inability to have Smart Folders that have OR clauses.

I subscribe to a number of mailing lists, and what I wanted was for all the messages to those lists to be delivered to subfolders (so they’re not in my Inbox), but I wanted to be able to see all the unread messages in all those folders, in one place.

Outlook 2011’s Advanced Search lets you search for unread messages, and search for messages in a combination of folders, but not both.

But all is not lost. Outlook 2011 on the Mac uses Spotlight to index mail, and supports creating queries that are passed right through to the Spotlight query engine. You can access this using Advanced Search in Outlook, and setting the search type to “Raw Query”.

201103240929

You can enter any query here that’s supported by Spotlight. Here’s a list of Spotlight attributes that you can use in your queries, and from that page you can find information on the query syntax.

For example, to find all messages written by people named Steve:

201103240934

Note that in the Search ribbon bar, there are settings for the scope of the search. These don’t get saved with the search, so you need to set them to where you want the search to be applied.

 Users Stevex Library Application-Support Evernote Data 101370 Content P1040 7831437B6F570Ffee97Fee5Bc5Cac224

So back to my problem. I wanted to find all the unread messages in a number of folders. I need to be able to query for two different things: Whether or not a messages is Unread, and what folder a message is in. These aren’t Spotlight attributes. They’re Outlook attributes. How do you find a list of these?

Well, not on the web, as far as I can see, but they are there in the file system. If you use the Mac command “mdfind” you can find a file containing some particular text, so I created a message with the text “xyzzy” in it (by attaching a text file with that name), and then did an mdfind to locate it. The message was in a file on disk:

/Users/stevex/Documents/Microsoft User Data/Office 2011 Identities/Main Identity/Data Records/Messages/0T/0B/0M/21K/x00_21535.olk14Message

An mdls on that file yields the list of Outlook metadata:
com_microsoft_outlook_accountID = 1
com_microsoft_outlook_attachments = (
"xyzzy.txt"
)
com_microsoft_outlook_author_email_addresses = (
"stibbett@adobe.com"
)
com_microsoft_outlook_categories = (
0,
0
)
com_microsoft_outlook_completed = 0
com_microsoft_outlook_flagged = 0
com_microsoft_outlook_folderID = 121
com_microsoft_outlook_forwarded = 0
com_microsoft_outlook_has_attachments = 1
com_microsoft_outlook_has_reminder = 0
com_microsoft_outlook_has_text_content = 1
com_microsoft_outlook_hasDueDate = 0
com_microsoft_outlook_hasStartDate = 0
com_microsoft_outlook_isFromMailingList = 0
com_microsoft_outlook_messageSent = 2011-03-24 09:38:23 -0400
com_microsoft_outlook_primaryCategory = 0
com_microsoft_outlook_priority = 0
com_microsoft_outlook_recordID = 18520
com_microsoft_outlook_redirected = 0
com_microsoft_outlook_repliedTo = 0
com_microsoft_outlook_size = 1
com_microsoft_outlook_unread = 1

And there are the properties I’m looking for: unread, and folderID.

folderID? How do I find out what the folderID is for the folders I’m interested in?

Well, one way would be to use mdfind to find messages in these folders and mdls to extract them, but that’s a pain. Here’s a slightly simpler way, using AppleScript.

Launch AppleScript Editor (a utility that comes with every Mac), and paste in this text:
tell application "Microsoft Outlook"
get selected folder
end tell

Select the folder whose ID you want, and click the Run button in the AppleScript Editor. You should see something like this:

201103240944
You can use this to get the Folder ID for each folder you want to include in your Smart Folder.

The last step is forming the query. Now that we have all the information it’s pretty easy:
com_microsoft_outlook_unread != 0 &&
(com_microsoft_outlook_folderID == 123 ||
com_microsoft_outlook_folderID == 234)

Substitute your own folder IDs for 123 and 234, and add as many more (or less) clauses as you need. Paste this into the Raw Query box, make sure you have All Folders selected in the ribbon, and you should see the unread messages in those folders!

Click the Save button on the Search ribbon to save your smart folder.

There, now, that was easy wasn’t it? :)