Normalizing Phone Numbers in Outlook Contacts

I have a very Pesky customer that would now like to have all of his Outlook phone numbers look the same.  A lot of the numbers are imports from Act, other contact lists etc.  So some of them are formatted as xxxxxxxxxx, some xxx-xxx-xxxx and others the way he'd like to see them (xxx) xxx-xxxx.  The settings in the modem and dialing options are correct and newly entered numbers are fine, it's just all of the old numbers that are the issue.  The only way I know how to fix this would be to manually enter each contact and erase a single number of the phone number and retype it which will automatically format correctly.

Outlook is 2007 and Windows is XP Pro.

Thanks
Fluid_ImageryAsked:
Who is Participating?
 
David LeeCommented:
Try this.
Sub FormatPhoneNumbers() 
    Const PHONE_FIELDS = "AssistantTelephoneNumber,Business2TelephoneNumber,BusinessFaxNumber,BusinessTelephoneNumber,CallbackTelephoneNumber,CarTelephoneNumber,Home2TelephoneNumber,HomeFaxNumber,HomeTelephoneNumber,ISDNNumber,MobileTelephoneNumber,OtherFaxNumber,OtherTelephoneNumber,PagerNumber,PrimaryTelephoneNumber,RadioTelephoneNumber,TelexNumber,TTYTDDTelephoneNumber" 
    Dim olkFolder As Outlook.Folder, _ 
        olkItem As Object, _ 
        olkProp As Outlook.ItemProperty, _ 
        arrNumbers As Variant, _ 
        varNumber As Variant, _ 
        strTemp As String 
    arrNumbers = Split(PHONE_FIELDS, ",") 
    Set olkFolder = Application.ActiveExplorer.CurrentFolder 
    For Each olkItem In olkFolder.Items 
        If olkItem.Class = olContact Then 
            For Each varNumber In arrNumbers 
                Set olkProp = olkItem.ItemProperties(varNumber) 
                olkProp.Value = olkProp.Value 
                olkItem.Save 
            Next 
        End If 
    Next 
    Set olkItem = Nothing 
    Set olkFolder = Nothing 
    msgbox "All done!" 
End Sub

Open in new window

0
 
ICaldwellCommented:
You can use Outlook Tools to do this... Its a free download:

http://www.softwarelode.com/31978/details-outlook-tools.html
0
 
David LeeCommented:
Hi, Fluid_Imagery.

The code below should fix all the numbers.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor
10. Select the Contact folder to process
11. Run the macro

I recommend backing the contact up before running the code just in case.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
David LeeCommented:
I don't know what happened to the code.  Let's try this again.
Sub FormatPhoneNumbers()
    Const PHONE_FIELDS = "AssistantTelephoneNumber,Business2TelephoneNumber,BusinessFaxNumber,BusinessTelephoneNumber,CallbackTelephoneNumber,CarTelephoneNumber,Home2TelephoneNumber,HomeFaxNumber,HomeTelephoneNumber,ISDNNumber,MobileTelephoneNumber,OtherFaxNumber,OtherTelephoneNumber,PagerNumber,PrimaryTelephoneNumber,RadioTelephoneNumber,TelexNumber,TTYTDDTelephoneNumber"
    Dim olkFolder As Outlook.Folder, _
        olkContact As Outlook.contactItem, _
        olkProp As Outlook.ItemProperty, _
        arrNumbers As Variant, _
        varNumber As Variant, _
        strTemp As String
    arrNumbers = Split(PHONE_FIELDS, ",")
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    For Each olkContact In olkFolder.Items
        For Each varNumber In arrNumbers
            Set olkProp = olkContact.ItemProperties(varNumber)
            olkProp.Value = olkProp.Value
            olkContact.Save
        Next
    Next
    Set olkContact = Nothing
    Set olkFolder = Nothing
    msgbox "All done!"
End Sub

Open in new window

0
 
Fluid_ImageryAuthor Commented:
BlueDevil,

I'm getting a type mismatch error when I try to run the script (runtime error 13).  Any ideas?
0
 
David LeeCommented:
My fault.  I forgot to check for and exclude distribution lists.  Please try this version.
Sub FormatPhoneNumbers()
    Const PHONE_FIELDS = "AssistantTelephoneNumber,Business2TelephoneNumber,BusinessFaxNumber,BusinessTelephoneNumber,CallbackTelephoneNumber,CarTelephoneNumber,Home2TelephoneNumber,HomeFaxNumber,HomeTelephoneNumber,ISDNNumber,MobileTelephoneNumber,OtherFaxNumber,OtherTelephoneNumber,PagerNumber,PrimaryTelephoneNumber,RadioTelephoneNumber,TelexNumber,TTYTDDTelephoneNumber"
    Dim olkFolder As Outlook.Folder, _
        olkItem As Object, _
        olkProp As Outlook.ItemProperty, _
        arrNumbers As Variant, _
        varNumber As Variant, _
        strTemp As String
    arrNumbers = Split(PHONE_FIELDS, ",")
    Set olkFolder = Application.ActiveExplorer.CurrentFolder
    For Each olkItem In olkFolder.Items
        If olkItem.Class = olContact Then
            For Each varNumber In arrNumbers
                Set olkProp = olkContact.ItemProperties(varNumber)
                olkProp.Value = olkProp.Value
                olkContact.Save
            Next
        End If
    Next
    Set olkItem = Nothing
    Set olkFolder = Nothing
    msgbox "All done!"
End Sub

Open in new window

0
 
Fluid_ImageryAuthor Commented:
Runtime error 424 Object Required.
0
 
Fluid_ImageryAuthor Commented:
if it helps, it's pointing to the
"Set olkProp = olkContact.ItemProperties(varNumber) "  line
0
 
WaidentCommented:
Just an FYI - I had this same problem and was going to use this VBA script but instead tried an easier method (for me at least).  I exported all the contacts out of outlook, deleted them all from the contacts folder, and reimported them back in and they all came up with the (xxx) xxx-xxxx format.  

Cheers!
0
 
BTCTechXpertsCommented:
To expand on Waident's much eaiser solution; just make sure you export to CSV(DOS) instead of PST, otherwise it will not reformat the phone numbers for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.