Automatically Reserving Travel Time for Outlook Meetings: outlook, vba, macro, twitter

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

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.

Private Sub CreateTravelAppointmentEntry(ByVal Item As Object, Optional ByVal isTo As Boolean = True) Dim olkTravel As Outlook.AppointmentItem Dim intMinutes As Integer intMinutes = InputBox("How many minutes " & IIf(isTo, "to", "from") & "?", OLK_TRAVEL_SCRIPT_NAME, 60) If intMinutes > 0 Then Set olkTravel = Application.CreateItem(olAppointmentItem) With olkTravel 'Edit the subject as desired' .Subject = "Travel " & IIf(isTo, "to", "from") & " Meeting: " & Item.Subject If isTo Then .Start = DateAdd("n", intMinutes * -1 - 5, Item.Start) Else .Start = DateAdd("n", 5, Item.End) End If .End = DateAdd("n", intMinutes, .Start) .Categories = Item.Categories .BusyStatus = olBusy .Save End With End If Set olkTravel = Nothing End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object) If Item.BusyStatus = OlBusyStatus.olOutOfOffice Then If MsgBox("Do you need to schedule travel time for this meeting?", vbQuestion + vbYesNo, OLK_TRAVEL_SCRIPT_NAME) = vbYes Then CreateTravelAppointmentEntry Item 'setup TO travel time CreateTravelAppointmentEntry Item, False 'setup FROM travel time End If End If End Sub

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

30-day free trial. Register in 60 seconds.

Automatically Reserving Travel Time for Outlook Meetings

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

Here’s how to add the code to Outlook.

outlook

vba

macro

twitter

Outlook Groupware Software

About The Author

More Articles From BlueDevilFan

Comments

Add your Comment

Join Experts Exchange Today