Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fields and Macros
I got a lot of help last month (special thanks to G. Mayor) setting up
Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! -- Joe McGuire |
#2
|
|||
|
|||
The function that enables Outlook data to be inserted by the macro will not
access all the fields in Outlook contacts. The fields that may be accessed are listed at the end of the web page http://www.gmayor.com/Macrobutton.htm If the field that contains the data you wish to use is contained in one of those fields then it should be possible to conditionally choose between them in the macro code. The problem area that I have not been able to resolve is the need to access Outlook before you can determine what the record contains and then decide what to do with it (better minds than mine frequent the vba newsgroups is you wish to bounce the problem there to overcome the double access). Public Sub InsertAddressAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then MsgBox "No office number available" & vbCr _ & "select again for alternative number" 'Define alternative number strPhone = "PR_OFFICE2_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Joseph McGuire wrote: I got a lot of help last month (special thanks to G. Mayor) setting up Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! |
#3
|
|||
|
|||
I have had the opportunity to play around a little more and have modified
the code to avoid the double access - when all else fails reading the instructions often works - Try the following. You'll still have to set your choice of alternative fields - this example uses office phone with home phone as alternative Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then 'Define alternative number strPhone = "PR_HOME_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 2, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: The function that enables Outlook data to be inserted by the macro will not access all the fields in Outlook contacts. The fields that may be accessed are listed at the end of the web page http://www.gmayor.com/Macrobutton.htm If the field that contains the data you wish to use is contained in one of those fields then it should be possible to conditionally choose between them in the macro code. The problem area that I have not been able to resolve is the need to access Outlook before you can determine what the record contains and then decide what to do with it (better minds than mine frequent the vba newsgroups is you wish to bounce the problem there to overcome the double access). Public Sub InsertAddressAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then MsgBox "No office number available" & vbCr _ & "select again for alternative number" 'Define alternative number strPhone = "PR_OFFICE2_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub Joseph McGuire wrote: I got a lot of help last month (special thanks to G. Mayor) setting up Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! |
#4
|
|||
|
|||
Thanks! I'll give it a try.
JWM "Graham Mayor" wrote in message ... I have had the opportunity to play around a little more and have modified the code to avoid the double access - when all else fails reading the instructions often works - Try the following. You'll still have to set your choice of alternative fields - this example uses office phone with home phone as alternative Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then 'Define alternative number strPhone = "PR_HOME_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 2, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: The function that enables Outlook data to be inserted by the macro will not access all the fields in Outlook contacts. The fields that may be accessed are listed at the end of the web page http://www.gmayor.com/Macrobutton.htm If the field that contains the data you wish to use is contained in one of those fields then it should be possible to conditionally choose between them in the macro code. The problem area that I have not been able to resolve is the need to access Outlook before you can determine what the record contains and then decide what to do with it (better minds than mine frequent the vba newsgroups is you wish to bounce the problem there to overcome the double access). Public Sub InsertAddressAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then MsgBox "No office number available" & vbCr _ & "select again for alternative number" 'Define alternative number strPhone = "PR_OFFICE2_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub Joseph McGuire wrote: I got a lot of help last month (special thanks to G. Mayor) setting up Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! |
#5
|
|||
|
|||
Graham:
At first this macro would not run due to a Compile error: Ambiguous name detected: InsertNameAndPhoneFromOutlook. Then I realized that the first two line of the macro were repeated. I cut them and the macro worked. Only problem is that it does no offer a choice between a Contact's two numbers. It enters the BusinessNumber or if there is none, the HomeNumber. I may be mistaken but there seems to be no field name that can be used in the macro to get the Company name from OL.Your site does no list one and I could not find one in the various Word KB papers. "Graham Mayor" wrote in message ... I have had the opportunity to play around a little more and have modified the code to avoid the double access - when all else fails reading the instructions often works - Try the following. You'll still have to set your choice of alternative fields - this example uses office phone with home phone as alternative Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then 'Define alternative number strPhone = "PR_HOME_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 2, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: The function that enables Outlook data to be inserted by the macro will not access all the fields in Outlook contacts. The fields that may be accessed are listed at the end of the web page http://www.gmayor.com/Macrobutton.htm If the field that contains the data you wish to use is contained in one of those fields then it should be possible to conditionally choose between them in the macro code. The problem area that I have not been able to resolve is the need to access Outlook before you can determine what the record contains and then decide what to do with it (better minds than mine frequent the vba newsgroups is you wish to bounce the problem there to overcome the double access). Public Sub InsertAddressAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then MsgBox "No office number available" & vbCr _ & "select again for alternative number" 'Define alternative number strPhone = "PR_OFFICE2_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub Joseph McGuire wrote: I got a lot of help last month (special thanks to G. Mayor) setting up Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! |
#6
|
|||
|
|||
Sorry about the duplication - a paste error
The only fields available for certain are those documented at the end of http://www.gmayor.com/Macrobutton.htm I suspect there may be others undocumented (I found one that is listed) but you would have to experiment to find others. Company name is listed and there are several different telephone numbers to choose from. You don't have to stick with the ones I used. -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Joseph McGuire wrote: Graham: At first this macro would not run due to a Compile error: Ambiguous name detected: InsertNameAndPhoneFromOutlook. Then I realized that the first two line of the macro were repeated. I cut them and the macro worked. Only problem is that it does no offer a choice between a Contact's two numbers. It enters the BusinessNumber or if there is none, the HomeNumber. I may be mistaken but there seems to be no field name that can be used in the macro to get the Company name from OL.Your site does no list one and I could not find one in the various Word KB papers. "Graham Mayor" wrote in message ... I have had the opportunity to play around a little more and have modified the code to avoid the double access - when all else fails reading the instructions often works - Try the following. You'll still have to set your choice of alternative fields - this example uses office phone with home phone as alternative Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String Public Sub InsertNameAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then 'Define alternative number strPhone = "PR_HOME_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 2, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: The function that enables Outlook data to be inserted by the macro will not access all the fields in Outlook contacts. The fields that may be accessed are listed at the end of the web page http://www.gmayor.com/Macrobutton.htm If the field that contains the data you wish to use is contained in one of those fields then it should be possible to conditionally choose between them in the macro code. The problem area that I have not been able to resolve is the need to access Outlook before you can determine what the record contains and then decide what to do with it (better minds than mine frequent the vba newsgroups is you wish to bounce the problem there to overcome the double access). Public Sub InsertAddressAndPhoneFromOutlook() Dim strCode, strAddress, strName, strPhone As String strName = "PR_DISPLAY_NAME" strPhone = "PR_OFFICE_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone 'Let the user choose the name in Outlook strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) If Right(strAddress, 1) = Chr(9) Then MsgBox "No office number available" & vbCr _ & "select again for alternative number" 'Define alternative number strPhone = "PR_OFFICE2_TELEPHONE_NUMBER" strCode = strName & vbTab & strPhone strAddress = Application.GetAddress("", strCode, _ False, 1, , , True, True) End If 'Insert the name at the current insertion point If ActiveDocument.ProtectionType wdNoProtection Then bProtected = True ActiveDocument.Unprotect ActiveDocument.FormFields("NameOfFormfield").Resul t = _ strAddress Else Selection.TypeText strAddress End If End Sub Joseph McGuire wrote: I got a lot of help last month (special thanks to G. Mayor) setting up Macrobuttons with a Macro to pull certain information, including a Contact's phone number, from OL and insert it in my Word document. Double-clicking on the button gets me the Contact's name and his/her phone number and puts it in a Word document that I use as a log to account for phone charges. This has worked quite well until I hit a glitch. The macro is set to retrieve OFFICE_TELEPHONE_NUMBER. Most of my contacts have a Business phone, as the field seems to be called in OL, which I use for their direct line. This appears to be what the macro is retrieving. For others I have their phone number as Company Phone since I don't know or they do not have a direct line. For such Contacts, my macro returns the Contact's name but a blank for the number. Then I have to cut and paste the number into my log, which is why I wanted the Macro/Macrobutton in the first place. Is there any reasonable way short of a vba nightmare to either set some priorities (e.g., if there is no Business Number, look for Company Number? Let's not even get into Contacts who also have mobile numbers, etc. The combination of Business Phone and Company phone probably cover 95% of my billable calls. I will be happy if this macro works for that 95% but I am not going to kill myself to get from 95 to 100%! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving a merge letter changes previous letters | Mailmerge | |||
How do I mail merge fields exceeding 255 characters from Excel to. | Mailmerge | |||
How do I create & merge specific data base & master documents? | New Users | |||
word xp crashes after macros are recorded | Microsoft Word Help |