Monday, March 10, 2008

A "fix" for the barrage of Deleted Items created by Google's Outlook Calendar sync

This article was written when Google's Outlook Calendar Sync tool was first made available to the public. Since then, Google has fixed this problem in their tool. I'm leaving the post here, as the general principle of having Outlook take specific actions (that couldn't be accomplished in a Rule) when new items show up in any Outlook location is still valid.

Google's new Outlook Calendar sync is a terrific idea. Unfortunately (but not unexpectedly) it has some problems. The first thing most people notice is that Outlook Calendar items that involve anyone besides yourself (i.e., you Invited others or someone else Invited you) don't show up in Google Calendar. The FAQ is suppposed to contain a "fix" for this, but people don't seem too happy with this.

The other problem takes longer to notice. The sync tool creates TONS of Appointments on 12/31/1979 at 7:00 pm - and then deletes them. When I say "tons", I mean it. At one point, I had >12,000 items in my Deleted Items, most of which were these 12/31/1979 appointments.

I don't know how to stop the sync from making these Appointments (I assume they're used as some temporary scratchpad (or someone really messed up their code)), but I do know how to keep all those items out of my Deleted Items.

This does require that you turn on Visual Basic (VB) for Applications (VBA) macros in Outlook. This is NOT a security risk that I know of, as I think you're the only one you can add macros in this manner.

In Outlook, go to Tool -> Macro -> Visual Basic Editor. In the left-hand column, there should be a hierarchy that looks like Project1 -> Microsoft Office Outlook Objects -> ThisOutlookSession. (If that's not there, you might need to record a macro and then delete it. This should create the ThisOutlookSession item. Click on ThisOutlookSession in the left-hand column, and then paste this into the main window:

Private WithEvents DeletedItems As Items

Sub Application_Startup()
Dim objNS As NameSpace
Set objNS = Application.GetNamespace("MAPI")
Set DeletedItems = objNS.GetDefaultFolder(olFolderDeletedItems).Items
Set objNS = Nothing
End Sub

Private Sub DeletedItems_ItemAdd(ByVal myItem As Object)
If myItem.Subject = "" Then
' Google's Outlook Calendar sync causes a bunch of Appointments to be create on 12/31/1979 at 7:00pm.
' After the sync, they get "deleted", but you can have THOUSANDS of these in your Deleted Items.
' This will permanently get rid of them (as well as any other items with no Subject).
End If
End Sub

BTW, the same idea can be extended to create an event handler to handle items getting added to a folder that collects all of your automated or junk email. Add a "Private WithEvents" line, and then add this to Application_Startup():
  Set AutomatedItems = objNS.GetDefaultFolder(olFolderInbox).Folders.item("Automated").Items

And then add a new handler to do something like:
Private Sub AutomatedItems_ItemAdd(ByVal myItem As Object)
myItem.UnRead = False
End Sub