Format Contact phone numbers to use with Skype

Andrew wanted to place Skype calls using Outlook Contacts but the country codes are missing from his contacts. While Outlook can automatically add the country code when you use the Call Phone feature, Skype dials the number as exactly as shown in the contact. Andrew put together this macro to add the country code to the phone numbers. This code is similar to our code sample that cleans up area codes and prefixes, found at Remove Prefix and Reformat Phone Numbers.

To use, add to the VBA editor, change the country code to the correct one for your location, and if your country code is not 2 digits, change the value in this line: new_phone_no = my_country_code + Mid$(phone_no, 2) and then run it. Note that this is intended for use on phone numbers that are in your own country. If international numbers begin with +, the code will ignore the contacts.

Sub correct_phone_nos_country_code()
Dim olApp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim objItems As Outlook.Items
Dim objItem As Object, objAdd As Object
Dim new_no As String

Set olApp = CreateObject("Outlook.Application")
Set myNameSpace = olApp.GetNamespace("MAPI")

Set objFolder = myNameSpace.GetDefaultFolder(olFolderContacts) ' Main (default) contacts folder
Set objItems = objFolder.Items

For Each objItem In objItems
' Make sure we have a contact item
With objItem
If .Class = olContact Then

Debug.Print .FileAs, .BusinessTelephoneNumber, .HomeTelephoneNumber, .MobileTelephoneNumber

.HomeTelephoneNumber = correct_phone_no_to_international(.HomeTelephoneNumber)
.BusinessTelephoneNumber = correct_phone_no_to_international(.BusinessTelephoneNumber)
.HomeTelephoneNumber = correct_phone_no_to_international(.HomeTelephoneNumber)
.MobileTelephoneNumber = correct_phone_no_to_international(.MobileTelephoneNumber)
.BusinessFaxNumber = correct_phone_no_to_international(.BusinessFaxNumber)

objItem.Save
End If
End With
Next

End Sub

'=============================
Function correct_phone_no_to_international(phone_no As String) As String
Dim new_phone_no As String
Dim my_country_code As String

'****change this to your country code:
' if your country code is not 2 digits, make additional change below
my_country_code = "+44"
'***********

correct_phone_no_to_international=phone_no

If Len(Trim(phone_no)) = 0 Then
    change_phone_no_to_international = ""
    Exit Function
End If

If Left$(phone_no, 1) <> "+" Then
' replace 2 with the number of digits in your country code
    new_phone_no = my_country_code + Mid$(phone_no, 2)
    correct_phone_no_to_international = new_phone_no
End If
End Function

How to use a macro

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, 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:

1. Right click on Project1 and choose Insert > Module
2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor