Update Contact Area Codes

This version of the bulk change macros combines the code from Remove Prefix and Reformat Phone Numbers with the Bulk Change Contacts code to change the area code (or country code) of selected contacts.

To use this macro, select the contacts that need updated then run the macro.

If you want to run it on all contacts in the selected folder, use the FixFormat function below with the FixPhoneFormat macro at Remove Prefix macro.

To change the country code, change the Replace line: strPhone = Replace(strPhone, "+1", "+44")

Public Sub ChangeSelectedAreaCode()
    Dim Session As Outlook.NameSpace
    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim oFolder As Folder
    
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
    Set oFolder = currentExplorer.CurrentFolder
    
 If Left(UCase(oFolder.DefaultMessageClass), 11) <> "IPM.CONTACT" Then
   MsgBox "You need to select a Contacts folder", vbExclamation
 Exit Sub
 End If

    On Error Resume Next

 Dim nCounter As Integer
 nCounter = 0
 
 Dim oItem
 For Each oItem In Selection
 Dim oContact As ContactItem

' If the current item is a not a Contact Group process the phone numbers
' If it's a DL, the code moves on to the next item
 If TypeName(oItem) <> "DistListItem" Then

 Set oContact = oItem
 With oContact
 .AssistantTelephoneNumber = FixFormat(.AssistantTelephoneNumber)
 .Business2TelephoneNumber = FixFormat(.Business2TelephoneNumber)
 .BusinessFaxNumber = FixFormat(.BusinessFaxNumber)
 .BusinessTelephoneNumber = FixFormat(.BusinessTelephoneNumber)
 .CallbackTelephoneNumber = FixFormat(.CallbackTelephoneNumber)
 .CarTelephoneNumber = FixFormat(.CarTelephoneNumber)
 .CompanyMainTelephoneNumber = FixFormat(.CompanyMainTelephoneNumber)
 .Home2TelephoneNumber = FixFormat(.Home2TelephoneNumber)
 .HomeFaxNumber = FixFormat(.HomeFaxNumber)
 .HomeTelephoneNumber = FixFormat(.HomeTelephoneNumber)
 .ISDNNumber = FixFormat(.ISDNNumber)
 .MobileTelephoneNumber = FixFormat(.MobileTelephoneNumber)
 .OtherFaxNumber = FixFormat(.OtherFaxNumber)
 .OtherTelephoneNumber = FixFormat(.OtherTelephoneNumber)
 .PagerNumber = FixFormat(.PagerNumber)
 .PrimaryTelephoneNumber = FixFormat(.PrimaryTelephoneNumber)
 .RadioTelephoneNumber = FixFormat(.RadioTelephoneNumber)
 .TelexNumber = FixFormat(.TelexNumber)
 .TTYTDDTelephoneNumber = FixFormat(.TTYTDDTelephoneNumber)
 
.Save
 
nCounter = nCounter + 1
 End With
 End If
 Next
 
MsgBox nCounter & " contacts processed.", vbInformation
    Set Session = Nothing
    Set currentExplorer = Nothing
    Set obj = Nothing
    Set Selection = Nothing

End Sub

Private Function FixFormat(strPhone As String) As String

strPhone = Trim(strPhone)
 FixFormat = strPhone
 If strPhone = "" Then Exit Function
 
'if using (nnn) area code format
' works with country codes
strPhone = Replace(strPhone, "(440)", "(123)")

FixFormat = strPhone
Debug.Print FixFormat
End Function

If you have some contacts with phone numbers that aren't formatted as phone numbers (ie, in 4405551212 format), this version of the FixFormat function will replace the first 3 numbers with the new area code. When the contact is saved, Outlook will convert it to (440) 555-1212 format.

Private Function FixFormat(strPhone As String) As String

strPhone = Trim(strPhone)
 FixFormat = strPhone
 If strPhone = "" Then Exit Function
 
'if using (nnn) area code format
' works with country codes
strPhone = Replace(strPhone, "(440)", "(123)")

' no () around phone 4405551212
If Left(strPhone, 3) = "440" Then
' change the first 3
    strPhone = Right(strPhone, Len(strPhone) - 3)
    Debug.Print strPhone
    strPhone = "440" & strPhone
End If

FixFormat = strPhone
Debug.Print FixFormat
End Function

How to use the macros on this page

First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.

To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look 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.

The macros on this page should be placed in a module.

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