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.)
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().
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.
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 evert 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
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:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
To put the macro code in ThisOutlookSession:
- Expand Project1 and double click on ThisOutlookSession.
- 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