Automatically Linking to a Meeting Location on Google Maps


I saw this tweet from RussellAllert and was intrigued.

Why doesn’t #Outlook pull in Google or Bing maps when a location is entered. That would be very helpful.

I liked the idea so I tweeted back and asked if the map needed to appear in the body of the appointment or if a link to it would do. Russell replied that having the map in the body would “rock” while a link would “win the silver medal”. I really wanted to be a rock star, but it looks like I’ll have to settle for a silver. Try as I might I could not find a way to have the map appear in the appointment body. The problem, at least with Google Maps, is that including the map in the body requires using an IFRAME which Microsoft does not support in Outlook 2007/2010. Fortunately, linking to the map is very simple.

The best feature of this process is that it’s automatic. When an appointment is added, whether Russell adds it or whether it’s an invite from someone else, the script will create a link to whatever is specified in the appointment’s Location field. That does introduce a problem though. If Location doesn’t contain a valid address, then the link will effectively be useless. It will still take Russell to Google Maps, but it won’t show anything useful.

I hope Russell finds this useful.

Instructions.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code snippet below 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. Open the 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.
Dim WithEvents olkCalendar As Outlook.Items

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

Private Sub Application_Quit()
    Set olkCalendar = Nothing
End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    Const LINK_TEXT = "View the meeting location on Google Maps"
    If Not InStr(1, Item.Body, LINK_TEXT) Then
        InsertLink Item, "http://maps.google.com/maps?q=" & Item.Location, LINK_TEXT
        Item.Save
    End If
End Sub

Sub InsertLink(olkApt As Outlook.AppointmentItem, strLink As String, strLinkText As Variant)
    'This is a modified version of Sue Mosher's example from
    'http://www.outlookcode.com/codedetail.aspx?id=1726
    Dim objInsp As Outlook.Inspector, _
        objDoc As Word.document, _
        objSel As Word.Selection
    On Error Resume Next
    Set objInsp = olkApt.GetInspector
    Set objDoc = objInsp.WordEditor
    olkApt.Body = strLinkText & vbCrLf & olkApt.Body
    objDoc.Windows(1).document.Range(0, Len(strLinkText)).Select
    Set objSel = objDoc.Windows(1).Selection
    objDoc.Hyperlinks.Add Anchor:=objSel.Range, Address:=strLink, TextToDisplay:=strLinkText
    If Err.Number <> 0 Then
        MsgBox "Problem adding hyperlink to appointment." & vbCrLf & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Insert Link"
    End If
    On Error GoTo 0
    Set objInsp = Nothing
    Set objDoc = Nothing
    Set objSel = Nothing
End Sub

Notes.

  1. This code is designed for Outlook 2007/2010.
  2. Keep in mind that the Location field must contain a valid, mappable address for this to be of any value.
  3. The code will add the link automatically when an appointment is first added to the calendar. It will not update the link if the appointment is updated.
  4. A perhaps better solution would be to create a custom appointment form with a new page for the map. The page could employ a browser control which could employ an IFRAME and embed the map. A custom form would also allow updates to change the location and still have it mapped.

Revisions.

  • Revision 1

This revision is for TJ who in a comment asked

… is there a way to change/update this so that if I create a meeting, this link is embedded in the invite that gets sent out to everyone?

A further idea – is there a way to build in a table to check/match conference rooms to street addresses? For instance: Conference Room A=123 anywhere st, anyplace, CA. Conference Room B=456 the other st, elswhere, CA. Then , if the location line did not list a room in the table, it would assume the location was an actual address and try to map it?

This revision implements both ideas. There is one hitch though. I could not find a way to add the link to the invitations automatically. For this to work TJ will have to save the meeting request manually before sending the invitations out. The link will appear in the meeting notes as soon as the meeting is saved. A quick way to save is to press CTRL+S.

Instructions and Code.

The code comes in two parts.

Part 1

Follow these instructions to add part 1 of 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 clsLocationLink
  6. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
'Edit the next line as desired.  This is the text of the hyperlink that will appear in the appointment/meeting.
Const LINK_TEXT = "View the meeting location on Google Maps"
Const LINK_TARGET = "http://maps.google.com/maps?q="

Private WithEvents olkCal As Outlook.Items
Private colLocations As Collection

Private Sub Class_Initialize()
    Set colLocations = New Collection
    Set olkCal = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub Class_Terminate()
    Set olkCal = Nothing
    Set colLocations = Nothing
End Sub

Private Sub olkCal_ItemAdd(ByVal Item As Object)
    If Not InStr(1, Item.Body, LINK_TEXT) Then
        InsertLink Item, LINK_TARGET & CheckLocation(Item.Location), LINK_TEXT
        Item.Save
    End If
End Sub

Private Sub InsertLink(olkApt As Outlook.AppointmentItem, strLink As String, strLinkText As Variant)
    'This is a modified version of Sue Mosher's example from
    'http://www.outlookcode.com/codedetail.aspx?id=1726
    Dim objInsp As Outlook.Inspector, _
        objDoc As Word.document, _
        objSel As Word.Selection
    On Error Resume Next
    Set objInsp = olkApt.GetInspector
    Set objDoc = objInsp.WordEditor
    olkApt.Body = strLinkText & vbCrLf & olkApt.Body
    objDoc.Windows(1).document.Range(0, Len(strLinkText)).Select
    Set objSel = objDoc.Windows(1).Selection
    objDoc.Hyperlinks.Add Anchor:=objSel.Range, Address:=strLink, TextToDisplay:=strLinkText
    If Err.Number <> 0 Then
        MsgBox "Problem adding hyperlink to appointment." & vbCrLf & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Insert Link"
    End If
    On Error GoTo 0
    Set objInsp = Nothing
    Set objDoc = Nothing
    Set objSel = Nothing
End Sub

Private Function CheckLocation(strLocation As String) As String
    Dim strTemp As String
    On Error Resume Next
    strTemp = colLocations(strLocation)
    CheckLocation = IIf(strTemp = "", strLocation, strTemp)
    On Error GoTo 0
End Function

Public Sub Location(strLocation As String, strAddress As String)
    colLocations.Add strAddress, strLocation
End Sub

Part 2.

Follow these instructions to add part 2 of 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. Edit the code as needed. I included comments wherever something needs to or can change
  4. Click the diskette icon on the toolbar to save the changes
  5. Close the VB Editor
  6. Click Tools > Trust Center
  7. Click Macro Security
  8. Set Macro Security to “Warnings for all macros”
  9. Click OK
  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.

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 objLocLink As clsLocationLink

Private Sub Application_Quit()
    Set objLocLink = Nothing
End Sub

Private Sub Application_Startup()
    Set objLocLink = New clsLocationLink
    With objLocLink
        'Add a line like the following for each location you want to reference indirectly.  The format is "Location name", "Actual location address".  Remove the next two lines or edit them as needed.
        .Location "Conference Room A", "123 Anywhere St., Anyplace, CA"
        .Location "Conference Room B", "456 Other St., Elsewhere, CA"
    End With
End Sub
Advertisements

16 comments on “Automatically Linking to a Meeting Location on Google Maps

  1. Digging up the past…You have GREAT solutions. For this one, it works very well for me…but is there a way to change/update this so that if I create a meeting, this link is embedded in the invite that gets sent out to everyone?

    A further idea – is there a way to build in a table to check/match conference rooms to street addresses? For instance: Conference Room A=123 anywhere st, anyplace, CA. Conference Room B=456 the other st, elswhere, CA. Then , if the location line did not list a room in the table, it would assume the location was an actual address and try to map it?

    • Thanks, TJ. I’m glad you find them useful.

      I can definitely embed the link in invites as they are sent out.

      If by “build in a table” you mean to compare the value in Location with a known set of locations and take action based on whether a match is found, then yes, I can do that too. I’m assuming that if there’s a match, then use the address specified for that value. Otherwise, use the actual address. Is that correct?

  2. I’m getting a “Compile error: User-defined type not defined”.
    It stops on the following line in the vb editior (end of line 22 and all of 23:)
    , _
    23 objDoc As Word.document, _

    • Hi, Jason.

      On lines 23 and 24 replace “Word.document” and “Word.Selection” with the word “Object”.

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