I’ve been using Outlook at work and for my personal mail for years and I have always had issues with the ability to view or segregate my email according to specific criteria. I’ve always felt I was was limited and could not attain the kind of granularity I sometimes desired. I blamed this on the inability to use complex AND/OR logic in the selection process.
Having had a technical career in IT for many years, I’ve subscribed to many newsletters and various groups. Consequently, I receive a fair amount of email on a daily basis. I’m also a pack-rat and I don’t like to discard ANYTHING. Well, at least not anything that is under a few years old. This being the case, my mailbox can become really cluttered, especially if I have not had a chance to read through it in several days succession. We all get busy in our work and our lives at times and it becomes difficult to read all of our mail.
Over the years, I had developed a number or rules to segregate mail from my inbox into appropriate folders. However, as groups have changed their from address or have included other criteria in their name or address, the rules broke down. My rules just were not cutting the mustard.
A few weeks ago while researching a totally unrelated technological issue, I stumbled upon a Google entry with a topic of Dan Brett’s Blog: Give Outlook a GMail Conversation view. I thought this looked pretty interesting! I was familiar with Outlook’s ability to group messages by conversation but was always impressed with GMAIL’s more complete method (replies are grouped into a GMAIL conversation).
Dan’s article revealed to me the ability to use SQL syntax within a View or a Search Folder. Well whatayaknow! Instead of depending on a bunch of rules to try and segregate mail as it comes in, I could enhance the ability to categorize, group, and view my mail by using Search Folders and Views which utilize advanced (but simple to build) SQL queries.
I followed Dan’s article but still ran into some issues when trying to reference the many different context objects which can be referenced in the SQL. I found the objects as documented by Microsoft but had a difficult time understanding their correct use. The Microsoft documentation on this leaves a lot to be desired. However, with some more research, I came across another very fine article: Using a SQL Filter in Outlook 2002/2003 Views. This article pointed out the ability to use the Advanced tab to construct selection criteria with available fields and then switching to the SQL method to manipulate and enhance the generated SQL statement.
As an example: I previously had a Search folder to view all email articles I receive from various Techtarget subscriptions. However, I did not want to see the article which I had committed to the Deleted Items folder. At the time I first constructed the SQL and using the Microsoft context documentation, I could not determine what context to reference or even what to look for in the context. Using knowledge gained from the Using a SQL filter in Outlook 2002/2003 article, mentioned above, I used the Advanced tab to select criteria for excluding a message in the Deleted Items folder
Now… looking at the SQL tab, I could see the generated SQL query
I then copied the generated SQL to notepad so I could combine it with my original Search query. The original query to find all my Techtarget articles looks like this: “urn:schemas:httpmail:fromemail” LIKE ‘%techtarget%’. After including the generated query and changing it to a LIKE operator, I came up with this SQL: “urn:schemas:httpmail:fromemail” LIKE ‘%techtarget%’ AND NOT “http://schemas.microsoft.com/mapi/proptag/0x0e05001f” LIKE ‘%deleted%’.
This criteria now delivers the exact results I am looking for.