mail merge and userforms

[ben]

Hi there,

I need help with mail merge and userforms:

How can I tell word to merge a template and then have a
user form pop up to fill in missing information:

1. I am merging data from a database into a fax or
transmittal or letter template (like the name of the
person, fax number, address etc) I am using one contact
per merge (or several for a multi recipient fax cover
sheet), thus just creating one form per merge.
This part of the process works very well. the database
program opens up the word template and creates a new
document with the merged info. I also inserted fill-ins to
fill out the rest of the data like job number, number of
pages etc. Before the new document is created, the fill-
ins prompts the user to fill in missing info one at
a time.(line job number, then job name then... So far so
good.

2. I would like to consolidate all the fill-ins into one
userform, that allows me to add drop down menus, and check
boxes (let's say there is a check box saying 'urgent', the
userform would fill this into the fax document) as well
as other information and program it to restrict users to
go over 20 characters etc. etc.

How can I make the userform pop up after/while I perform
mail merge just like the fill-ins? I wasn't able to do it
yet, but I guess I need a macro that says something like
show userform1 before creating mail merge? A macro
called 'autonew' and says 'userform1.show' didn't do the
job, that only works without mail merge.

Thank you so much for your help,

Ben

 

[Doug Robbins - Word MVP]

Hi Ben,

Instead of using mailmerge, use code such as the following to populate a
listbox on a userform with the data from the database and have additional
controls on the userform for the other data that you want the user to enter
manually. Then have a command button on the user form that dumps the
information from the record selected in the ListBox plus that entered by the
user into the appropriate places in the document either by the use of
bookmarks or document variables:

Private Sub UserForm_Activate()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[ben]

thanks doug, yes, but the database is not microsoft based
and has an .isd extension, I don't think this will work.
Also the template is on the network linked to the
database, so one can open it from within the database
program. The way you discribe it would be from within word
and the template folder is on the c drive and not on the
network, thus any changes made to the template would have
to be made on every one's machine. Any suggestions?

I guess I could export all of our data periodically into a
word based database (is this part of word or do I need to
get access or their like) and do it that way, but again I
would have to do it for every one's machine and won't be
able to network it without buying the ms exchange server,
right?


Ben

 

[Cindy Meister -WordMVP-]

Hi Ben,

How can I make the userform pop up after/while I perform
mail merge just like the fill-ins?

You neglect to mention WHICH version of Word you have. Very
simply put, you can only do this if you have Word 2002 or
later. No way to "interrupt" Word's mail merge in earlier
versions.

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