How do I repeat records in a mail merge?

[iainaitken]

Hello I am trying to automate a tedious operation in our office,
certificate printing. Currently we send out certificates produced in
Powerpoint 2000. We manually type in the candidate name, course, and
date for each certificate. I would like to do this with a mail merge.
I have redone our course certificate template in Word 2000 and have all
the data setup in an Excel spreedsheet. My problem is that the first
certificate prints propely but the remainder only have the candidate
names on them. How do I setup the mail merge to repeat the course name
and date for the remainder of my candidate names?

 

[Peter Jamieson]

In essence, Word should output a copy of all the text and fields in the Mail
Merge Main Document, inserting the values for each Merge field (MERGEFIELD,
ADDRESSBLOCK etc.) from each row in the data source.

My guess is that your Excel sheet is set up so that you have, e.g.

First row: Course name and date, and info. for the first candidate
Second row: blank course name and date, and infor for the second candidate
etc.

If so, the simplest thting to do is change your Excel sheet so that is that
all the data for each row. The alternative is, in my view, unnecessarily
complicated although I can suggest how you could do it if you want.

If not, how is your excel data laid out?

Peter Jamieson

 

[iainaitken]

Yes that's basically how I have it setup. I thought about filling in
course name and date for each candidate but I just wanted to see if
there was another way.

 

[Peter Jamieson]

It may be simpler than I assumed.

Let's suppose you have a column in your Excel sheet called Course that is
only filled in on the first row for each course.

In your Mailmerge main document, you could use

{ IF "{ MERGEFIELD Course }" = ""
"" "{ SET myCourse { MERGEFIELD Course }"
}{ REF myCourse }

where all the {} are the special field code braces you can insert using
ctrl-F9, not the ones you can type directly on the keyboard.

Peter Jamieson

 

[iainaitken]

I tried your code and it only prints the first one, the rest are all
blank. I noticed that there was one } missing so what I typed in is
this:

{ IF " { MERGEFIELD Course }" = ""
"" "{ SET myCourse { MERGEFIELD Course }"
}{REF myCourse}}

Do I have to have it broken up as you typed it or should it be all on
one line?

Also does the spacing between {} have to be as you typed it?

 

[Graham Mayor]

{ IF { MERGEFIELD Course } = "" "" "{ SET myCourse { MERGEFIELD
Course }"}{ REF myCourse }

or conversely

{ IF { MERGEFIELD Course } <> "" "{ SET myCourse { MERGEFIELD Course }"}{
REF myCourse }

The line (and it is one line) checks to see if Course has content and if it
does, it adds its content to a bookmark 'MyCourse' and then the REF field
reproduces the content of the bookmark.


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


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

 

[Peter Jamieson]

Graham's corrected my missing brace error.

The multiline layout is simply to prevent news reader software from breaking
up the text in a way that makes it difficult to understand. It's simpler if
it isn't broken up into lines but should work in the multiline format
because all the new lines are outside the result values, i.e. they will only
show up in the document when you are looking at the field codes, not the
field results.

Peter Jamieson

 

[iainaitken]

Wonderful, thank you very much got it to work. There was still a
missing brace but I figured out where to put it:

{ IF " { MERGEFIELD Course }" = ""
"" "{ SET myCourse { MERGEFIELD Course }}"
}{REF myCourse}}

Do you have any websites that give more information and examples of
these codes?

 

[iainaitken]

Ooops something didn't quite copy correctly that should have said:

{ IF { MERGEFIELD Course }= ""
"" "{ SET myCourse { MERGEFIELD Course }}"
}{REF myCourse}

 

[Peter Jamieson]

Word Help is the best starting point for the field codes. For other stuff to
do with field codes there are lots of places to look, but I tend to start by
looking at
a. the Word MVP site at http://word.mvps.org
b. Graham Mayor's site at http://www.gmayor.com (look for Word Tips)
c. Cindy Meister's site at http://homepage.swissonline.ch/cindymeister/site
and you will find lots of material by searching this newsgroup using the
facilities at
http://groups.google.com

Peter Jamieson