This macro checks the time zone an appointment is in and adds the time zone name as a category and/or a custom field. Why would you need this? If your appointments are duplicated during a sync process when you are traveling in different time zones and the duplicates are in the other time zone. Or you just want to easily see what time zone the appointments were created in.
I synced my phone calendar using Samsung Kies 3 (as I have for a few years). Somewhere during or after my trip, every single appointment I've had from October 2003 till January 2015 has been doubled: one copy on New York time, and a second copy on Dublin time. There are almost 6,000 entries. If I could sort them by time zone, I could quickly delete all the duplicates, but I haven't been able to find a way to do that.
While you can't sort by time zone, we can use categories or a custom text field and group by the field then delete the group. The advantage of using a custom field is that it doesn't touch the categories, reducing the risk that you'll make a mistake when you remove the time zone category from the remaining appointments and accidentally categories.
This macro is based off of the handy-dandy macro at Working with All Items in a Folder or Selected Items.
I'm using the macro on the default calendar folder but you can use the selected calendar folder by changing the objFolder:
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Switch to a list view then run the macro. Add the TZ field to the view. In Outlook 2010 and above:
- Click View Settings button (View tab)
- Click Columns
- In Select available columns from dropdown, select User-defined fields in folder.
- Select TZ field and click Add.
- Close the dialog and return to Outlook.
- Right click on the TZ field and choose Group by this field.
- If you need to delete the appointments, select the Group name and press Delete.
Public Sub CategorizeTimeZones() Dim objOL As Outlook.Application Dim objOutlookItem As Object Dim objItems As Outlook.Items Dim objFolder As Outlook.MAPIFolder Dim obj As Object Dim strCat as String Dim objProp As Outlook.UserProperty Set objOL = Outlook.Application Set objFolder = Session.GetDefaultFolder(olFolderCalendar) 'objOL.ActiveExplorer.CurrentFolder Set objItems = objFolder.Items For Each obj In objItems With obj Debug.Print "Time Zone is " & .StartTimeZone strCat = .StartTimeZone ' Remove the ' to add the time zone name as a category '.Categories = StrCat & "," & .Categories Set objProp = .UserProperties.Add("TZ", olText, True) objProp.Value = strCat .Save End With Next Set obj = Nothing Set objOutlookItem = Nothing Set objItems = Nothing Set objFolder = Nothing Set objOL = Nothing End Sub
How to use this macro
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor