VBA Template Form

[mysteryg45]

I need to create a letter template for work that pulls information
from the Outlook address book. I don't know much about VBA, but I was
able to get enough information to pull this together:

' *** Code Begin

Option Explicit

Private Sub Document_New()

InsertAddressFromOutlook

End Sub

Private Sub InsertAddressFromOutlook()
Dim strCode As String
Dim strAddress As String
Dim fldMyField As Field
' Declare an array to hold the field code.
' Arrays index from 0, so to fill three
' fields, we declare...
Dim astrCodes(7) As String

' Populate the array of address codes
astrCodes(0) = ""
astrCodes(1) = "<PR_GIVEN_NAME>"
astrCodes(2) = "<PR_SURNAME>"
astrCodes(3) = "<PR_POSTAL_ADDRESS>"
astrCodes(4) = "" 'Subject
astrCodes(5) = "<PR_GIVEN_NAME>"
astrCodes(6) = "" 'Initials
astrCodes(7) = "" 'File name and path

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book. Since this is the first
'call to GetAddress, DisplaySelectDialog = 1.
strAddress = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'For each field, use the matching value from astrCodes
'to get the correct address value. Note that in these
'calls to GetAddress, DisplaySelectDialog = 2. This
'tells Word to use the address that was selected previously.
For Each fldMyField In ActiveDocument.Fields
strAddress = ""
strCode = astrCodes(fldMyField.Index - 1)
' If the array element is a zero-length string
' then skip that field
If strCode <> "" Then
strAddress = Application.GetAddress
(AddressProperties:=strCode, UseAutoText:=False,
DisplaySelectDialog:=2)
fldMyField.Result.Text = strAddress
End If
Next fldMyField

' Clean up memory
strCode = ""
strAddress = ""
Set fldMyField = Nothing

End Sub

' *** Code End

Now, I want to change the template so it has a header on all pages
after the first, which also pulls from the address book, but I can't
figure out how to make it work. Adding more astrCodes doesn't seem to
do anything.

Any suggestions would be much appreciated.

 

[Jean-Guy Marcil]

I need to create a letter template for work that pulls information
from the Outlook address book. I don't know much about VBA, but I was
able to get enough information to pull this together:

' *** Code Begin

Option Explicit

Private Sub Document_New()

InsertAddressFromOutlook

End Sub

Private Sub InsertAddressFromOutlook()
Dim strCode As String
Dim strAddress As String
Dim fldMyField As Field
' Declare an array to hold the field code.
' Arrays index from 0, so to fill three
' fields, we declare...
Dim astrCodes(7) As String

' Populate the array of address codes
astrCodes(0) = ""
astrCodes(1) = "<PR_GIVEN_NAME>"
astrCodes(2) = "<PR_SURNAME>"
astrCodes(3) = "<PR_POSTAL_ADDRESS>"
astrCodes(4) = "" 'Subject
astrCodes(5) = "<PR_GIVEN_NAME>"
astrCodes(6) = "" 'Initials
astrCodes(7) = "" 'File name and path

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book. Since this is the first
'call to GetAddress, DisplaySelectDialog = 1.
strAddress = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'For each field, use the matching value from astrCodes
'to get the correct address value. Note that in these
'calls to GetAddress, DisplaySelectDialog = 2. This
'tells Word to use the address that was selected previously.
For Each fldMyField In ActiveDocument.Fields
strAddress = ""
strCode = astrCodes(fldMyField.Index - 1)
' If the array element is a zero-length string
' then skip that field
If strCode <> "" Then
strAddress = Application.GetAddress
(AddressProperties:=strCode, UseAutoText:=False,
DisplaySelectDialog:=2)
fldMyField.Result.Text = strAddress
End If
Next fldMyField

' Clean up memory
strCode = ""
strAddress = ""
Set fldMyField = Nothing

End Sub

' *** Code End

Now, I want to change the template so it has a header on all pages
after the first, which also pulls from the address book, but I can't
figure out how to make it work. Adding more astrCodes doesn't seem to
do anything.

Any suggestions would be much appreciated.

it is not entirely clear exactly what it is you want to do now.

What will the exact content of that 2nd page header be?
How does it relate to the information you get with your code above?

 

[mysteryg45]

it is not entirely clear exactly what it is you want to do now.

What will the exact content of that 2nd page header be?
How does it relate to the information you get with your code above?

Just in case it's not entirely clear, this is how the above code
works: When the user opens a new doc from this template, they are
prompted to choose a name from the Outlook address book. The person's
name and address are then applied to different parts of the document
(a letter). As long as I stay within the normal body of the page, the
above code works fine.

Now I've discovered I need to add information, including the
addressee's name, to a header on all pages after the first. But when
I try to apply this code to the document's header, it doesn't do
anything. It just ignores the blank macro.

Is there something specific I have to add to make the code work in the
header/footer of the document? Also, will it matter that I've split
the document into sections to distinguish the first page header from
subsequent pages'?

Is there a better way to accomplish what I'm trying to do?

Thanks again!

 

[Doug Robbins - Word MVP]

The following will insert the selected name and address of the selected
contact from Outlook at the location of the selection and the name of the
selected contact in the primary header of the first Section of the document,
setting the Different First Page attribute of the Page Setup for that
Section to true so that the addressee's name only appears in the header of
the second and subsequent pages.

Dim Addressee As String
Selection.Text = Application.GetAddress(UseAutoText:=False, _
DisplaySelectDialog:=1, RecentAddressesChoice:=True, _
UpdateRecentAddresses:=True)
Addressee = Selection.Paragraphs(1).Range.Text
With ActiveDocument.Sections(1)
.PageSetup.DifferentFirstPageHeaderFooter = True
.Headers(wdHeaderFooterPrimary).Range.Text = Addressee
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[mysteryg45]

Thanks Doug. I feel like I'm getting on the right track -- It does
get the addressee's name in the header of page 2 and beyond. However,
it adds a couple of other problems.

First, it adds a copy of the addressee's name and address to the top
of the letter, which would be fine except that I don't know how to
move it around to the place I would need it to be, and the code I
already has it in the necessary spot.

Second, it overwrites all the other information I had in the header
that didn't need to be accessed by VBA to work in the previous version
of the template (meaning it would be just a blank macro in the old
version...information like page number and date).

To help clarify, and maybe make it easier for someone to help, I've
posted a copy of the actual template (it will be up for 7 days):
http://www.yousendit.com/download/TTZuc0wycWY1UjQwTVE9PQ

And here's the VBA code with Doug's suggestion included:

' *** Code Begin

Option Explicit


Private Sub Document_New()

InsertAddressFromOutlook

End Sub


Private Sub InsertAddressFromOutlook()
Dim strCode As String
Dim strAddress As String
Dim fldMyField As Field
' Declare an array to hold the field code.
' Arrays index from 0, so to fill three
' fields, we declare...
Dim astrCodes(6) As String


' Populate the array of address codes
astrCodes(0) = ""
astrCodes(1) = "<PR_GIVEN_NAME>"
astrCodes(2) = "<PR_SURNAME>"
astrCodes(3) = "<PR_POSTAL_ADDRESS>"
astrCodes(4) = "" 'Subject
astrCodes(5) = "<PR_GIVEN_NAME>"
astrCodes(6) = "" 'Initials


'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book. Since this is the first
'call to GetAddress, DisplaySelectDialog = 1.
strAddress = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub


'For each field, use the matching value from astrCodes
'to get the correct address value. Note that in these
'calls to GetAddress, DisplaySelectDialog = 2. This
'tells Word to use the address that was selected previously.
For Each fldMyField In ActiveDocument.Fields
strAddress = ""
strCode = astrCodes(fldMyField.Index - 1)
' If the array element is a zero-length string
' then skip that field
If strCode <> "" Then
strAddress = Application.GetAddress
(AddressProperties:=strCode, UseAutoText:=False,
DisplaySelectDialog:=2)
fldMyField.Result.Text = strAddress
End If
Next fldMyField

Dim Addressee As String
Selection.Text = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
Addressee = Selection.Paragraphs(1).Range.Text
With ActiveDocument.Sections(1)
.PageSetup.DifferentFirstPageHeaderFooter = True
.Headers(wdHeaderFooterPrimary).Range.Text = Addressee
End With

' Clean up memory
strCode = ""
strAddress = ""
Set fldMyField = Nothing

Is there another addition I can make? Or am I just going about the
whole project incorrectly?

Thanks in advance for any help!

 

[Doug Robbins - Word MVP]

Insert a bookmark with the name "Address" at the location where you want the
address to appear in the document and insert a { DOCVARIABLE Addressee }
field in the header at the location where you want the addressee's name to
appear.

Then use the following code

Dim Addressee As String
With ActiveDocument
.Bookmarks("Address").Range.Text =
Application.GetAddress(UseAutoText:=False, _
DisplaySelectDialog:=1, RecentAddressesChoice:=True, _
UpdateRecentAddresses:=True)
.Variables("Addressee").Value =
..Bookmarks("Address").Range.Paragraphs(1).Range.Text
.Sections(1).Headers(wdHeaderFooterPrimary).Range.Fields.Update
End With

I would suggest that you investigate using a userform.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and for more advance information, see the following page of fellow MVP Greg
Maxey's website:

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm





--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Thanks Doug. I feel like I'm getting on the right track -- It does
get the addressee's name in the header of page 2 and beyond. However,
it adds a couple of other problems.

First, it adds a copy of the addressee's name and address to the top
of the letter, which would be fine except that I don't know how to
move it around to the place I would need it to be, and the code I
already has it in the necessary spot.

Second, it overwrites all the other information I had in the header
that didn't need to be accessed by VBA to work in the previous version
of the template (meaning it would be just a blank macro in the old
version...information like page number and date).

To help clarify, and maybe make it easier for someone to help, I've
posted a copy of the actual template (it will be up for 7 days):
http://www.yousendit.com/download/TTZuc0wycWY1UjQwTVE9PQ

And here's the VBA code with Doug's suggestion included:

' *** Code Begin

Option Explicit


Private Sub Document_New()

InsertAddressFromOutlook

End Sub


Private Sub InsertAddressFromOutlook()
Dim strCode As String
Dim strAddress As String
Dim fldMyField As Field
' Declare an array to hold the field code.
' Arrays index from 0, so to fill three
' fields, we declare...
Dim astrCodes(6) As String


' Populate the array of address codes
astrCodes(0) = ""
astrCodes(1) = "<PR_GIVEN_NAME>"
astrCodes(2) = "<PR_SURNAME>"
astrCodes(3) = "<PR_POSTAL_ADDRESS>"
astrCodes(4) = "" 'Subject
astrCodes(5) = "<PR_GIVEN_NAME>"
astrCodes(6) = "" 'Initials


'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book. Since this is the first
'call to GetAddress, DisplaySelectDialog = 1.
strAddress = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub


'For each field, use the matching value from astrCodes
'to get the correct address value. Note that in these
'calls to GetAddress, DisplaySelectDialog = 2. This
'tells Word to use the address that was selected previously.
For Each fldMyField In ActiveDocument.Fields
strAddress = ""
strCode = astrCodes(fldMyField.Index - 1)
' If the array element is a zero-length string
' then skip that field
If strCode <> "" Then
strAddress = Application.GetAddress
(AddressProperties:=strCode, UseAutoText:=False,
DisplaySelectDialog:=2)
fldMyField.Result.Text = strAddress
End If
Next fldMyField

Dim Addressee As String
Selection.Text = Application.GetAddress(UseAutoText:=False,
DisplaySelectDialog:=1, RecentAddressesChoice:=True,
UpdateRecentAddresses:=True)
Addressee = Selection.Paragraphs(1).Range.Text
With ActiveDocument.Sections(1)
.PageSetup.DifferentFirstPageHeaderFooter = True
.Headers(wdHeaderFooterPrimary).Range.Text = Addressee
End With

' Clean up memory
strCode = ""
strAddress = ""
Set fldMyField = Nothing

Is there another addition I can make? Or am I just going about the
whole project incorrectly?

Thanks in advance for any help!

 

[mysteryg45]

Thanks, Doug. I really appreciate the quick responses, and I'm sorry
I'm not getting back to you with equally quick responses.

A big part of my problem right now is that I recently upgraded my
computer and my version of Word from XP to 2007. With the change,
I've lost all ability to be able to save the vba code along with the
template document -- Every time I save and close the template, and
then try to create a new document from the template, all the code
disappears and no address book box pops up.

I've seen other discussion posts recommend that I do a save-as and
save the whole thing to a new template document. Either I'm doing
something wrong (that I wasn't doing with the earlier version of Word)
or that's not working. Can someone give me some insight?

Also, Doug, in response to the user forms... Honestly, it's exactly
what I want to do. But the coding required for it sounds like it goes
WAY over my head. If you can give me something specific, that would
be AMAZING.

Thanks again.

 

[Doug Robbins - Word MVP]

Are you saving the template as a Word Macro Enabled Template (*.dotm )?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Thanks, Doug. I really appreciate the quick responses, and I'm sorry
I'm not getting back to you with equally quick responses.

A big part of my problem right now is that I recently upgraded my
computer and my version of Word from XP to 2007. With the change,
I've lost all ability to be able to save the vba code along with the
template document -- Every time I save and close the template, and
then try to create a new document from the template, all the code
disappears and no address book box pops up.

I've seen other discussion posts recommend that I do a save-as and
save the whole thing to a new template document. Either I'm doing
something wrong (that I wasn't doing with the earlier version of Word)
or that's not working. Can someone give me some insight?

Also, Doug, in response to the user forms... Honestly, it's exactly
what I want to do. But the coding required for it sounds like it goes
WAY over my head. If you can give me something specific, that would
be AMAZING.

Thanks again.

 

[Jean-Guy Marcil]

Are you saving the template as a Word Macro Enabled Template (*.dotm )?

Also, I am not all that familair with 2007 yet, but isn't there the issue of
trusted locations as well?

 

[mysteryg45]

Also, I am not all that familair with 2007 yet, but isn't there the issueof
trusted locations as well?

I'm not sure about the trusted locations thing. What does that mean?

Also, I've tried saving both as a .dotm and as a word 97-2003 .dot,
and neither one has worked.

 

[Doug Robbins - Word MVP]

Are you sure that you have the code in the template that you think you have
it?

Into what folder have you saved the templates?

How are you attempting to make use of the templates?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Also, I am not all that familair with 2007 yet, but isn't there the issue
of
trusted locations as well?

I'm not sure about the trusted locations thing. What does that mean?

Also, I've tried saving both as a .dotm and as a word 97-2003 .dot,
and neither one has worked.

 

[mysteryg45]

If you're asking if I've put the code in the normal template vs. my
template, the answer is no. I've been pretty careful about that.

I've saved the templates into a folder on the office network...could
that cause a problem? I've definitely pointed all computers that will
use the template to search for them in this folder.

I'm not sure if I know what you're asking in the last question,
so...sorry if this isn't the answer you're looking for: It's a letter
template. When the user creates a new document from the template, the
first thing that should happen is the address book dialogue pops up.
The user then picks a name from the Outlook contact list, and the name
and address pop up in all the right spots for a letter (ie. the
addressee's name and address go at the top, his/her first name goes
after "dear," etc.). It's just a way to automate things a bit more.

 

[Graham Mayor]

For the code to work 'out of the box' the template must be stored in a
trusted location and it must be saved either as a macro enabled template
'dotm' or a Word 97-2003 template 'dot' (the default is 'dotx' which will
not store macros).

Unlike previous versions, saving the document as a template does not
automatically save into the user templates folder (which would normally be a
trusted location).

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[mysteryg45]

For the code to work 'out of the box' the template must be stored in a
trusted location and it must be saved either as a macro enabled template
'dotm' or a Word 97-2003 template 'dot' (the default is 'dotx' which will
not store macros).

Unlike previous versions, saving the document as a template does not
automatically save into the user templates folder (which would normally be a
trusted location).

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web sitewww.gmayor.com
Word MVP web sitehttp://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Graham, I've saved the template as both of the types you mention, and
I've saved the template in the location I've set as the network
templates folder (which you say should be trusted).

Just to make sure, to set a folder as an official folder for
templates, under File Locations, you change the "workgroup templates"
folder to the location on the network where you're storing templates,
correct?

 

[Graham Mayor]

Graham, I've saved the template as both of the types you mention, and
I've saved the template in the location I've set as the network
templates folder (which you say should be trusted).

Just to make sure, to set a folder as an official folder for
templates, under File Locations, you change the "workgroup templates"
folder to the location on the network where you're storing templates,
correct?

That sounds correct - http://www.gmayor.com/Template_Locations.htm
However you need to ensure that the folder is flagged in Word 2007 as a
trusted location.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>