Copy New Appointments to Another Calendar using VBA

This macro "watches" for a new appointment to be added to the calendar and copies it to a second calendar. This is useful if you are sharing a calendar or syncing one calendar with a smartphone.

The code contains and If - Then statement (If Item.BusyStatus = olBusy Then) and copies only items that are marked busy. You can use use Categories or keywords in the subject as the filter or copy all items by removing the If.. and Then lines. You can remove or change the "Copied" tag added to the subject line.

This code, as written, assumes the second calendar is in another data file in the profile. It can easily be changed to work with a folder in the current data file. See Working with VBA and non-default Outlook Folders for more information.

To use:

1. Enable macros in the Trust Center. In Outlook 2010/2013, this is at File, Options, Trust Center, Macros. In Outlook 2007, go to Tools, Trust Center. Use either warn or no security for now. Once you are happy with it, you can sign it with a self-certificate and set macros to signed-only.
2. Open the VB Editor by pressing Alt+F11 on your keyboard.
3. Expand Project1 to display ThisOutlookSession and double click to open it to the right side.
4. Paste the code below into ThisOutlookSession. I have a text file with the macros here: Text file containing the macros to copy, change, and delete appointments.
5. Change the folder path ("display name in folder list\Calendar") to the display name you see in the Folder List (this is usually the email address in Outlook 2010 and 2013). You can see the parent path in the Folder List (Ctrl+6) or right-click on the Calendar folder and choose Properties when in the Calendar module. For example, the path shown in the screenshot is "New PST\Test Cal".
6. Place the mouse in the Application_StartUp macro and press the Run button or F5.
7. Create an appointment in your calendar and see if it was copied to the other calendar.

Tip: The path can be copied from the Properties page, which can be helpful for long paths. Right-click on the calendar, choose Properties to see this dialog.

February 11 2015. Updated the macros to use a GUID. This is better (IMHO) because it allows you to change the time or subject... and works with the delete macro. I'm also setting the move-to-folder as a global variable so you only need to change the path once (in the application_startup macro).

If you prefer to use a random alphanumeric code instead of the GUID, use the last code sample at Create sequential numbers or random character keywords for the necessary VBA code and update the macro accordingly.

Dim WithEvents curCal As Items
Dim newCalFolder As Outlook.folder
 
Private Sub Application_Startup()
   Dim NS As Outlook.NameSpace
   Set NS = Application.GetNamespace("MAPI")
' calendar to watch for new items
   Set curCal = NS.GetDefaultFolder(olFolderCalendar).Items
' calendar moving copy to
   Set newCalFolder = GetFolderPath("data-file-name\calendar")
   Set NS = Nothing
End Sub
  
Private Sub curCal_ItemAdd(ByVal Item As Object)
Dim cAppt As AppointmentItem
Dim moveCal As AppointmentItem
 
' On Error Resume Next

'remove to make a copy of all items
If Item.BusyStatus = olBusy Then
 
   Item.Body = Item.Body & "[" & GetGUID & "]"
   Item.Save
   
Set cAppt = Application.CreateItem(olAppointmentItem)

With cAppt
    .Subject = "Copied: " & Item.Subject
    .Start = Item.Start
    .Duration = Item.Duration
    .Location = Item.Location
    .Body = Item.Body
End With
 
' set the category after it's moved to force EAS to sync changes
 Set moveCal = cAppt.Move(newCalFolder)
 moveCal.Categories = "moved"
 moveCal.Save
 
    End If
 End Sub

Public Function GetGUID() As String
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
End Function

Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder
    Dim oFolder As Outlook.Folder
    Dim FoldersArray As Variant
    Dim i As Integer
        
    On Error GoTo GetFolderPath_Error
    If Left(FolderPath, 2) = "\\" Then
        FolderPath = Right(FolderPath, Len(FolderPath) - 2)
    End If
    'Convert folderpath to array
    FoldersArray = Split(FolderPath, "\")
    Set oFolder = Application.Session.Folders.Item(FoldersArray(0))
    If Not oFolder Is Nothing Then
        For i = 1 To UBound(FoldersArray, 1)
            Dim SubFolders As Outlook.Folders
            Set SubFolders = oFolder.Folders
            Set oFolder = SubFolders.Item(FoldersArray(i))
            If oFolder Is Nothing Then
                Set GetFolderPath = Nothing
            End If
        Next
    End If
    'Return the oFolder
    Set GetFolderPath = oFolder
    Exit Function
        
GetFolderPath_Error:
    Set GetFolderPath = Nothing
    Exit Function
End Function

Copy to a Shared Exchange Calendar

When the calendar you want to copy to or from is in a shared Exchange mailbox, you need to resolve the owner's name or alias and pass it to GetSharedDefaultFolder. You can use the alias, default SMTP address, or display name. I recommend using the alias or email address.

Replace the Application_Startup macro in this text file with the version here.

Private Sub Application_Startup()
  Dim NS As Outlook.NameSpace
  Dim CalendarFolder As Outlook.Folder
  Dim objOwner As Outlook.Recipient
  
  Set NS = Application.GetNamespace("MAPI")
   ' default calendar
Set curCal = NS.GetDefaultFolder(olFolderCalendar).Items

   'calendar you are copying to
Set objOwner = NS.CreateRecipient("maryc")
    objOwner.Resolve
     

 If objOwner.Resolved Then
   'MsgBox objOwner.Name

  Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
  Set Items = newCalFolder.Items
 End If

End Sub

Update copied appointment

If you want to update appointments on the second calendar, add this code sample to the module. This code looks for an appointment with the same GUID. When you save changes, the matching event is also updated.

Because this code looks for the GUID, you can change the subject or start time.

This code is written to work with the ItemAdd macros above and gets the newCalFolder name from the application_startup macro.

 
Private Sub curCal_ItemChange(ByVal Item As Object)
Dim cAppt As AppointmentItem
Dim objAppointment As AppointmentItem
Dim strBody As String    
 On Error Resume Next
      
' use 2 + the length of the GUID
strBody = Right(Item.Body, 38)
 
For Each objAppointment In newCalFolder.Items
 If InStr(1, objAppointment.Body, strBody) Then
         Set cAppt = objAppointment
   End If
 Next

With cAppt
    .Subject = "Copied: " & Item.Subject
    .Start = Item.Start
    .Duration = Item.Duration
    .Location = Item.Location
    .Body = Item.Body
    .Save
End With
  
End Sub

Delete the copied appointment

When you delete the original appointment, the following code will delete the copy as well. Thanks to Adrian for this!

This code watches the deleted items folder for deleted appointments. It gets the newCalFolder name from the application_startup macro.

You'll need to add lines to the top of ThisOutlookSession and Application_Startup.

Updated January 26 2016 to watch the deleted items folder for deleted appointments instead of using the Remove event.

'At top of ThisOutlookSession:
Dim WithEvents DeletedItems As Items

'In Application_Start macro:
Set DeletedItems = NS.GetDefaultFolder(olFolderDeletedItems).Items

Private Sub DeletedItems_ItemAdd(ByVal Item As Object)
' only apply to appointments
If Item.MessageClass <> "IPM.Appointment" Then Exit Sub
' if using a category on copied items, this may speed it up.
If Item.Categories = "moved" Then Exit Sub
 
Dim cAppt As AppointmentItem
 Dim objAppointment As AppointmentItem
 Dim strBody As String
 
On Error Resume Next
 
' use 2 + the length of the GUID
 strBody = Right(Item.Body, 38)
 If Left(strBody, 1) <> "[" Then Exit Sub

For Each objAppointment In newCalFolder.Items
 If InStr(1, objAppointment.Body, strBody) Then
 Set cAppt = objAppointment
 cAppt.Delete
 End If
 Next
 
 End Sub