Automatic Project Manager Notifications


This post is primarily for Tom Hegh, although anyone is welcome to use it or build on the concept. Tom contacted me last week and asked if there was some way to get Outlook to send a notification message to the PM of a project whenever Tom adds an appointment for that project to his calendar. Here’s an example. Say that Tom is working on a project for a company called Acme Tools. When Tom adds an appointment to his calendar for the Acme Tools project he wants Outlook to send a message to Sally, the PM of that project, letting her know about the meeting. As you may already have guessed, Outlook does not have a built-in means of doing this. It is possible though. Time to write some code.

In order to accomplish this we need two things: a list of projects and associated PMs, and code that runs each time a new appointment is added to Tom’s calendar. That code will search the subject line of the appointment for any of the project names from the list. If it finds one, then it will send a message to the associated PM. For the list portion I decided to use a post in the Drafts folder. The post will contain project name/PM pairs in the form

PROJECT NAME=PM

Where PROJECT NAME is the name of the project and PM is the name or email address of the PM. The code creates the post automatically the first time it runs. All Tom will need to do is enter the project name/PM pairs. The entries are not case sensitive. In addition to its other duties, the code will monitor the post for changes. Anytime Tom edits the post the code will detect the change and load the revised list.

Right now Tom’s list has just one entry, Acme Tools. Sally Jones is the PM for the project so his list looks like this

Acme Tools=Sally Jones

When Tom creates an appointment for the Acme Tools project he will include the words “Acme Tools” in the subject line. Saving the appointment triggers the code. The code checks the appointment subject for “Acme Tools”, the only project in Tom’s list, and finds it is there. It now creates a new message, addresses it to Sally Jones, adds details about the appointment to the message, and sends it. If instead the code had not found a project match, then it would simply have exited without doing anything.

This solution does have a couple of restrictions. First, the code monitors just the default calendar. It won’t work if you have a separate calendar for each project. Second, it only works for new appointments. The code is NOT triggered by appointment changes. Third, project names must be unique and must not be a subset of another name. Consider the following list

Acme Tools=Sally Jones
Acme Tools Redesign=Bill Smith

Which PM will get the notification for an “Acme Tools Redesign” appointment? If you said Sally Jones you’re right. That’s because “Acme Tools” is a subset of “Acme Tools Redesign”. The code is always going to find the substring. That brings me to the fourth and final restriction. The code will act on only one match which will be the first one it finds. If Tom’s list looks like this

Acme Tools Redesign=Bill Smith
Acme Tools=Sally Jones

then an appointment for “Acme Tools Redesign” will go to the correct PM because the code will find that match before it finds the match for “Acme Tools”

Instructions and Code.

The code comes in two parts. This is part 1. Follow these instructions to add the code.

  1. Start Outlook
  2. Press ALT+F11 to open the VB editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select Insert > Class Module
  5. In the Properties panel click on Name and enter clsProjectNotifier
  6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included comments wherever something needs to or can change
  8. Click the diskette icon on the toolbar to save the changes
Option Explicit

'Declare some constants
    'On the next line edit the name of the post that will contain the project name/PM pairs as desired.
    Const POST_SUBJECT = "Project Notification List"
    'On the next line edit the character that will be the comment character used in the post contining the project name/PM pairs as desired.
    Const COMMENT_CHAR = "*"
    'On the next line edit the date formatting that will be used in the message as desired.
    Const DATE_FORMAT = "ddd dd/mm/yyyy"

'Declare some class variables
    Private WithEvents olkFld As Outlook.Items
    Private WithEvents olkPost As Outlook.PostItem
    Private objRegEx As Object
    Private objProjects As Object

Private Sub Class_Initialize()
    Set objProjects = LoadMatchList()
    Set objRegEx = CreateObject("VBscript.RegExp")
    Set olkFld = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub Class_Terminate()
    Set olkFld = Nothing
    Set olkPost = Nothing
    Set objRegEx = Nothing
    Set objProjects = Nothing
End Sub

Private Sub olkFld_ItemAdd(ByVal Item As Object)
    Dim strProject As String, olkMsg As Outlook.MailItem
    strProject = ContainsProjectName(Item.Subject)
    If strProject <> "" Then
        Set olkMsg = Application.CreateItem(olMailItem)
        With olkMsg
            .Recipients.Add objProjects.Item(strProject)
            .Recipients.ResolveAll
            .Subject = Session.CurrentUser.Name & ": Appointment Update"
            .HTMLBody = "Appointment update for " & strProject & " on " & Format(Item.Start, DATE_FORMAT)
            .Send
        End With
        Set olkMsg = Nothing
    End If
End Sub

Private Function LoadMatchList() As Object
    Dim olkTemp As Outlook.PostItem, arrLines As Variant, varWord As Variant, arrProject As Variant
    Set LoadMatchList = CreateObject("Scripting.Dictionary")
    Set olkTemp = Outlook.Application.Session.GetDefaultFolder(olFolderDrafts).Items.Find("[Subject] = '" & POST_SUBJECT & "'")
    If TypeName(olkTemp) = "Nothing" Then
        Set olkTemp = Outlook.Application.Session.GetDefaultFolder(olFolderDrafts).Items.Add(olPostItem)
        With olkTemp
            .Subject = POST_SUBJECT
            .BodyFormat = olFormatPlain
            .Body = String(10, COMMENT_CHAR) & vbCrLf _
                  & COMMENT_CHAR & " Enter your list of projects and PMs in the format PROJECT NAME=PM NAME.  One project per line.  Case is immaterial." & vbCrLf _
                  & COMMENT_CHAR & " The PM NAME portion can be a name or an email address.  If you use a name, then the name must match a name in your address book." & vbCrLf _
                  & COMMENT_CHAR & " Any line beginning with a " & COMMENT_CHAR & " is considered a comment." & vbCrLf _
                  & String(10, COMMENT_CHAR) & vbCrLf & vbCrLf
            .Save
        End With
    Else
        arrLines = Split(olkTemp.Body, vbCrLf)
        For Each varWord In arrLines
            If Left(varWord, 1) <> COMMENT_CHAR Then
                arrProject = Split(varWord, "=")
                If UBound(arrProject) = 1 Then
                    LoadMatchList.Add arrProject(0), arrProject(1)
                End If
            End If
        Next
    End If
    Set olkPost = olkTemp
    Set olkTemp = Nothing
End Function

Private Function ContainsProjectName(strValue As String) As String
    Dim colMatches As Object, strProjectName As Variant, arrKey As Variant
    arrKey = objProjects.Keys
    For Each strProjectName In arrKey
        With objRegEx
            .IgnoreCase = True
            .Pattern = CStr(strProjectName)
            .Global = True
        End With
        Set colMatches = objRegEx.Execute(strValue)
        If colMatches.Count > 0 Then
            ContainsProjectName = strProjectName
            Exit For
        End If
    Next
    Set colMatches = Nothing
End Function

Private Sub olkPost_Write(Cancel As Boolean)
    Set objProjects = LoadMatchList()
End Sub

This is part 2. Follow these instructions to add the code.

Outlook 2007.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click Tools > Trust Center
  6. Click Macro Security
  7. Set Macro Security to “Warnings for all macros”
  8. Click OK
  9. Close Outlook
  10. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.

Outlook 2010.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click File and select Options
  6. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  7. Click Macro Settings
  8. Select either of the two bottom settings (i.e. “Notifications for all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”. The choice of which to chose is up to you. If you select “Notifications”, then you’ll be prompted at times to enable macros. If you pick “Enable all” then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  9. Click Ok until the dialog-boxes have all closed
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
Dim objProjectNotification As clsProjectNotifier

Private Sub Application_Quit()
    Set objProjectNotification = Nothing
End Sub

Private Sub Application_Startup()
    Set objProjectNotification = New clsProjectNotifier
End Sub
Advertisements

2 comments on “Automatic Project Manager Notifications

  1. Thanks a lot David.

    After some testing and checking out the Notifier list and correct e-mail adresses, I can say it’s working great.

    AND, Also in combination with my other code, which is monitoring an additional mailbox and creates a notification.

    Best Regards,

    Tom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s