Travel Time for Meetings


I was checking tweets this afternoon and came across an interesting one from Brian Kahrs. Here’s what Brian said: Why doesn’t @Microsoft Outlook ask to block “transportation time” when setting up a meeting? I may have a 1:15, but can’t meet from 1-1:15. Brian makes a really good point. Meetings don’t reflect the travel time. Of course Brian could simply pad the meeting time to include his travel time, but that would be confusing to the other meeting participants. They’d have to know that the meeting times were padded to include travel time and they’d have to know what Brian’s travel time is in order for them to mentally adjust the meeting start/end times accordingly. Not good. Another solution Brian could employ is to manually create appointments on either side of the actual meeting to block off the travel time to and from the meeting location. That would eliminate any confusion for the other meeting attendees, but Brian has to remember to create those two additional appointment for every meeting he creates.

I can’t answer Brian’s question on why Microsoft hasn’t included this feature. What I can do is offer Brian a workaround. I’m not going to call it a solution because it’s imperfect. The workaround is to use a script to detect when Brian adds a meeting to his calendar and give him the opportunity to block-off travel time on either side of it. Here’s how it works.

  1. Brian creates or accepts a meeting.
  2. The script detects the fact that a meeting has been added to his calendar.
  3. The script displays a dialog-box asking Brian if he wants to schedule travel time for this appointment.
  4. If Brian answers “yes”, then the script prompts him for the number of minutes of travel.
  5. Brian enters the number of minutes.
  6. The script creates two appointments, one on either side of the meeting. Each of these appointments blocks off the number of minutes Brian entered in step #5.

Using the example Brian gave in his tweet, if he created a meeting that ran from 1:15 to 2:15, then the script would create an appointment from 1:00 – 1:15 for Brian to travel to the meeting and another from 2:15 – 2:30 for Brian to return from the meeting.

As I noted earlier this isn’t a complete solution because it’s not perfect. In this context “not perfect” means that it doesn’t handle everything it needs to. If the meeting time changes, then Brian will have to manually deal with the two travel appointments. Ditto if the meeting is canceled. The workaround doesn’t handle different travel times either. Brian may be able to get to the meeting in 15 minutes, but he may know that at 2:15 it’ll take him 30 minutes to get back. Or perhaps this is the last meeting of the day and Brian won’t be returning and therefore doesn’t need to block travel time after the meeting. A true solution would handle all of these issues.

Here’s the code that Brian will need to use the workaround.

Dim WithEvents olkCalendar As Outlook.Items

Private Sub Application_Quit()
    Set olkCalendar = Nothing
End Sub

Private Sub Application_Startup()
    Set olkCalendar = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    Const SCRIPT_NAME = "Schedule Travel Time"
    Dim olkTravel1 As Outlook.AppointmentItem, _
        olkTravel2 As Outlook.AppointmentItem, _
        intMinutes As Integer
    If Item.MeetingStatus = olMeeting Then
        If msgbox("Do you need to schedule travel time for this meeting?", vbQuestion + vbYesNo, SCRIPT_NAME) = vbYes Then
            intMinutes = InputBox("How many minutes each way?", SCRIPT_NAME, 15)
            If intMinutes > 0 Then
                Set olkTravel1 = Application.CreateItem(olAppointmentItem)
                With olkTravel1
                    .Subject = "Travel to Meeting: " & Item.Subject
                    .Start = DateAdd("n", intMinutes * -1, Item.Start)
                    .End = Item.Start
                    .Categories = Item.Categories
                    .BusyStatus = olBusy
                    .Save
                End With
                Set olkTravel2 = Application.CreateItem(olAppointmentItem)
                With olkTravel2
                    .Subject = "Travel from Meeting: " & Item.Subject
                    .Start = Item.End
                    .End = DateAdd("n", intMinutes, Item.End)
                    .Categories = Item.Categories
                    .BusyStatus = olBusy
                    .Save
                End With
            End If
        End If
    End If
    Set olkTravel1 = Nothing
    Set olkTravel2 = Nothing
End Sub

Here’s how to add the code to Outlook.

Outlook 2003 and Earlier
1. Start Outlook
2. Click Tools > Macro > Visual Basic Editor
3. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4. Copy the code from the Code Snippet box and paste it into the right-hand pane of
5. Outlook’s VB Editor window
6. Edit the code as needed. I included comment lines wherever something needs to or can change
7. Click the diskette icon on the toolbar to save the changes
8. Close the VB Editor
9. Click Tools > Macro > Security
10. Set the Security Level to Medium
11. Close Outlook
12. Start Outlook
13. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.

Outlook 2007
1. Start Outlook
2. Click Tools > Macro > Visual Basic Editor
3. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
5. Edit the code as needed. I included comment lines wherever something needs to or can change
6. Click the diskette icon on the toolbar to save the changes
7. Close the VB Editor
8. Click Tools > Trust Center
9. Click Macro Security
10. Set Macro Security to “Warnings for all macros”
11. Click OK
12. Close Outlook
13. 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.

I hope Brian finds this useful.

Revisions.

I create this revision after a couple of readers asked for a version that would block travel time for appointments as well as meetings. The original version only handles meetings. Since the code keys on items being added to the calendar, it has to have a way to discriminate between real meetings/appointments and the appointments it adds for travel time. The original code solved that by checking to see if the new item is a meeting. If it is, then add travel time. If not, then ignore it. Without that kind of check the code would get locked in an infinite loop as the travel time appointments would be seen as new items which would cause the code to prompt for travel time and add new appointments, which would be seen as new items, and so on, and so on, ad infinitum. To prevent that, this version uses a category called “Travel”. If the appointment belongs to the “Travel” category, then the code won’t prompt for travel time since it sees the appointment as a travel time entry. If the new item does not belong to the “Travel” category, then the code will prompt for travel time.

Use the instructions from the original post to add this code to Outlook. If you have the original code in place, then either delete it or overwrite it with this version. You don’t want to have both versions in place.

Dim WithEvents olkCalendar As Outlook.Items

Private Sub Application_Quit()
    Set olkCalendar = Nothing
End Sub

Private Sub Application_Startup()
    Set olkCalendar = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    Const SCRIPT_NAME = "Schedule Travel Time"
    'On the next line change the category name as desired.  The code will ignore any meeting or appointment assigned to this category.
    Const CATEGORY_NAME = "Travel"
    Dim olkTravel1 As Outlook.AppointmentItem, _
        olkTravel2 As Outlook.AppointmentItem, _
        intMinutes As Integer, _
        strNoun As String
    If InStr(1, Item.Categories, CATEGORY_NAME) = 0 Then
        strNoun = IIf(Item.MeetingStatus = olMeeting, "meeting", "appointment")
        If MsgBox("Do you need to schedule travel time for this " & strNoun & "?", vbQuestion + vbYesNo, SCRIPT_NAME) = vbYes Then
            intMinutes = InputBox("How many minutes each way?", SCRIPT_NAME, 15)
            If intMinutes > 0 Then
                Set olkTravel1 = Application.CreateItem(olAppointmentItem)
                With olkTravel1
                    .Subject = "Travel to " & StrConv(strNoun, vbProperCase) & ": " & Item.Subject
                    .Start = DateAdd("n", intMinutes * -1, Item.Start)
                    .End = Item.Start
                    .Categories = IIf(Len(Item.Categories) > 0, Item.Categories & ", Travel", "Travel")
                    .BusyStatus = olBusy
                    .Save
                End With
                Set olkTravel2 = Application.CreateItem(olAppointmentItem)
                With olkTravel2
                    .Subject = "Travel from " & StrConv(strNoun, vbProperCase) & ": " & Item.Subject
                    .Start = Item.End
                    .End = DateAdd("n", intMinutes, Item.End)
                    .Categories = IIf(Len(Item.Categories) > 0, Item.Categories & ", Travel", "Travel")
                    .BusyStatus = olBusy
                    .Save
                End With
            End If
        End If
    End If
    Set olkTravel1 = Nothing
    Set olkTravel2 = Nothing
End Sub