A Slipstick.com visitor asked how to assign a category to his appointments automatically, as soon as they are over.

You can do this using a macro with a trigger, such as the meetings own reminder or the next appointment reminder, that will kick off the macro. The other option is to run a macro manually, such as at the end of the day.

If you don't assign color categories to any appointments, you can use a custom view to apply automatic formatting colors to old appointments. Category colors take precedence, so this method only works on non-categorized appointments and events.

This macro is triggered by an appointment reminder and checks all appointments with End times between Now and 3 days ago. If you don't restrict it to recent events, the macro will check every appointment, which could take several minutes. (I used 3 days to cover days when there are no appointments.)

Move old Appointments to an Archive Calendar

To keep any existing categories, use Appt.Categories = "Completed;" & Appt.Categories or Appt.Categories = "Completed" to erase categories and replace them with the Completed category.

To use the Appointment start date, use Appt.Start < Now().

How to use the VBA Editor

Set the category when a reminder fires

This macro code goes into ThisOutlookSession. When an appointment reminder fires, it runs. To run it when any reminder fires, remove the If... End If code block.

Private Sub Application_Reminder(ByVal Item As Object)
If Item.MessageClass <> "IPM.Appointment" Then
  Exit Sub
End If
 
Dim Appt As Object
Set Items = Session.GetDefaultFolder(olFolderCalendar).Items
 
For Each Appt In Items
 
On Error Resume Next
 
If Appt.End < Now() And Appt.End> Now() - 3 Then
    Appt.Categories = "Completed;" & Appt.Categories
    Appt.ReminderSet = False
    Appt.Save
End If
 
Next
 
Set Appt = Nothing

End Sub

Set the category using a macro

This macro can be placed in a module or in ThisOutlookSession and assigned to a button on the ribbon or QAT for easy access.

If you want to make the change at the end of the day, you can run this macro to change all appointments with a start time before now.

Remove reminders from appointments that occur in the past

Public Sub AddCategory()
 Dim Appt As Object

  Set Items = Session.GetDefaultFolder(olFolderCalendar).Items
   For Each Appt In Items
    On Error Resume Next

If Appt.End < Now() Then
    With Appt
       .Categories = "Completed"
       .ReminderSet = False
       .Save
    End with
End If
 
Next
 
Set Appt = Nothing

End Sub

Using an ItemAdd macro to set the category

This macro is an ItemAdd macro and runs when a new event is added to your calendar.

It looks for words in the subject and sets a category based on the word. If the words you are looking for are also the Category name, you only need one array and would set the category using:
.Categories = arrCode(i)

This macro needs to be in ThisOutlookSession.

Private WithEvents calItems As Outlook.Items

Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

Set calItems = olNS.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub calItems_ItemAdd(ByVal Item As Object)
Dim arrKey
Dim arrCat

strCode = Item.Subject

' Set up the array
arrKey = Array("works", "test", "share", "word4", "word5")
arrCat = Array("red", "blue", "green", "holiday", "vacation")

' Go through the array and look for a match, then do something
For i = LBound(arrKey) To UBound(arrKey)
    If InStr(LCase(strCode), arrKey(i)) Then
     .Categories = arrCat(i)
     .ReminderSet = False
     .Save
    Exit Sub
    End If
Next i

End Sub

Use ItemAdd to watch multiple calendars

Like the previous macro, this macro is an ItemAdd macro and runs when a new event is added to your calendar. It "watches" the default calendar plus two subfolders of the default calendar.

Private WithEvents calFolder As Outlook.Folder
Private WithEvents calItems As Outlook.Items
Private WithEvents personalItems As Outlook.Items
Private WithEvents familyItems As Outlook.Items

Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

Set calFolder = olNS.GetDefaultFolder(olFolderCalendar)

Set calItems = calFolder.Items

' subfolders of default calendar
Set personalItems = calFolder.Folders("Personal").Items
Set familyItems = calFolder.Folders("Family").Items

MsgBox "App Start Started"
End Sub

Private Sub calItems_ItemAdd(ByVal Item As Object)
AddCategories Item
End Sub

Private Sub personalItems_ItemAdd(ByVal Item As Object)
AddCategories Item
End Sub

Private Sub familyItems_ItemAdd(ByVal Item As Object)
AddCategories Item
End Sub

Private Sub AddCategories(ByVal Item As Object)
Dim arrKey
Dim arrCat

'set variable to check for subject, drop to lower case
strCode = LCase(Item.Subject)
Debug.Print strCode

' Set up the array for subjects to match
' Items in arrKey MUST be lowercase !!
arrKey = Array("remote sessie", "service call", "test")
arrCat = Array("MyBusiness", "Service", "Test")

' Go through the array and look for a match, then do something
For i = LBound(arrKey) To UBound(arrKey)
   'MsgBox "Item Processed", , "Message"
   Debug.Print i, InStr(strCode, arrKey(i))
   If InStr(strCode, arrKey(i)) Then
    With Item
     .Categories = arrCat(i)
     .ReminderSet = True
     .Save
    End With
   Exit Sub
   End If
Next i

End Sub

How to use the macros on this page

First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.

To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look 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.

Macros that run when Outlook starts or automatically need to be in ThisOutlookSession, all other macros should be put in a module, but most will also work if placed in ThisOutlookSession. (It's generally recommended to keep only the automatic macros in ThisOutlookSession and use modules for all other macros.) The instructions are below.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

1. Right click on Project1 and choose Insert > Module
2. Copy and paste the macro into the new module.

To put the macro code in ThisOutlookSession:

1. Expand Project1 and double click on ThisOutlookSession.
2. Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)

More information as well as screenshots are at How to use the VBA Editor