Since a lot of people at work may be using the Outlook mail client, I thought it would be useful to introduce the scripting capabilities that Visual Basic for Applications (VBA) offers in Outlook. (This is also the first post where I will be using the code->HTML converter from Notepad++. Trying this as an alternative to vim. Let me know what you think.)
This post demonstrates a simple script to delete duplicate emails in an Outlook folder. While this seems quite specific, it should present enough info for you to get started with VBA for Outlook. So here goes:
Hmm, why would anyone want to do Visual Basic scripting in Outlook??
I have long been a proponent of VBA (Visual Basic for Applications), specially in MS Excel and CorelDraw, but only recently have I got my hands dirty with Outlook.
The problem I faced a while back was that I accidentally created two copies of every email in my conversations folder. There being around 250 items to sift through, I decided it would be better to spend a half hour learning how to write Outlook macros, than to find and delete the duplicate items.
So, lets get down to business. To write a macro in Outlook, go to Tools->Macros->Visual Basic Editor.
In the Project window, select Project1->Microsoft Office Outlook->ThisOutlookSession. You should be presented with a code window, where you will write the macro.
The macro will be a VB subroutine. In this post, I will discuss how to write a macro that moves only duplicate emails to some folder of your choice (maybe trash). We could delete the emails instead, but I usually refrain from writing destructive statements in code, atleast until it is fully tested ;)
Before we start off, this is the URL that I referred to for help: http://support.microsoft.com/?kbid=208520
I will provide the code listing below, with inline comments (comment lines start with an apostrophe in VB).
'We start off with the name of the subroutine,
'Also make it public, so outlook can access it.
'Sub means Subroutine
Public Sub deleteDuplicateMails()
'Now for some variable declarations.
'Note that this is not needed in VB, but
'its a good practice, and also helps VB to
'autocomplete your code.
Dim oldEmail As MailItem, newEmail As MailItem
Dim i As Integer, dupcount As Integer, totalcount As Integer
Dim folder1 As Folder, folder2 As Folder
Dim myfolder As Folder, dupfolder As Folder
'Now, we need to access the folders where
'emails are stored. The following lines do that.
'This is to help VB access the stuff inside outlook
'MAPI is "Messaging Application Programming Interface"
Set ol = New Outlook.Application
Set olns = ol.GetNamespace("MAPI")
'Note: I am assuming these folders and subfolders
'already exist. The code won't run otherwise.
'We could make it more robust, and create
'the folders here, but thats too much work :P
'set the source folder
Set folder1 = olns.Folders("Personal Folders")
'go to subfolder inside folder1
Set myfolder = folder1.Folders("Conversations")
'set the destination folder
Set folder2 = folder1.Folders("Deleted Items")
'again, we want a subfolder here
Set dupfolder = folder2.Folders("Conversations")
'sort items based on sent-on date and time
Set myitems = myfolder.Items
myitems.Sort "[SentOn]", olAscending
'iterate through mails, checking for duplicates
totalcount = myitems.Count
dupcount = 0
'start from end and go towards first item, deleting duplicates from the end
For i = totalcount - 1 To 1 Step -1 'assuming there are atleast 2 emails
Set oldEmail = myitems(i)
Set newEmail = myitems(i + 1)
'check if 2 emails are same
If (oldEmail.ConversationTopic = newEmail.ConversationTopic) And _
(oldEmail.SentOn = newEmail.SentOn) _
Then
'no short circuit of boolean expressions, so we have inner if statement
'len(oldEmail.Body): comparing message length, faster than full string compare
If (Len(oldEmail.Body) = Len(newEmail.Body)) Then
dupcount = dupcount + 1
newEmail.Move dupfolder
End If
End If
Next i
MsgBox "Duplicates: " & dupcount
'clean up any references
Set myitems = Nothing
Set myfolder = Nothing
Set dupfolder = Nothing
Set folder2 = Nothing
Set folder1 = Nothing
Set olns = Nothing
Set ol = Nothing
End Sub
'Also make it public, so outlook can access it.
'Sub means Subroutine
Public Sub deleteDuplicateMails()
'Now for some variable declarations.
'Note that this is not needed in VB, but
'its a good practice, and also helps VB to
'autocomplete your code.
Dim oldEmail As MailItem, newEmail As MailItem
Dim i As Integer, dupcount As Integer, totalcount As Integer
Dim folder1 As Folder, folder2 As Folder
Dim myfolder As Folder, dupfolder As Folder
'Now, we need to access the folders where
'emails are stored. The following lines do that.
'This is to help VB access the stuff inside outlook
'MAPI is "Messaging Application Programming Interface"
Set ol = New Outlook.Application
Set olns = ol.GetNamespace("MAPI")
'Note: I am assuming these folders and subfolders
'already exist. The code won't run otherwise.
'We could make it more robust, and create
'the folders here, but thats too much work :P
'set the source folder
Set folder1 = olns.Folders("Personal Folders")
'go to subfolder inside folder1
Set myfolder = folder1.Folders("Conversations")
'set the destination folder
Set folder2 = folder1.Folders("Deleted Items")
'again, we want a subfolder here
Set dupfolder = folder2.Folders("Conversations")
'sort items based on sent-on date and time
Set myitems = myfolder.Items
myitems.Sort "[SentOn]", olAscending
'iterate through mails, checking for duplicates
totalcount = myitems.Count
dupcount = 0
'start from end and go towards first item, deleting duplicates from the end
For i = totalcount - 1 To 1 Step -1 'assuming there are atleast 2 emails
Set oldEmail = myitems(i)
Set newEmail = myitems(i + 1)
'check if 2 emails are same
If (oldEmail.ConversationTopic = newEmail.ConversationTopic) And _
(oldEmail.SentOn = newEmail.SentOn) _
Then
'no short circuit of boolean expressions, so we have inner if statement
'len(oldEmail.Body): comparing message length, faster than full string compare
If (Len(oldEmail.Body) = Len(newEmail.Body)) Then
dupcount = dupcount + 1
newEmail.Move dupfolder
End If
End If
Next i
MsgBox "Duplicates: " & dupcount
'clean up any references
Set myitems = Nothing
Set myfolder = Nothing
Set dupfolder = Nothing
Set folder2 = Nothing
Set folder1 = Nothing
Set olns = Nothing
Set ol = Nothing
End Sub
And that completes the code. VBA can be used to write all kinds of neat macros that speed up day to day tasks. Finding duplicate mails is just one of them.
