I'm often asked how to include a person's age on the birthday event in the calendar. While it is logical that people would want to see this, its not a feature in current versions of Outlook. There are a handful of ways you can include the person's age: use a macro or use a custom contact or appointment form to calculate their age. (I have a contact form example here and an appointment form here.)
The custom appointment form is an interesting concept but the form doesn't update automatically, so the age is less likely to be correct. It also shows the current age on all occurrences, not the age for that year. (That is a problem with this macro too, unfortunately.)
Instead of using a form, we can use a macro to calculate a person's age (and anniversary years) and add it to the subject field, in the form of Full Name's Birthday (27 in 2014). To make it easier to construct the subject in future years, the macro writes the original subject to a custom field and uses that value in the new subject.
The age is calculated by subtracting the birth year from the current year. This requires users to run the macro just once per year. It would be possible to get the date difference, but you'd need to run it frequently (weekly or monthly). While this is possible using a task reminder and another macro, identifying the age as 'this year' should meet the needs of many users.
Option Explicit Public Sub UpdateAges() Dim objOL As Outlook.Application Dim objOutlookItem As Object Dim objItems As Outlook.Items Dim objFolder As Outlook.Folder Dim obj As AppointmentItem Dim Age As Integer Dim objProp As Outlook.UserProperty Set objOL = Outlook.Application Set objFolder = Session.GetDefaultFolder(olFolderCalendar) Set objItems = objFolder.Items For Each obj In objItems If (InStr(1, obj.Subject, "Birthday") Or InStr(1, obj.Subject, "Anniversary")) And obj.IsRecurring = True Then With obj ' see if the custom field exists, if not create it If obj.UserProperties("Original Subject") Is Nothing Then Set objProp = obj.UserProperties.Add("Original Subject", olText, True) objProp.Value = .Subject .Save End If ' can use a specific date instead of 'today' (Date) ' Age = DateDiff("yyyy", .Start, "1/1/2015") Age = DateDiff("yyyy", .Start, Date) .Subject = .UserProperties("Original Subject") & " (" & Age & " in " & Format(Date, "yyyy") & ")" .Save End With End If Next Set obj = Nothing Set objOutlookItem = Nothing Set objItems = Nothing Set objFolder = Nothing Set objOL = Nothing End Sub
Calculate the age in years and months
While I think the age reached during the birthday "this year" makes the most sense, if you want to display the age as of "this month" you can do that by replacing a couple of lines in the code above to calculate the age. The subject line in the event would use a format such as Full Name's Birthday (26 years 9 months in Sep 2014).
To do this, you'll need to calculate the date difference and determine if the birthday has occurred yet this year. If not, subtract 1 from the years and calculate the remaining months.
You'll need to run the macro on a regular basis and can set up a task to trigger the macro when a reminder fires. Instructions are below.
Replace the Age and .subject lines (2 lines) in the macro above with the following.
Dim M As Integer Dim strAge as String Age = DateDiff("yyyy", .Start, Date) M = Month(Date) - Month(.Start) ' see if birthday is coming up If M < 0 Then Age = Age - 1 M = 12 + M End If ' only use months if not 0. If M = 0 Then strAge = Age & " years" Else strAge = Age & " years " & M & " months" End If .Subject = .UserProperties("Original Subject") & " (" & strAge & " in " & Format(Date, "mmm yyyy") & ")"
Run macro using a Task Reminder
You can run the macro when a task reminder fires by adding this code to ThisOutlookSession and creating a recurring task with the category "Update Ages". When the reminder fires, the macro runs to update the ages.
Do something when a Task reminder fires for more information.
Private Sub Application_Reminder(ByVal Item As Object) If Item.MessageClass <> "IPM.Task" Then Exit Sub End If If Item.Categories <> "Update Ages" Then Exit Sub End If Call UpdateAges End Sub
How to use macros
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