<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Automatically Reserving Travel Time for Outlook Meetings

Published on
29,896 Points
19,896 Views
5 Endorsements
Last Modified:
Awarded
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

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.
5
Author:David Lee
2 Comments
LVL 61

Expert Comment

by:Kevin Cross
Very nice, BlueDevilFan!

Love the article and think I will make good use of this new tool. In fact right after loading this, I had to enter a dental appointment which is an appointment since no one else is invited and is important for me to know the time of actual appointment but requires I block my calendar to allow travel time from office to dentist. To take advantage of your tip as well as your note on usinging this for both appointments and meetings, here is my version.

Added Sub Routine:
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

Open in new window


Note I moved constant SCRIPT_NAME to a global variable:
Const OLK_TRAVEL_SCRIPT_NAME = "Schedule Travel Time"

Open in new window


Updated olkCalendar_ItemAdd:
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

Open in new window


For me, I only wanted this prompt on "Out Of Office" meetings or appointments which also prevented the prompting of travel time for the travel time as you had already set those to have a BUSY status in your code. I also like to give myself a few extra minutes (to be early), so added 5 minutes buffer between travel time and actual meetings in my version. Additioanlly, using two calls for creating appointments, I take advantage of your code which will NOT create an entry for 0 minute travel time, so I get to easily choose when I only want travel time to or from a meeting.

Again thanks and you have a YES from me above!

Best regards,

mwvisa1
1

Expert Comment

by:Taylor Bates
I am definitely a work in progress when it comes to VBA Coding, I know enough to read most of the code. However, trying to create my own is difficult as I do not know a lot of the call functions or syntax. I have looked at your code and modified it so you can specify travel times before and after your appointment/meeting, and it works great when you are the meeting organizer. My only issue is trying to prevent the initial travel time prompt when a meeting invite is sent to you. The meeting is automatically placed in my calendar as a placeholder and I get prompted for a travel time. Then if I accept the invite it prompts me a second time. I only want to be prompted for travel time after sending an "Accept" response to the meeting organizer, not upon receiving the request. Is there a way to block this initial prompt when receiving meeting request?

Any guidance or help will be appreciated. I will post my modification of your code below.

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 to the meeting?", SCRIPT_NAME, 30)
            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
            End If
            intMinutes2 = InputBox("How many minutes from the meeting?", SCRIPT_NAME, 30)
            If intMinutes2 > 0 Then
                Set olkTravel2 = Application.CreateItem(olAppointmentItem)
                With olkTravel2
                    .subject = "Travel from " & StrConv(strNoun, vbProperCase) & ": " & Item.subject
                    .Start = Item.End
                    .End = DateAdd("n", intMinutes2, 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

Open in new window

0

Featured Post

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
There are many cases found where ScanPST.exe fails to repair corrupt Outlook PST File. When user tries to repair PST using Inbox Repair tool and it throws below error: •      Inbox Repair tool does not recognize the file •      ScanPST.exe hangs in betwee…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month