Urgent! Further VB Asssitance Word/Outlook Integration

[Shauna Koppang]

Hi,

The following macro works perfectly with only one thing
that needs to be changed and I am not sure how to do it.
It has to do with the Address in Outlook. If I have an
address that has been entered into Outlook on multi
lines, when it comes into Word using this or even the
AddressLayout AutoText entry, it puts in a hard return in
the address, not a line break which it does for a single
line address. Is there a way in this macro to have it
check the address that is brought in and change any hard
returns it finds into line breaks? The reason is I have
another macro that picks up the address block as a single
item (no hard returns) then places it on an envelope.
This is the last thing to finish on this project and I
need to finish it today if possible. THANKS SO MUCH!!!
Shauna


Private Sub CommandButton1_Click()

Setup.Hide
'Unload Setup

Application.StatusBar = "Word is customizing the
letter with the information you have provided"
Application.ScreenUpdating = False

Dim MyName As String
Dim MyLawyer As String
Dim strMyLocal As String
Dim strMyInitials As String

strMyName = YourName.Value
strMyLawyer = Lawyer.Value
strMyInitials = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyName,
Key:="Initials")
strMyLawyerDirectPhone = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyLawyer,
Key:="DirectPhone")
strMyLawyerEmail = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyLawyer,
Key:="Email")
strMyLawyerDirectFax = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyLawyer,
Key:="DirectFax")
strMyLawyerInitials = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyLawyer,
Key:="Initials")
strMyLawyerName = System.PrivateProfileString
(FileName:="h:\blginfo.ini", Section:=strMyLawyer,
Key:="Name")


ActiveDocument.Bookmarks("DirectPhone").Select
Selection.TypeText strMyLawyerDirectPhone
ActiveDocument.Bookmarks("SecretaryInitials").Select
Selection.TypeText strMyInitials
ActiveDocument.Bookmarks("Email").Select
Selection.TypeText strMyLawyerEmail
ActiveDocument.Bookmarks("DirectFax").Select
Selection.TypeText strMyLawyerDirectFax
ActiveDocument.Bookmarks("Name").Select
Selection.TypeText strMyLawyerName
ActiveDocument.Bookmarks("Name2").Select
Selection.TypeText strMyLawyerName
ActiveDocument.Bookmarks("LawyerInitials").Select
Selection.TypeText strMyLawyerInitials

Unload Setup

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub UserForm_Initialize()
Lawyer.AddItem "RLB"
Lawyer.AddItem "DLS"
Lawyer.AddItem "BJF"

YourName.AddItem "Kellyr"
YourName.AddItem "Fazals"

End Sub

 

[Shauna Koppang]

Sorry in haste pasted in the wrong macro. Here is the
correct one - Busy morning!!

Sub InsertAddressFromOutlook()
'Macro created by Shauna Koppang May 27, 2004
'Modifed by JGM Newsgroups

Dim strCode As String
Dim strAddress As String
Dim strAddressS As String
Dim iDoubleCR As Integer
Dim FullDetails As String
Dim SplitDetails As Variant

'Set up the formatting codes in strAddress

strCode = strCode & "<PR_COMPANY_NAME>" & vbVerticalTab
strCode = strCode & "<PR_STREET_ADDRESS>" &
vbVerticalTab
strCode = strCode & "<PR_LOCALITY>,
<PR_STATE_OR_PROVINCE>" & vbVerticalTab
strCode = strCode & "<PR_COUNTRY> <PR_POSTAL_CODE>" &
vbVerticalTab & vbVerticalTab
strCode = strCode & "Attention: " & vbTab
& "<PR_DISPLAY_NAME>" & vbVerticalTab
strCode = strCode & vbTab & vbTab & "<PR_TITLE>"

strCode = strCode & "__/__" & "<PR_GIVEN_NAME>"

'Let the user choose the name in Outlook - Fix this
FullDetails = Application.GetAddress("", strCode,
False, 1, , , True, True)

SplitDetails = Split(FullDetails, "__/__")
strAddress = SplitDetails(0)
strAddressS = SplitDetails(1)

Selection.TypeText strAddress

'Makes the attention block bold
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveUp Unit:=wdLine, Count:=1,
Extend:=wdExtend
Selection.Font.Bold = wdToggle
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.EndKey Unit:=wdLine
Selection.NextField.Select

'Moves and selects salutation placeholder and puts in
GIVEN_NAME
'ActiveDocument.Bookmarks("Salutation").Select
Selection.TypeText strAddressS

'Eliminate blank lines by looking for two carriage
returns in a row
iDoubleCR = InStr(strAddress, vbCr & vbCr)
While iDoubleCR <> 0
strAddress = Left(strAddress, iDoubleCR - 1) & Mid
(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Wend

Selection.NextField.Select

End Sub

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Shauna Koppang > écrivait :
In this message, < Shauna Koppang > wrote:

|| Hi,
||
|| The following macro works perfectly with only one thing
|| that needs to be changed and I am not sure how to do it.
|| It has to do with the Address in Outlook. If I have an
|| address that has been entered into Outlook on multi
|| lines, when it comes into Word using this or even the
|| AddressLayout AutoText entry, it puts in a hard return in
|| the address, not a line break which it does for a single
|| line address. Is there a way in this macro to have it
|| check the address that is brought in and change any hard
|| returns it finds into line breaks? The reason is I have
|| another macro that picks up the address block as a single
|| item (no hard returns) then places it on an envelope.
|| This is the last thing to finish on this project and I
|| need to finish it today if possible. THANKS SO MUCH!!!
|| Shauna
||

Try with

strAddress = Replace(strAddress, Chr(13), Chr(11))

just before the line:

Selection.TypeText strAddress


[ Chr(13) = ¶ and Chr(11) = vbVerticalTab ]

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Shauna]

ThankS! Tried it but it did not remove it. Here is the
code now with the change:
Sub InsertAddressFromOutlook()
'Macro created by Shauna Koppang May 27, 2004
'Modifed by JGM Newsgroups

Dim strCode As String
Dim strAddress As String
Dim strAddressS As String
Dim iDoubleCR As Integer
Dim iCR As Integer
Dim FullDetails As String
Dim SplitDetails As Variant

'Set up the formatting codes in strAddress

strCode = strCode & "<PR_COMPANY_NAME>" & vbVerticalTab
strCode = strCode & "<PR_STREET_ADDRESS>" &
vbVerticalTab
strCode = strCode & "<PR_LOCALITY>,
<PR_STATE_OR_PROVINCE>" & vbVerticalTab
strCode = strCode & "<PR_COUNTRY> <PR_POSTAL_CODE>" &
vbVerticalTab & vbVerticalTab
strCode = strCode & "Attention: " & vbTab
& "<PR_DISPLAY_NAME>" & vbVerticalTab
strCode = strCode & vbTab & vbTab & "<PR_TITLE>"

strCode = strCode & "__/__" & "<PR_GIVEN_NAME>"

'Let the user choose the name in Outlook - Fix this
FullDetails = Application.GetAddress("", strCode,
False, 1, , , True, True)

SplitDetails = Split(FullDetails, "__/__")
strAddress = SplitDetails(0)
strAddressS = SplitDetails(1)

Selection.TypeText strAddress

'Makes the attention block bold
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveUp Unit:=wdLine, Count:=1,
Extend:=wdExtend
Selection.Font.Bold = wdToggle
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.EndKey Unit:=wdLine
Selection.NextField.Select

'Moves and selects salutation placeholder and puts in
GIVEN_NAME
'ActiveDocument.Bookmarks("Salutation").Select

' Remove the Return and replace with line break
strAddress = Replace(strAddress, Chr(13), Chr(11))

Selection.TypeText strAddressS

'Eliminate blank lines by looking for two carriage
returns in a row
iDoubleCR = InStr(strAddress, vbCr & vbCr)

While iDoubleCR <> 0
strAddress = Left(strAddress, iDoubleCR - 1) & Mid
(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Wend

Selection.NextField.Select

End Sub

-----Original Message-----
Bonjour,

Dans son message, < Shauna Koppang > écrivait :
In this message, < Shauna Koppang > wrote:

|| Hi,
||
|| The following macro works perfectly with only one thing
|| that needs to be changed and I am not sure how to do it.
|| It has to do with the Address in Outlook. If I have an
|| address that has been entered into Outlook on multi
|| lines, when it comes into Word using this or even the
|| AddressLayout AutoText entry, it puts in a hard return in
|| the address, not a line break which it does for a single
|| line address. Is there a way in this macro to have it
|| check the address that is brought in and change any hard
|| returns it finds into line breaks? The reason is I have
|| another macro that picks up the address block as a single
|| item (no hard returns) then places it on an envelope.
|| This is the last thing to finish on this project and I
|| need to finish it today if possible. THANKS SO MUCH!!!
|| Shauna
||

Try with

strAddress = Replace(strAddress, Chr(13), Chr(11))

just before the line:

Selection.TypeText strAddress


[ Chr(13) = ¶ and Chr(11) = vbVerticalTab ]

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org



.

 

[Shauna Koppang]

Hi Guy!

Moved it to the right location and it works! Sorry, am
rattled trying to get this finished to the customers
specs. I have REALLY appreciated you help!!!

Thanks again!!!!

Shauna

 

[Shauna Koppang]

Jean-Guy,

The macro we have been discussing inserts a similar
format from outlook and bolds the attention/title line.
This MACROBUTTON placeholder is in the left most cell of
a 2 cell table. The blank right cell is for the user to
optinally insert an additional recipient, which they
would use the Insert Address tool to do. However, I have
been able to successfully bold the attention line in the
macro, I am unable to bold it and ensure the tab is set
correctly in the AddressLayout AutoText I have below. I
read somewhere about a switch and I tried what I
remembered and it did not work. Any suggestions! This
is their first change request on the deployment of the
first templates.

{<PR_COMPANY_NAME>
}{<PR_STREET_ADDRESS>
}{<PR_LOCALITY>}, {<PR_STATE_OR_PROVINCE>
}{<PR_COUNTRY>} {<PR_POSTAL_CODE>}

Attention: {<PR_DISPLAY_NAME>}
{<PR_TITLE>}
Thanks so much!

Shauna

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Shauna Koppang > écrivait :
In this message, < Shauna Koppang > wrote:

| Jean-Guy,
|
| The macro we have been discussing inserts a similar
| format from outlook and bolds the attention/title line.
| This MACROBUTTON placeholder is in the left most cell of
| a 2 cell table. The blank right cell is for the user to
| optinally insert an additional recipient, which they
| would use the Insert Address tool to do. However, I have
| been able to successfully bold the attention line in the
| macro, I am unable to bold it and ensure the tab is set
| correctly in the AddressLayout AutoText I have below. I
| read somewhere about a switch and I tried what I
| remembered and it did not work. Any suggestions! This
| is their first change request on the deployment of the
| first templates.

Sorry. I don't get the problem...

If you can bold one part... why not bold a second one?
What do you mean by
"(...)the tab is set correctly in the AddressLayout AutoText(...)" ?
What is the requirement that determines if the tab is set correctly? What
tab for that matter?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Shauna]

Good Morning Jean-Guy,

I'm sorry I guess I was not clear. The AddressLayout
AutoText entry is a special type of entry in Word that
when you click the Insert Address tool (can be put on a
custom toolbar) that is reads in the specified Outlook
Contact fields into the desired layout. The layout I put
in this message is the clients desired format and is
similar to the macro you assisted me with.

In the macro, and more specifically, through the macro I
can bold the Attention line and insert 2 tabs the align
the TITLE under the DISPLAY_NAME. This works.

However, when you create and AddressLayout in the same
format, bold the Attention Line and Title the
AddressLayout does not hold the bold formatting when you
click the Insert Address Tool. I recheck it this morning
and I now see what was happening this the Tabs so that is
ok, it is just the bolding that will not stay.

Even when I insert the AddressLayout into the document it
appears to be formatted with the bolding correctly but
will not bold the Attention Line and title when a contact
is inserted. I think I read somewhere about a switch
that would assist but I cannot locate that information
anywhere.

I hope that makes more sense Thanks!!!!!!

Shauna
(e-mail address removed)

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Shauna > écrivait :
In this message, < Shauna > wrote:

| Good Morning Jean-Guy,
|
| I'm sorry I guess I was not clear. The AddressLayout
| AutoText entry is a special type of entry in Word that
| when you click the Insert Address tool (can be put on a
| custom toolbar) that is reads in the specified Outlook
| Contact fields into the desired layout. The layout I put
| in this message is the clients desired format and is
| similar to the macro you assisted me with.
|
| In the macro, and more specifically, through the macro I
| can bold the Attention line and insert 2 tabs the align
| the TITLE under the DISPLAY_NAME. This works.
|
| However, when you create and AddressLayout in the same
| format, bold the Attention Line and Title the
| AddressLayout does not hold the bold formatting when you
| click the Insert Address Tool. I recheck it this morning
| and I now see what was happening this the Tabs so that is
| ok, it is just the bolding that will not stay.
|
| Even when I insert the AddressLayout into the document it
| appears to be formatted with the bolding correctly but
| will not bold the Attention Line and title when a contact
| is inserted. I think I read somewhere about a switch
| that would assist but I cannot locate that information
| anywhere.
|
| I hope that makes more sense Thanks!!!!!!
|

OK, so the client want the Insert Address button to behave in the same way
your macro does?
Is that it?
Your macro already allows the user to choose an address in Outlook (exactly
like the built-in Word "InsertAddress" button does), so why not tell the
user to use your macro instead? Just create a button that is identical to
the one from Word (you can copy the button image) and let that button you
create call your macro. Why can't that work?

I cannot help you with the "switch" you speak of.... I have never heard of
it and I cannot find the AutoText entry called AddressLayout. But I know
that autotext entries by themselves do not have switches and {AUTOTEXT}
fields only have the usual \* switch for formatting, which cannot let you
specify that you only want the first 2 lines of the autotext entry bold.
If you can insert that autotext entry called AddressLayout, then you should
be able to modify it and recreate it after you modify it...

Sorry I cannot be of more help.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Shauna]

Hi Jean-Guy,

Here is the link for AddressLayouts:
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;212345

I think you are right, either add the formatting
manually, or create a macrobutton tool which would allow
for more consistency. I am still sure I read somewhere
that there was some sort of switch that went something
lik {<PR_DISPLAY_NAME> *\ CHARFORMAT} type switch but I
just can't find it now and what I have tried just does
not work. THERE MUST BE A WAY for those not using Macros
to do this.

If you do come across it when talking to Microsoft,
suggest they post that in the Knowledgebase as I am sure
others would be interested in it as well.

AND please let me know at (e-mail address removed)

Again, I truly appreciate you help in this.

Shauna

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Shauna > écrivait :
In this message, < Shauna > wrote:

| Hi Jean-Guy,
|
| Here is the link for AddressLayouts:
| http://support.microsoft.com/default.aspx?scid=kb;EN-
| US;212345

Got it.
Never used it before, it is actually useful, unless you want to bold some of
the text!

|
| I think you are right, either add the formatting
| manually, or create a macrobutton tool which would allow
| for more consistency. I am still sure I read somewhere
| that there was some sort of switch that went something
| lik {<PR_DISPLAY_NAME> *\ CHARFORMAT} type switch but I
| just can't find it now and what I have tried just does
| not work. THERE MUST BE A WAY for those not using Macros
| to do this.

The switch you are referring to is
\* CHARFORMAT
which normally means that the field value will be displayed according to the
formatting of the first character in the field. So, for example, if you
have:
{AUTHOR \* CHARFORMAT}
and that you bold the "A" in AUTHOR, then when the text is displayed, the
whole field result will be bold.

The problem here is that although you use something like
{<PR_GIVEN_NAME>} {<PR_SURNAME>}
{<PR_COMPANY_NAME>}
{<PR_STREET_ADDRESS>}
{<PR_LOCALITY>}, {<PR_STATE_OR_PROVINCE>} {<PR_POSTAL_CODE>}

to create the autotext, the curly braces here are not actually acting as
real field braces.... so you cannot use any switches.

When you use the InsertAddress button, Word looks up the AddressLayout
autotext (if it exists, otherwise it uses a default layout) and uses it as a
template to insert text. The inserted text is not however made up of fields.
Word does something with the autotext after extracting the information from
the address book, something similar to your macro I bet. Strangely enough it
respects the layout of the autotext entry, but not its character formatting.

Go figure!

|
| If you do come across it when talking to Microsoft,
| suggest they post that in the Knowledgebase as I am sure
| others would be interested in it as well.

I do not talk to Microsoft!
I could pass along some info/request more directly than non-MVP's... But
there are absolutely no guarantees! MVP's do not work for Microsoft, most of
us are just regular users who like to help out fellow computer users....

Good luck!

| AND please let me know at (e-mail address removed)
|
| Again, I truly appreciate you help in this.
|
| Shauna

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Shauna]

Great digging Jean-Guy!

Thanks so much for clearing that up. A good thing for
Microsoft to update in their product though. I have
already created a macro button and will proceed with
updating there training materials to let them know if
they have multiple addresses and want to put in the
attention line in bold in the 2nd and consecutive
addresses they have to use the tool I have provided.

Thanks again for all you help. I have another project
coming up in which I have to read from Goldmine, the
contact information into Word templates. Any
experience? I will need to use the same macro but I
suspect the way I collect the information from the fields
in Goldmines contacts will be much different. Also
another potential that has Mazimizer. Outlook is so easy.

Could you recommend any place I can find assistance on
this kind of integration?

Thanks!
Shauna
West Vancouver, BC Canada

(e-mail address removed)

 

[Shauna]

Jean-Guy,

I am going to start a new thread with the Goldmine
connection.

Thanks again for your help!

Shauna

 

[Jean-Guy Marcil]

Bonjour,

Dans son message, < Shauna > écrivait :
In this message, < Shauna > wrote:

| Great digging Jean-Guy!
|
| Thanks so much for clearing that up. A good thing for
| Microsoft to update in their product though. I have
| already created a macro button and will proceed with
| updating there training materials to let them know if
| they have multiple addresses and want to put in the
| attention line in bold in the 2nd and consecutive
| addresses they have to use the tool I have provided.
|
| Thanks again for all you help. I have another project
| coming up in which I have to read from Goldmine, the
| contact information into Word templates. Any
| experience? I will need to use the same macro but I
| suspect the way I collect the information from the fields
| in Goldmines contacts will be much different. Also
| another potential that has Mazimizer. Outlook is so easy.
|
| Could you recommend any place I can find assistance on
| this kind of integration?
|

Never heard of Goldmine? until 5 minutes ago!

But, after a bit of Googling,
http://www.infacta.com/tutorials.asp?a=nav
I think you will find that it is easy to convert Goldmine? contacts into an
Excel (or Access, or even a txt or csv file), after which point it will be
very easy to use that as a database source. See
http://www.word.mvps.org/faqs/InterDev/index.htm
for a start.

However, I do not think that you will be able to interact directly between
Word and Goldmine? as you can with Word and Outlook. After all, they are
both MS products and were designed to make this type of interaction fairly
easy to manage.
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org