mail merge and userforms in one template?

[ben]

Hi there,

Can I make a mail merge fax template that also contains a
macro to bring up the userform, so I can fill in the rest
of the infomration like job number etc? I tried it with
the simple userform tutorial on the mvp's web site, but
wasn't too successful. I am also fairly new to this part
of word.

The idea is the following:
1. User open database program (Time and Choas)
2. User looks for contact that the fax/letter/transmittal
etc is going to be sent.
3. User merges the info from the database into the word
template (So far it works just fine)
4. Word prompts user to fill out the missing sections of
the form like job number, job name, number of pages etcpp
5. User fills in all info and clicks ok and then user
might be able to make certain changes to the template
(like writing the message on the fax etc.)

We are using Word 2000 and 2002 on Windows 2000 and XP.

Please help! Thanks in advance!

Ben

 

[Cindy Meister -WordMVP-]

Hi Ben,

I'm having some problems following the steps, because I
don't think we're understanding "template" the same way. For
me, "template" is a *.dot file; the user uses File/New to
create a new document from it, and works with that. The user
never does anything with the template.

It's also not clear to me whether the user is actually
executing a merge to a new document? If that is what is
meant under (3), then...

Unlike earlier versions, Word 2000/2002 do base the merge
result document on the actual main merge document template.
However, the "link" back to the template isn't really
complete, so macros in the template tend not to work
correctly.

What I usually do is:
- create a macro in the template
- attach it to a toolbar button I put in the mail merge
toolbar
- the user clicks this instead of the built-in buttons to
execute the mail merge
- the macro executes the mail merge, makes sure the newly
created document is reattached to the template. And it can
also display the user form

Can I make a mail merge fax template that also contains a
macro to bring up the userform, so I can fill in the rest
of the infomration like job number etc? I tried it with
the simple userform tutorial on the mvp's web site, but
wasn't too successful. I am also fairly new to this part
of word.

The idea is the following:
1. User open database program (Time and Choas)
2. User looks for contact that the fax/letter/transmittal
etc is going to be sent.
3. User merges the info from the database into the word
template (So far it works just fine)
4. Word prompts user to fill out the missing sections of
the form like job number, job name, number of pages etcpp
5. User fills in all info and clicks ok and then user
might be able to make certain changes to the template
(like writing the message on the fax etc.)

We are using Word 2000 and 2002 on Windows 2000 and XP.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[ben]

Okay, I try that so I am also not sure what I need to do
exactly. I mispelled my problem earlier. What's happening
is that when the mail merge happens from the other program
(the database) it opens the word template and also create
a new document that contains the merged information. So
you are right the user is not tempering with the template,
he /she is modifying the newly created document. The neat
part is if I use a fillin command (in the toolbar: word
field/then fill in) then the new document prompts me to
fill in a certain filed like job number. if I click ok and
I have another fillin that will pop up as well till I get
to all the fill in commands. However I like to consolidate
all that in a userform that could also check off some
check boxes, so that you have the entire form in front of
you, fill it out, press okay and everything is filled out.
What I try to accomplish is to save the time and effort to
copy paste name, fax number and addresses from the
database into the userform. If I could skip that step by
mail merging and use the userform that would be great. But
I haven't been able to actually make that thing pop up
like the fill in command. Thanks for all your help.

Ben

 

[ben]

Dear Cindy,

I tried a couple of things, but I am not able to merge and
then have the userform pop up. I guess I need some more
instructions or an article on this subject or a book that
covers these things. I really thank you in advance for an
answer.

Ben

 

[Peter Jamieson]

Just in case Cindy's not around....

I think Cindy already gave you most of the clues you need to do what you
want:

What I usually do is:
- create a macro in the template
- attach it to a toolbar button I put in the mail merge
toolbar
- the user clicks this instead of the built-in buttons to
execute the mail merge
- the macro executes the mail merge, makes sure the newly
created document is reattached to the template. And it can
also display the user form

If you need general help with the creation of macros, userforms, and
attaching them to toolbars etc. I suggest you have a good look around at the
http://www.mvps.org/word site.

As for a macro that could do the merge then display a form, the minimum you
would need is something like:

Sub domergeandform()
Dim mmmd As Document
Dim mmod As Document

' do the merge using the active document
Set mmmd = ActiveDocument
With mmmd.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

' at this point the output document should be the ActiveDocument
' make a reference to it
Set mmod = ActiveDocument

' Show your user form. Let's assume there is a text box called
' tbMyTextBox in it, and a command button that, when clicked,
' hides the UserForm.

frmMyForm.Show

' by this point, the user has clicked the command button.

' Now it is up to you to determine where you want to put the data
' entered by the user. That could be tricky because the merge operation
' leaves no bookmarks in the destination document.
' The data is simply in the properties of the
' form. So, for example, to set the Subject property of the output document,
' you can use

mmod.BuiltInDocumentProperties("Subject") = frmMyForm.tbMyTextBox.Value

' clean up

Set mmod = Nothing
Set mmmd = Nothing

End Sub