Sent Items Cleanup

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:

Sub CleanSent()
    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)
        Loop Until gbASComplete
        If oSearch.Results.Count >= 1 Then
            fld.Items(i).Move oSearch.Results.Item(1).Parent
            Debug.Print “Moved”
            Debug.Print “Not Moved”
        End If
    Next i
End Sub

For which I needed a global variable (gbASComplete) and this event code:

Private Sub Application_AdvancedSearchComplete(ByVal SearchObject As Search)
    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:

Private Sub MoveSentMail(sTopic As String, fldTag As MAPIFolder)
    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
    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.

Posted in Uncategorized

6 thoughts on “Sent Items Cleanup

  1. 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: (shows how to make an example com addin for excel) (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.GetNamespace(“MAPI”).GetDefaultFolder(olFolderSentMail)
    Set fldr = Application.Session.GetDefaultFolder(olFolderSentMail)

    As Session just returns the current namespace object.

  2. 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.

  3. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.