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
Debug.Print fld.Items(i).Subject
gbASComplete = False
sTopic = Replace(fld.Items(i).ConversationTopic, “‘”, “””)
Set oSearch = Application.AdvancedSearch(“Inbox”, _
“”“urn:schemas:httpmail:thread-topic”” = ‘” & sTopic & “‘”, True)
Do
DoEvents
Loop Until gbASComplete
If oSearch.Results.Count >= 1 Then
fld.Items(i).Move oSearch.Results.Item(1).Parent
Debug.Print “Moved”
Else
Debug.Print “Not Moved”
End If
Next i
End Sub
For which I needed a global variable (gbASComplete) and this event code:
gbASComplete = True
End Sub
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
Else
lInclude = 5
End If
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
mi.Move fldTag
End If
Next i
End If
End Sub
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.
Dick,
Correct, COM add-ins are the only type of addin you can have for outlook (I can only assume for security reasons). Dennis has posted a couple examples here, though I don’t have the links readily available, but if you’re creating them from VB6 (easy), the following links should help:
http://www.thecodenet.com/articles.php?id=38 (shows how to make an example com addin for excel)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2kta/html/mso2kaddin.asp (from MSDN – explains it a bit more in-depth, though it was written for office 2k)
Last year I made a couple (one to make outlook blink the scroll lock light when mail was received, the other was work related but did a lot more outlook-centric code. I’d be happy to send you the source of either if you’re interested.
Also, though it probably would never make a difference for you, rather than use GetNamespace(“MAPI”), you can just use Session:
Set fldr = Application.Session.GetDefaultFolder(olFolderSentMail)
As Session just returns the current namespace object.
Thanks Matt, good tips. I’d love to take a look at the source if you don’t mind sending it.
If I remember to move an incoming message from the inbox to the appropriate folder before I reply, the reply stays in the folder with the message I replied to.
Hi Dick,
I know that you are a fellow programmer, but just for kicks I thought I would mention the Sent Items Organizer, an Outlook add-in that automatically files your emails as they are sent. See http://www.SperrySoftware.com/Outlook/Sent-Items-Organizer.asp.
Thanks!
Mike Sperry
Sperry Software
http://www.SperrySoftware.com
Find 35 Outlook add-ins that can make a difference!
Those look like some nice add-ins Mike. No 30 day trial, or did I miss it? And that Auto Print add-in should be illegal. :)
Hi Dick,
I want to calculate the response time of a email(received From the list of people I have in a sheet). Please help me out in this. I need this very much. your help will be of great help.
Thanks in advance
Arun