Automatically Reserving Travel Time for Outlook 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 appointments 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
                                          'Edit the subject as desired'
                                          .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
                                          'Edit the subject as desired'
                                          .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

Select all Open in new window

One quick note on the code: it only works against meetings, not appointments.  In the Outlook context a meeting involves others whereas an appointment only involves you.  If you want the code to work for appointments, then remove lines 16 and 42.

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!


Note: This is a repost of a posting I created on my external blog.