A couple weeks ago, I had more than 1,200 emails in my Inbox. Now I have none. I’ve been using, and tweaking, my Outlook Tags macro to clean and maintain my mail. In addition to handling new mail in a timely manner, I processed a couple hundred old emails every day. I really gained an appreciation for writing good subjects. There’s one person in my office who writes terrible subjects. I had to open almost every email she sent me to see what was in it before I could classify it. Moving those emails everyday took about 10 minutes.
I moved the last of them on Saturday and then set my sights on the Sent Items folder. Saturday morning I had 1,409 items in that folder. Cleaning the inbox felt like a real accomplish. Seeing the number of items in Sent Items made me feel like Nando Parrado when he reached the first summit only to find Andean peaks as far as the eye could see. I took care of a good chunk of it with this macro:
Dim fld As MAPIFolder
Dim oSearch As Search
Dim i As Long
Dim sTopic As String
Set fld = Application.GetNamespace(“MAPI”).GetDefaultFolder(olFolderSentMail)
For i = fld.Items.Count To 1 Step -1
gbASComplete = False
sTopic = Replace(fld.Items(i).ConversationTopic, “‘”, “””)
Set oSearch = Application.AdvancedSearch(“Inbox”, _
“”“urn:schemas:httpmail:thread-topic”” = ‘” & sTopic & “‘”, True)
Loop Until gbASComplete
If oSearch.Results.Count >= 1 Then
Debug.Print “Not Moved”
For which I needed a global variable (
gbASComplete) and this event code:
gbASComplete = True
The AdvancedSearch works asynchronously, meaning that the rest of the macro continues to run before the search is complete. Thus the need for the event. It prevents the macro execution from continuing until the search is complete.
To get the Filter argument of the AdvancedSearch method, I went to the Outlook UI, specifically
View > Arrange By > Current View > Customize Current View. I then clicked on the Filter button and set up a filter the way I wanted. Finally, I switched to the SQL tab to get the syntax.
If the search returned any items, I moved the sent item to the same folder as the first item in the search. It took almost two hours for this macro to execute and it knocked the item count down to 666. Next I sorted on subject and deleted all of the email I didn’t want to save, like those five emails from a Thursday last summer when I needed a sub for my golf league. That got me down to 257, which I disposed of in about 20 minutes using the Outlook Tags userform.
Now I have a clean inbox, a clean Sent Items folder, and a plan for keeping them that way. What I also have is some broken code. To keep the Sent Items folder clean, I checked the last five emails in Sent Items for related messages. Now that the count is zero, the code fails. I changed that code to look like this:
Dim mi As Object
Dim i As Long
Dim fldr As MAPIFolder
Dim lInclude As Long
Set fldr = Application.GetNamespace(“MAPI”).GetDefaultFolder(olFolderSentMail)
‘lInclude = Application.Min(fldr.Items.Count, 5)
If fldr.Items.Count < 5 Then
lInclude = fldr.Items.Count – 1
lInclude = 5
If lInclude > 0 Then
For i = fldr.Items.Count To fldr.Items.Count – lInclude Step -1
Set mi = fldr.Items(i)
If mi.ConversationTopic = sTopic Then
I left that commented line in there for your amusement. Guess which application I’m used to programming in.
I didn’t get a chance to test this in Outlook 2007 like I’d hoped, but I plan to do that soon. Then I suppose I’ll have to port this over to a COM Add-in. I don’t really know how to write distributable code for Outlook. There doesn’t appear to be non-COM Add-ins, at least that I can see. If anyone has experience with that, leave a comment.