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.
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:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
More Bulk Change Contact articles at Slipstick.com:
- Bulk Change Contact's FileAs Format to Match Outlook's Default Setting
- Bulk Change Email Display Name Format
- Bulk Change File As Format for Contacts
- Bulk Move Phone Numbers to a Different Phone Field
- Macro to Swap First and Last Name Fields
- Show the Home Address on a Contact Form by Default
- Update Contacts with a New Company Name and Email Address