Outlook 2007 Fix Phone Number

Status
Not open for further replies.

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
I have the following macro that if I have a phone number in the note field of 7136669999, when I run the macro it fixes the phone number to: 713.666.9999 But this macro does not do it when the phone number is in the business phone number field, the home phone number field or the cell phone number field. So what can we add to this for each phone number fields please, as this helps me very quickly in terms of my contacts.

Thanks very much!

Public Sub PhoneNumberFix()

Dim Ins As outlook.inspector
Dim Document As Word.Document
Dim Word As Word.Application
Dim Selection As Word.Selection

Set Ins = Application.ActiveInspector
Set Document = Ins.WordEditor
Set Word = Document.Application
Set Selection = Word.Selection

Selection.HomeKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."

End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
That macro won't work on the numbers in the phone fields - you'll need to reference the field directly. This also assumes all numbers are 10 digits.

Set olItem = objApp.ActiveInspector.CurrentItem
txtphone = olItem.businesstelephonenumber
oitem.businesstelephonenumber = left(txtphone, 3) & ""." & mid(txtphone, 4,3) & "." & right(txtphone,4)


i have a macro at http://www.slipstick.com/developer/remove-prefix-phone-number/ that is used to removed country codes - with a little tweaking, it could work here too.

At the end of the function that cleans up the numbers, add the line to add the dots:
strPhone = Replace(strPhone, "-", "")
strPhone = left(strPhone, 3) & ""." & mid(strPhone, 4,3) & "." & right(strPhone,4)
FixFormat = strPhone
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
oh, and that code works on all contacts in the selected folder. to use it on the current message, replace the for each oitem in ofolder.items with

Set olItem = objApp.ActiveInspector.CurrentItem

then remove the next item.


oh, and if objApp errors change it to the application object you're using or replace it with "application"
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Thanks very much. Can you post the full code I should use please?
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Again, I got what you said but I don't know how to change the code. Can you give me the full code that I can use please?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
I don't want to duplicate the code here.

Replace
Dim oItem
For Each oItem In oFolder.Items
Dim oContact As ContactItem


with
Dim oItem
Set olItem = application.ActiveInspector.CurrentItem
Dim oContact As ContactItem

Remove the Next:
nCounter = nCounter + 1
End With
End If
Next


Replace:
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, ".", "")
strPhone = Replace(strPhone, " ", "")
strPhone = Replace(strPhone, "-", "")

FixFormat = strPhone
End Function


With

strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, ".", "")
strPhone = Replace(strPhone, " ", "")
strPhone = Replace(strPhone, "-", "")
strPhone = left(strPhone, 3) & ""." & mid(strPhone, 4,3) & "." & right(strPhone,4)
FixFormat = strPhone
End Function
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Sorry...I don't understand as again it is not my area. Here is the code I showed you before, and what do I change to this code in all areas?

Public Sub PhoneNumberFix()

Dim Ins As outlook.inspector
Dim Document As Word.Document
Dim Word As Word.Application
Dim Selection As Word.Selection

Set Ins = Application.ActiveInspector
Set Document = Ins.WordEditor
Set Word = Document.Application
Set Selection = Word.Selection

Selection.HomeKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."

End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Your code won't work on the numbers in the phone fields. Get the macro from http://www.slipstick.com/developer/remove-prefix-phone-number - if you want to change both the fields and the body, add FixPhoneFormat right before End Sub in your code:

Selection.TypeText Text:="."
FixPhoneFormat
End Sub

your macro will check the body, then it will call the new macro and check the fields.
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Thanks very much, but I just don't know how to do it....can you give me the code I just use?

Thanks very much again, as your are the one knows how to do it!!!
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
I there is a code that does it, please let me know. And I figured out that when I copy the numbers that need to be fixed and then go to the business phone field, the following code copies the numbers directly to the note field, fixes it, copies it, deletes it from the note field an paste to the business phone field. So also, is there a code that pastes to the note field?

Public Sub BusinessPhoneNumberFixPaste()

Dim Ins As outlook.inspector
Dim Document As Word.Document
Dim Word As Word.Application
Dim Selection As Word.Selection

Set Ins = Application.ActiveInspector
Set Document = Ins.WordEditor
Set Word = Document.Application
Set Selection = Word.Selection


Selection.PasteAndFormat (wdPasteDefault)
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.HomeKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.TypeText Text:="."
Selection.HomeKey Unit:=wdLine
Selection.MoveRight Unit:=wdCharacter, Count:=12, Extend:=wdExtend
Selection.Copy
Selection.Delete Unit:=wdCharacter, Count:=1

Call PastetoBusinessPhone

End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
as written, it fixes all 19 phone number fields - but if you never use certain field, you can remove those lines. It won't touch the notes field - use your current macro for that.
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Thanks so much. Can you tell me the lines re the business phone, home phone and cell phone? As that's all I need.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Look at the code I posted - it should be self explanatory -
.HomeTelephoneNumber = FixFormat(.HomeTelephoneNumber)
.BusinessTelephoneNumber = FixFormat(.BusinessTelephoneNumber)
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
Thanks very much, as you have helped me so so much over the years!!!
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
I used it and it worked perfect when I put in the number save the contact and open it up again, and run the macro. But if I don't save the contact and open it up, when I run the macro, it deletes the numbers on the field. And thing to add to it so I don't have to save and open the contact to run the macro, which again, did it when I save and open the contact.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
it deleted the numbers because it sees the field is empty - it doesn't see the numbers if its not saved. You'll need to save first, which you can do at the beginning of the macro by adding .save as the first line under With oitem.
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
so put the words .Save below the words With oitem before the next line .AssistantTelepohneNumber = ?
 

LMS

Senior Member
Outlook version
Outlook 2007
Email Account
Exchange Server
I just did what I asked you, and it worked perfect!!! Thanks so much!!!
 
Status
Not open for further replies.
Top