An Office 365 user had a problem: their default Skype for Business invitations added phone numbers used by the corporation worldwide but he only created meetings with people in certain locations and wanted to include only those phone numbers in his invitations. He found "Use Word Macro to Apply Formatting to Email" and wondered if the same method could be used to change the numbers.
When I set up a Skype for Business Meeting, it uses a default template that has dial-in numbers for multiple countries as configured by our Admin. Since we are a large company, that template is standard for everyone. For my specific meetings, I have to then replace the entire default with only the dial-ins for countries I am going to be conferencing. Is there a way to replace the entire Skype defaulted message with my customized version with the click of a button?
There are two ways you can do this: create HTML stationery to replace the template and use Regex to get the meeting code so you can recreate the link to the meeting or use a macro to delete or replace the numbers in the meeting invitation, using the method in "Use Word Macro to Apply Formatting to Email".
This code sample uses the Word object model to select the numbers you are replacing and replaces then with the new number and new hyperlink. It then finds the remaining numbers and deletes the entire row.
To use this macro you need to set a reference to the Word Object Model in the VBA editor's Tools, References dialog. You'll also need to change the phone number strings to the correct values.
After creating the Skype meeting, run the macro to update the numbers.
Sub EditSkype() Dim objApp As Outlook.Application Dim objItem As Object Dim objInsp As Outlook.Inspector Dim objWord As Word.Application Dim objDoc As Word.Document Dim objSel As Word.Selection Dim oRng As Word.Range Dim i As Long Dim iReplace As Long, iDelete As Long Dim strLink As String, strLinkText As String Dim strDeletePhone As String, strAddPhone As String Set objItem = Application.ActiveInspector.currentItem If Not objItem Is Nothing Then If objItem.Class = olMail Then Set objInsp = objItem.GetInspector If objInsp.EditorType = olEditorWord Then Set objDoc = objInsp.WordEditor Set objWord = objDoc.Application Set objSel = objWord.Selection Set oRng = objDoc.Range ' user wanted to clean up phone # left in objItem.HTMLBody = Replace(objItem.HTMLBody, "English (United States)", "") ' Replace 2 numbers For iReplace = 1 To 2 Select Case iReplace Case 1 strDeletePhone = "+1 234 567 8901 Canada (Toronto ON)" strAddPhone = "+1234 567 8901 Portland, OR" Case 2 strDeletePhone = "+256 1234 1234 Hong Kong" strAddPhone = "+35321 1234567 Ireland" End Select With oRng.Find .Text = strDeletePhone .Execute If .Found Then oRng.Paragraphs(1).Range.Select strLink = "tel:" & strAddPhone strLinkText = strAddPhone objDoc.Hyperlinks.Add objSel.Range, strLink, "", "", strLinkText, "" End If End With ' fix the hyperlink color With oRng.Find .Text = strAddPhone .Wrap = wdFindContinue .Execute If .Found = True Then oRng.Font.Color = RGB(0, 102, 204) End With Next iReplace ' Delete 2 numbers For iDelete = 1 To 2 Select Case iDelete Case 1 strDeletePhone = "+63 12 234 5678" Case 2 strDeletePhone = "+55 123 456 7890" End Select Set oRng = objDoc.Range With oRng.Find .Text = strDeletePhone While .Execute oRng.Paragraphs(1).Range.Delete Wend End With Next iDelete End If End If End If End Sub
How to Use Macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 and above, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, itâs at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
- Set a reference to the Word Object Model in the VBA editor's Tools, References dialog.
- Customize the meeting ribbon or Quick Access Toolbar to add a button to the macro for easy access.
More information as well as screenshots are at How to use the VBA Editor
This solution is EXACTLY what I am looking for my needs as the company I work for is larger and the tollfree numbers listed in the stock skype template don't meet my needs. I have tried running the macro however it appears to jump to the end of the macro bypassing majority of the code at the lines based below.
Set objItem = Application.ActiveInspector.currentItem
If Not objItem Is Nothing Then
If objItem.Class = olMail Then
Try commenting out this line and the matching if statement and see if it works
If objItem.Class = olMail Then
Thanks for replying I tried this and it threw compile errors. Perhaps I did it wrong. Could you send the code as you want me to test?
Currently my skype template has the below. I'd like to append 2 lines to it so it can have a number for Canada and United States.
Join by phone
+35315661080 (Dial-in Number) English (United Kingdom)
Find a local number
This is excatly what I was looking for, but unfortunatly, the macro does not react at all. I did follow the guidelines above. I am using Outlook 2016. Any advice?
Do you have macro security set to low and restarted Outlook?
Any error messages?
Does the macro run at all? Add msgbox "Running" to it to see if the message box comes up.
Diane,
In your post above you wrote, "There are two ways you can do this: create HTML stationery to replace the template and use Regex to get the meeting code"
Do offer instructions for how to do this?
I don't have instructions but will look at writing some. It will require using VBA - so if you can't use the current macro, using the default f=template won't work either.
I was probably thinking of doing something like this - https://www.slipstick.com/outlook/email/create-new-message-using-html-file-stationery/ - you'd create your own template and then use a macro to insert the template and replace the meeting code placeholder with the one for the meeting.