TL;DR? Here is some code to find the category ID for tagged emails in Outlook for OS X, plus a supporting script to search all your emails for a mail which matches the right criteria, and lastly the line to use in Outlook with the resultant value.
Late Edit 2021-11-12: OMG I’m such an idiot! All of this information can be found in an easily searchable sqlite database! Run this command: sqlite3 ~/Library/Group\ Containers/*.Office/Outlook/Outlook\ 15\ Profiles/Main\ Profile/Data/Outlook.sqlite "SELECT Record_RecordID,Category_Name FROM Categories;" If you really want to do it the hard way, look below! You can also do this search if you want to find specific folder names: SELECT Record_RecordID,Folder_Name FROM Folders; and use it in conjunction with this raw search: com_microsoft_outlook_folderID
In a bit of a surprising move on my part [1], I’m now using an Apple Mac, running OS X for my work computer. I’ve used Microsoft Outlook for almost all my working life as an email client, and moving to the (quite frankly) hobbled version of Outlook on OS X nearly broke me.
[1] After hearing some horror stories 15ish years ago from peers who developed code for Apple devices (iPhone and Mac), I was often considered to be fairly rabid about not using anything from Apple. That was until I got called out on this a couple of years ago, and I softened my stance from “Will not use” to “I’ve never used, so far”. When I changed companies in September, I figured that I should really at least give them a shot (after all, they’ve never actually done any harm to me, either directly or indirectly) and see what all the fuss is about. In addition, with changing job, everything from company, to job type and even into field entirely, I decided that I may as well actually learn a new OS, as I didn’t think anyone would expect me to be particularly productive within the first 30-60 days. Also, when I was hired, I was told (incorrectly) that Linux was not an option, so my other “option” was to run Windows… again. I’ve now had this incorrect advice corrected, and while I’m not giving up this Mac any time soon, when it comes time for a refresh, I may well sport a Linux device next 😄
Many of my intended action plans around email management suddenly dried up (macros and quick actions just don’t exist on the Mac), and now I’m looking at using the Smart Folders in combination with email Categories instead… but you can’t do an “or” search (category X or category Y) without using the “Raw Query” tool… which doesn’t use the category name, instead it uses an ID number.
To find out what the category number is, I found an email which had an easy-to-search title, and added the relevant category to it. I then popped open the terminal, and (with the help of a few internal and stack-overflow posts) wrote this inelegant function:
function find_category() {if grep -i "$1" "$2" 2>&1 >/dev/null ; then mdls "$2" | perl -ne 'print if (/com_microsoft_outlook_categories/.../\)/)' ; fi}Perl line, thanks to this SO answer: https://stackoverflow.com/a/36393093/5738
This function runs a grep command to find our search term against a file we picked, and if it finds it, then it runs the mdls command which dumps all the OS X metadata out. Finally it uses perl to search for a multi-line string (effectively grep -E with several extra switches, which don’t exist on the OS X version of grep) to dump the output.
While I was writing this post, I suddenly thought I should find out what (if anything) mdls stood for, so I ran man mdls and found that it wasn’t titled (but I suspect it means metadata list) but also found that instead of running some weird perl command to get the field I wanted, I could just pass it a -name com_microsoft_outlook_categories switch, and get just the field I wanted, so that function now looks like this:
function find_category() {if grep -i "$1" "$2" 2>&1 >/dev/null ; then mdls -name com_microsoft_outlook_categories "$2" ; fi}Note: I left the original version of that function in this post, because searching for multi-line strings that match regex-like patterns is actually quite useful, even though in this case I didn’t need to use it!
I ran this across my entire email store (which, in OS X Outlook, is stored as a collection of files, not one giant database – AKA the PST or OST file) to find the record I wanted, like this:
find ~/Library/Group\ Containers/*.Office/Outlook/Outlook\ 15\ Profiles/Main\ Profile/Data/Messages -exec find_category "My Search Term" '{}' \;And, with that I got:
com_microsoft_outlook_categories = (
30
)Huzzah! So, now I know my category (SSSH - Super Secret Customer) has an ID of 30, and I also found (using the same method) that the category ToMyTeam has an ID of 31, so I can search for that with this Smart Folder search:
com_microsoft_outlook_categories == 30 || com_microsoft_outlook_categories == 31There’s a whole slew of other interesting searchable criteria in there too… like this:
com_microsoft_outlook_unread == 1 && (com_microsoft_outlook_flagged == 0 || (com_microsoft_outlook_completed == 0 && kMDItemDueDate <= $time.today(0)))This means, search for mails which are unread (com_microsoft_outlook_unread == 1) AND which are either unflagged (com_microsoft_outlook_flagged == 0), OR mails which have not been completed (com_microsoft_outlook_completed == 0) AND have a “due date” reminder set for sometime before today (kMDItemDueDate <= $time.today(0)).
Unfortunately, where I would normally credit the sources for my post, I lost almost all of the browser tabs I had this content in now, so I can’t.
