Outlook 2007 Fix Phone Number

Status
Not open for further replies.

LMS

Senior Member
Outlook version
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
Email Account
Exchange Server
Thanks very much. Can you post the full code I should use please?
 

LMS

Senior Member
Outlook version
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
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
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
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
I would use the code in the attached file -
 

Attachments

  • fixphones.txt
    2.1 KB · Views: 397

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
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
Email Account
Exchange Server
Thanks very much, as you have helped me so so much over the years!!!
 

LMS

Senior Member
Outlook version
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
Email Account
Exchange Server
so put the words .Save below the words With oitem before the next line .AssistantTelepohneNumber = ?
 

LMS

Senior Member
Outlook version
Email Account
Exchange Server
I just did what I asked you, and it worked perfect!!! Thanks so much!!!
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
P Outlook 2007 Calendar Ribbon has changed and I can't fix it Using Outlook 10
R RE: Any Fix For Font & Spacing Issues in Outlook 2007? Using Outlook 2
W Transfer Outlook 2016 autocomplete file to Outlook 2007 Using Outlook 1
C Outlook 2007 Removing then adding account restores junk email processing Using Outlook 0
S Outlook 2007 crash linked to gdiplus.dll Using Outlook 0
S Outlook 2007 - Automatic purge fail Using Outlook 0
J outlook 2007 doesn't let me choose which .pst to search Using Outlook 2
D Outlook 2007 vs. Outlook 2010 -- ToDo Bar Using Outlook 0
D Outlook 2007 on 365 Using Outlook.com accounts in Outlook 2
S Macro for other actions - Outlook 2007 Outlook VBA and Custom Forms 23
S Verwendung von Outlook 2007 Using Outlook 0
A Arthur needs help with 2007 Outlook e-mail Using Outlook.com accounts in Outlook 3
M PST import from Outlook 2007 to 2010 - Address Book contacts all in 1 group Using Outlook 4
S outlook 2007 calendar search Using Outlook 6
B Migrate Outlook 2007 to Office 365 Using Outlook 3
X I have met my waterloo trying to resolve embedded graphics problem with outlook 2007 and now 2016 Using Outlook 1
R Outlook 2007 only loads some appointments Using Outlook 0
C Move Outlook 2007 to new PC with Outlook 365 Using Outlook 3
J Outlook 2007 Hide Messages Option not Available Using Outlook 2
S Outlook 2007 Calendar instant search problem. Windows 7 Using Outlook 4
S Outlook 2007 Calendar instant search problem. Windows 7 Using Outlook 0
B Server errors Outlook 2007 Using Outlook 1
S Reboot of frozen windows7 results in changed outlook 2007 settings Using Outlook 1
S Outlook 2007 printing wrong email address at top of page Using Outlook 8
M Configure outlook 2007 to accept digital signatures Using Outlook 2
D Outlook 2007 crashes when opening an email Using Outlook 2
R New chap saying hello and needing advice on Outlook 2007 thumbnails Using Outlook 3
icacream From Outlook 2007 to 2016 ! Using Outlook 9
vodkasoda Object could not be found Error in Outlook 2007 Outlook VBA and Custom Forms 5
S Outlook 2007: Address Cards allow entering text! Why? Using Outlook 3
S View Appointment in Text Wrap in Outlook 2007 Month Calendar View Using Outlook 0
L Outlook 2007 Separate the Send/Receive functions Using Outlook 2
M Outlook 2007 Contacts Glitch: Creating a new email Using Outlook 1
C Move from Outlook 2007 Enterprise (MOE) to Outlook Pro plus 2007 Using Outlook 1
J reinstalling Outlook 2007 asking for user name & password Using Outlook 14
P outlook addin unloaded in office 2007 Using Outlook 0
B Fonts in Outlook 2007 Using Outlook 4
R Add Exchange Account to existing POP3 Outlook 2007 Profile Using Outlook 0
C out of space in file group Outlook 2007 Using Outlook 2
A Moving archived contents in Outlook 2007 back into working folders Using Outlook 0
P Outlook 2007 Email Categorization using VBA Outlook VBA and Custom Forms 1
M Unable to Configure Gmail Account in Outlook 2007 Using Outlook 1
R Outlook 2007 or 2010 - Lock Down Functionality Outlook VBA and Custom Forms 3
S Outlook 2007, windows 10 Font size Using Outlook 1
Diane Poremsky Manually create a POP3 account in Outlook 2007 Using Outlook 0
J Can Click to Drag Custom Form Field But Cannot Drop When Designing in Outlook 2007 Outlook VBA and Custom Forms 2
L Outlook 2007 Font Using Outlook 3
J Outlook 2007 connector and Windows 10 Using Outlook 3
R Outlook 2007 - Shared Accounts and Resources without Exchange Server Using Outlook 0
L Outlook 2007 - Macro Re Search Using Outlook 16

Similar threads

Top