Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Is there a way of inserting hyphens (-) into all of the existing phone numbers contained in my Outlook 2007 contacts list? So the format will be 123-456-7890 instead of 1234567890

Posted on 2009-12-21
11
Medium Priority
?
476 Views
Last Modified: 2012-05-08
Is there a way of inserting hyphens (-) into all of the existing phone numbers contained in my Outlook 2007 contacts list? So the format will be 123-456-7890 instead of 1234567890.

What is the easiest way of dong this? As I have mentioned, most all of the phone numbers currently don't contain hyphens.

I know that one way of doing this might be to export all of the data into Microsoft Excel and then format the Microsoft Excel columns and then import all of this data back into my Outlook contacts. However, I am looking for an easier way of doing this.

How can this be done?
0
Comment
Question by:Knowledgeable
11 Comments
 
LVL 12

Accepted Solution

by:
LingerLonger earned 2000 total points
ID: 26100436
You could use something like Data Fields Manager - http://www.datafieldsmanager.com/. Unfortunately, not a freebie.
0
 
LVL 10

Expert Comment

by:jasfout
ID: 26100600
With the help of:
http://www.freevbcode.com/ShowCode.asp?ID=1968

I was able customize to do what you are asking using vba

From Outlook <Alt>+<F11>
Insert a new module
Paste the contents of the code snippet below into that module
Run the sub FixPnumbers


Private Sub FixPNumbers()
    Dim items As items, item As ContactItem, folder As folder
    Dim contactItems As Outlook.items
    Dim itemContact As Outlook.ContactItem

    Set folder = Session.GetDefaultFolder(olFolderContacts)
    Set items = folder.items

    Set contactItems = items.Restrict("[MessageClass]='IPM.Contact'")
  
    For Each itemContact In contactItems
        
        itemContact.MobileTelephoneNumber = FormatPhoneNumber(itemContact.MobileTelephoneNumber)
        ' add aditional number fields as needed
        itemContact.BusinessTelephoneNumber = FormatPhoneNumber(itemContact.BusinessTelephoneNumber)
        
        itemContact.Save
    Next
End Sub





'Pulled from http://www.freevbcode.com/ShowCode.asp?ID=1968
'Takes any entered phone number and returns it in ###-#### format
'or (###) ###-####

Public Function FormatPhoneNumber(ByVal sNumToBeFormatted As _
   String) As String

Dim iNumberLength As Integer 'Used for the Phone Number length
   
'Trim any leading and trailing spaces

sNumToBeFormatted = Trim$(sNumToBeFormatted)
   
'Length of the phone number.

iNumberLength = Len(sNumToBeFormatted)
   
Select Case iNumberLength

  Case 7  'Format : #######

    FormatPhoneNumber = Left$(sNumToBeFormatted, 3) & _
        "-" & Right$(sNumToBeFormatted, 4)
    Exit Function

  Case 8  'Format : ###-#### or ### ####

    If Mid$(sNumToBeFormatted, 4, 1) = "-" Then
       FormatPhoneNumber = sNumToBeFormatted
       Exit Function
    Else
       FormatPhoneNumber = Left$(sNumToBeFormatted, 3) & "-" & _
          Right$(sNumToBeFormatted, 4)
       Exit Function
    End If

  Case 10 'Format : ##########

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " _
   & Mid$(sNumToBeFormatted, 4, 3) & "-" & _
     Right$(sNumToBeFormatted, 4)
 
   Exit Function

  Case 11 'Format ######-####

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " & _
       Right$(sNumToBeFormatted, 8)
    Exit Function

  Case 12 'Format : ### ###-####

 FormatPhoneNumber = "(" & Left$(sNumToBeFormatted, 3) & ") " & _
      Mid$(sNumToBeFormatted, 5, 3) & "-" & _
      Right$(sNumToBeFormatted, 4)
    Exit Function

  Case 13 'Format : (###)###-####
     FormatPhoneNumber = Left(sNumToBeFormatted, 5) & " " & _
        Right(sNumToBeFormatted, 8)
     Exit Function


  Case Else
        'Return Value Passed
     FormatPhoneNumber = sNumToBeFormatted
           
End Select

End Function

Open in new window

0
 
LVL 10

Expert Comment

by:jasfout
ID: 26100612
Good question BTW
 I had a few numbers like that in my contacts, never really cared much, but this cleaned it up.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Knowledgeable
ID: 26100746
jasfout,

When I tried to compile the code using the instructions that you provided, I received the following error: "Compile error: Sub or Function not defined." (Refer to the screenshots).

What do I need to do to resolve this error so that I can execute the code that you have provided?
Complile-error.JPG
0
 

Author Comment

by:Knowledgeable
ID: 26100753
Compile error-Full screen screenshot.
Outlook-VB-error.JPG
0
 
LVL 10

Expert Comment

by:jasfout
ID: 26100800
it looks like you are missing second part which is the:
FormatPhoneNumber
function

Copy & paste everything in the snippet into that module
0
 
LVL 10

Expert Comment

by:jasfout
ID: 26100805
The second part begins with:
'Pulled from http://www.freevbcode.com/ShowCode.asp?ID=1968

0
 

Author Comment

by:Knowledgeable
ID: 26100926
jasfout,

Now I'm getting an "Invalid Procdeure Name" error message.

What can be done to fix this so that the code executes properly?
0
 
LVL 10

Expert Comment

by:jasfout
ID: 26101259
can you please paste the code you have in the module?
0
 
LVL 10

Expert Comment

by:jasfout
ID: 26101364
I meant, can you please post the code from your entire module for us to see here.
0
 
LVL 1

Expert Comment

by:migadmin
ID: 31406485
If you configure dialing options on your system, specifying your country and area code, Outlook will automatically convert numbers into (XXX) XXX-XXXX format.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an article on how to answer questions, earn points and become an expert.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question