Better WebEx / Lync / Skype meeting locations for mobile users

[Edit 2016-12-01 … changed the VBA code to format the phone number with dashes]
[Edit 2016-11-07 … changed the VBA code to handle WebEx invites, too][Edit 2015-10-27 … changed the VBA code to be able to extract the phone number]

By default, the “Location” for a Skype for Business (ne Lync) Meeting in an Outlook calender invite reads as “Skype Meeting”. WebEx is no better. This is not very friendly to mobile users who want to dial in via phone. If the body of the meeting contains a lot of agenda text, the WebEx / Skype dialin information may not display at all on mobile; and without something in the “Location”, a user can’t just tap the invite to dial in directly.

I wanted to have a Macro that lets me set the “Location” to “phone-number,,,conference-id#”. This way, the user can dial, and will be logged in automatically.
An alternative (you’d need to slightly modify the code by replacing “,,,” in the second line with ” ; “) would be “phone-number ; conference-id#” which makes the conference id available in the dial screen to be sent with one touch.

Further, I wanted to be able to control permissions through the Skype Meeting Options, which means I can’t use a “dedicated meeting space”, but have to use the “new meeting space” option, which means the conference ID changes with every meeting.

I want to hear when people enter or leave, I don’t want anyone to have to wait around in a lobby, and I conference with customers, so I don’t want restrictions as to who can join or present. The screenshot below shows the permissions I chose before hitting “Remember Settings”.

The macro I created can extract US phone numbers. If you have a need for international number detection, you’d need to key on the country code and then have individual patterns from there in a case structure. There’s no good way I know of to handle international number formats in a single regex.

In order to use the macro, you’ll need to:

– Enable access to the coding tools in Outlook, the “Developer” toolbar

– Create the macro

– Sign the macro and save

– Link the macro to the “New Appointment” screen

The macro was created for use with Outlook 2016, 2013 and 2010. The current version has only been tested with Outlook 2016.

Enable access to the coding tools in Outlook

From the Outlook main windows, click on “File” then “Options”

In the “Outlook Options” window, click on “Customize Ribbon” on the left. Check the “Developer” ribbon to show up.

Click OK

Create the macro

From the Outlook main window, click the “Developer” toolbar, then the “Visual Basic” icon

Right-click the Project name in the left pane, choose “Properties…” and set the “Project Name” to “Conf”, then click OK.

Under “Modules”, you should see a “Module 1” with an empty window to the right. Click it and paste the code below.

<Edit> Now with code formatting that will paste correctly.

Sub AddLocation()
Application.ActiveInspector.CurrentItem.Location = GetConfNumUsingRegEx() & ",,," & GetConfCodeUsingRegEx() & "#"
End Sub

Function GetConfNumUsingRegEx() As String
 ' Set reference to VB Script library
 ' Microsoft VBScript Regular Expressions 5.5
 
 Dim olAppt As Outlook.AppointmentItem
 Dim Reg1 As RegExp
 Dim M1 As MatchCollection
 Dim M As Match
 Dim fNumber As String ' Formatted phone number
 
 Set olAppt = Application.ActiveInspector.CurrentItem
 ' Debug.Print olAppt.Body

 Set Reg1 = New RegExp
 
 For i = 1 To 2 ' Run through twice in case this hasn't been saved and WebEx info isn't there yet
 
 With Reg1 ' look for the bizarre CI number we use, and prefer it if it's there
 .Pattern = "tel:\+1855-SkypeCI"
 .Global = True
 End With
 
 If Reg1.test(olAppt.Body) Then
 GetConfNumUsingRegEx = "+1-855-759-7324"
 Exit Function
 End If
 
 ' You can copy / paste code from "With Reg1" to "End If" any number of times if there are
 ' additional preferred numbers / oddly formatted numbers you want to be on the lookout for
 
 With Reg1 ' look for tel: style links, US numbers being matched
 .Pattern = "tel\s*[:]+\s*((?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *(?:ext\.|ext|x)?\s*(\d+))?)\w*"
 .Global = True
 End With
 
 If Reg1.test(olAppt.Body) Then
 
 Set M1 = Reg1.Execute(olAppt.Body)
 For Each M In M1 ' Find preferred toll-free number
 If (Found = False) And ((M.SubMatches(2) = "800") Or (M.SubMatches(2) = "844") Or (M.SubMatches(2) = "855") Or (M.SubMatches(2) = "866") Or (M.SubMatches(2) = "877") Or (M.SubMatches(2) = "888")) Then
 Found = True
 GetConfNumUsingRegEx = FormatPhoneNumber(M.SubMatches(0))
 Exit Function
 End If
 Next
 ' No preferred number, use the first one
 Set M = M1(0)
 GetConfNumUsingRegEx = FormatPhoneNumber(M.SubMatches(0))
 Exit Function
 End If
 
 With Reg1 ' sometimes tel: is missing, again US numbers being matched
 .Pattern = "((?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *(?:ext\.|ext|x)?\s*(\d+))?)\w*"
 .Global = True
 End With
 
 If Reg1.test(olAppt.Body) Then
 
 Set M1 = Reg1.Execute(olAppt.Body)
 For Each M In M1 ' Find preferred number
 If (Found = False) And ((M.SubMatches(2) = "800") Or (M.SubMatches(2) = "844") Or (M.SubMatches(2) = "855") Or (M.SubMatches(2) = "866") Or (M.SubMatches(2) = "877") Or (M.SubMatches(2) = "888")) Then
 Found = True
 GetConfNumUsingRegEx = FormatPhoneNumber(M.SubMatches(0))
 Exit Function
 End If
 Next
 ' No preferred number, use the first one
 Set M = M1(0)
 GetConfNumUsingRegEx = FormatPhoneNumber(M.SubMatches(0))
 Exit Function
 End If
 olAppt.Save ' This will fill in WebEx info with WebEx Productivity Tools running and try again
 Next i
End Function


Function GetConfCodeUsingRegEx() As String
 ' Set reference to VB Script library
 ' Microsoft VBScript Regular Expressions 5.5
 
 Dim olAppt As Outlook.AppointmentItem
 Dim Reg1 As RegExp
 Dim M1 As MatchCollection
 Dim M As Match
 
 Set olAppt = Application.ActiveInspector.CurrentItem
 ' Debug.Print olAppt.Body
 
 Set Reg1 = New RegExp
 
 With Reg1 ' look for Skype/Lync pattern
 .Pattern = "Conference ID\s*[:]+\s*(\d*)\s*"
 .Global = True
 End With
 If Reg1.test(olAppt.Body) Then
 
 Set M1 = Reg1.Execute(olAppt.Body)
 Set M = M1(0)
 GetConfCodeUsingRegEx = M.SubMatches(0)
 Exit Function
 End If
 
 With Reg1 ' look for WebEx public or private room pattern
 .Pattern = "[Aa]ccess code\)?\s*[:]+\s*(\d*\s?\d*\s?\d*)"
 .Global = True
 End With
 
 If Reg1.test(olAppt.Body) Then
 
 Set M1 = Reg1.Execute(olAppt.Body)
 Set M = M1(0)
 GetConfCodeUsingRegEx = Replace(M.SubMatches(0), " ", "") ' strip all spaces
 Exit Function
 End If
 
End Function

Function FormatPhoneNumber(sRawNumber As String)
 'Purpose: Formats a US telephone number as 999-999-9999,,,165.
 'Works with x, ext, ext. or just space before extension
 'Works with or without 1-number or +1-number
 'Works with or without spaces, dashes or parentheses between and around number groups
 'Does NOT deal with international numbers

 Dim sPhoneNumber As String 'Phone number formatted as 999-999-9999
 Dim Reg1 As RegExp
 Dim M1 As MatchCollection
 Dim M As Match
 
 Set Reg1 = New RegExp
 
 With Reg1 ' US numbers being matched
 .Pattern = "((?:\+?(\d{1,3}))?[-. (]*(\d{3})[-. )]*(\d{3})[-. ]*(\d{4})(?: *(?:ext\.|ext|x)?\s*(\d+))?)\w*"
 .Global = True
 End With
 
 If Reg1.test(sRawNumber) Then
 Set M1 = Reg1.Execute(sRawNumber)
 Set M = M1(0)
 If Not Len(M.SubMatches(1)) = 0 Then ' Starting with a code like "1"
 sPhoneNumber = M.SubMatches(1) & "-" & M.SubMatches(2) & "-" & M.SubMatches(3) & "-" & M.SubMatches(4)
 Else
 sPhoneNumber = M.SubMatches(2) & "-" & M.SubMatches(3) & "-" & M.SubMatches(4)
 End If
 If Not Len(M.SubMatches(5)) = 0 Then ' Found an extension
 sPhoneNumber = sPhoneNumber & ",,," & M.SubMatches(5)
 End If
 End If
 
 'Return formatted phone number
 FormatPhoneNumber = sPhoneNumber

End Function

This code should work as-is. If desired, you can hard-code a number to be used for your own invites inside the GetConfNumUsingRegex() function.

In the Visual Basic editor, click the “Tools” menu, then “References…” and check the “Microsoft VBScript Regular Expressions 5.5”, then click “OK”. This is required for the macro to function.

Here is a screen shot of the VBA editor for reference:

Sign the macro and save

Outlook 2010, by default, has a macro security setting of “Notifications for digitally signed macros, all other macros disabled”. Without signing the macro, it may work the first time around, but you might get an error message that “the macros in this project are disabled” in future.

We’ll create a self-signed cert and apply it. For Outlook 2010 and 2013, look for the “Digital Certificate for VBA Projects” application in the Start menu, give it your name, and click “OK” to create the certificate, like so:

N.B.: Your installation of Office might not have linked this utility in the Start Menu. My O365 copy of Office 2016 does not, for example. In that case, open File Explorer, navigate to where Office is installed (C:\Program Files (x86)\Microsoft Office\Office16 is the default path for Office 2016), and launch the “SELFCERT.EXE” program.

Next, apply this certificate to your project. In the VBA editor, choose the “Tools” menu then “Digital Signature…” and apply the certificate you just created by selecting it via “Choose…” then clicking “OK”:

 

Lastly, save your Project via “File” and “Save VbaProject.OTM”.

When that is done, you can close the VBA editor screen either via the X in the corner or through the “File” menu.

At some point when first executing the macro, you may see a warning whether to trust the certificate you just created. Choose “Enable Macros” or “Trust all documents from this publisher” :

 

Link the macro to the “New Appointment” screen

This was actually the least intuitive step of the whole lot.

In Outlook, click on “Calendar”, then “New Meeting”. As far as I can tell, you can only add the macro to the ribbon while in that window.

With that new meeting window open, click “File”, then “Options”.

Click on “Customize Ribbon”

Right-click “Appointment” and choose “Add New Group”

Right-Click the “New Group (Custom)” and rename it to “Conf”

Select “Conf (Custom)” and use the up-arrow to the right to move it just under “Skype Meeting” or “WebEx”, depending on which you use.

With “Conf (Custom)” still selected, change “Choose commands from:” on the left-hand list to “Macros”

Click on the “Conf.AddLocation” macro and use the “Add>>” button

The macro should now show up in your “Conf (Custom)” group.

Click OK and test the macro!

Screenshot of what this ribbon option window looks like below.

All Done

This is a fair serious amount of effort just to get a phone number into the “Location” field. For me, it was worth it because that effort makes life easier for my customers joining my meetings.

If so desired, you can disable the “Developer” toolbar again.