Mail Merge Word97 and Excel

[Earl Lewis]

I am trying to figure out how to make Word97(Small Business Edition)
(SR2 installed) do a mail merge from an .xls file into a form letter.
(My intention is to fold the 8 x 11 sheet over in half to form a
mailable envelope. The mailing is to a paltry 40-member club.) This is
the first time I ever attempted a mail merge. The documentation, of
course, sucks.[ But I do think that Microsoft has gotten a LITTLE
better about that since 1997. I'm used to trying to deciper their
compiler help files, so I'm used to it.] Here's what I've got working
so far:
Below I list the 'fields' I've inserted in the 'main document' (I
guess I'd call this a template, but what do I know)


«Name»
«Address»
«City», «St» «zip»«Next Record»


and on the second page of the main document:



«Name»
«Address»
«City», «St» «zip»

I don't understand why I need a 2nd page, but it seems to work better
if I do.
I want to merge into a document, rather than directly to a printer,
naturally. I'd have printed 2 boxes of paper by this time if I didn't
do my experimenting that way.

When I Tools | MailMerge | Merge | Merge, a small window pops up, with
a text box to type something in if I want to. I need to keep clicking
'OK' for the merge to progress. I can't figure out how to keep the
window from popping up, and make it merge automatically. Also, since
this is the first time I've ever tried this, and MicroSoft offers no
examples to emulate, I'm shooting in the dark here.
Can anyone shed any light for me?
Earl

 

[Hilary Ostrov]

I am trying to figure out how to make Word97(Small Business Edition)
(SR2 installed) do a mail merge from an .xls file into a form letter.

Take a look at the following pages which I believe will help you solve
your problems. Word's MailMerge can be quite confusing until you get
used to it! But these pages on the MVP site will help you!

Mail Merge FAQ
http://word.mvps.org/faqs/mailmerge/index.htm

Particularly:

How to create a Mail Merge
http://word.mvps.org/faqs/MailMerge/CreateAMailMerge.htm

and:

Creating a Mail Merge Data Source
http://word.mvps.org/faqs/mailmerge/CreateADataSource.htm

[...]

hro

 

[Earl Lewis]

Thanks Hilary, it does help a little, wish I'd had that info 2 days
earlier when I started this little project.
One more question:
My XLS file lists the members' names last-name-first. I figured that,
once I got the mail merge going, I would put an IF in there and
execute a little VisBasic code, which Word claims it can do. Here's
what I had in mind:

Dim sName, psn, ReversedName, length
SName=«MERGEFIELD Name»
length=Len(sName)
psn=Instr(1,sName,",")
ReversedName=Mid(sName,psn+1,length-psn)
ReversedName=ReversedName+" "+Mid(sName,1,psn-1)
«MERGEFIELD Name»=ReversedName

I tested the code in my VisBasic compiler (except for the MERGEFIELD
stuff), but Word just prints out the 'Dim', and code doesn't execute.

Could you tell me if what I'm attempting is possible, or am I banging
my head against a wall?
Earl

I am trying to figure out how to make Word97(Small Business Edition)
(SR2 installed) do a mail merge from an .xls file into a form letter.

Take a look at the following pages which I believe will help you solve
your problems. Word's MailMerge can be quite confusing until you get
used to it! But these pages on the MVP site will help you!

Mail Merge FAQ
http://word.mvps.org/faqs/mailmerge/index.htm

Particularly:

How to create a Mail Merge
http://word.mvps.org/faqs/MailMerge/CreateAMailMerge.htm

and:

Creating a Mail Merge Data Source
http://word.mvps.org/faqs/mailmerge/CreateADataSource.htm

[...]

hro

 

[Hilary Ostrov]

Thanks Hilary, it does help a little, wish I'd had that info 2 days
earlier when I started this little project.
One more question:
My XLS file lists the members' names last-name-first. I figured that,
once I got the mail merge going, I would put an IF in there and
execute a little VisBasic code, which Word claims it can do. Here's
what I had in mind:

If you want to do it via a VBA macro, I'm sure you can ... but I
suggest you pose that question in microsoft.public.word.vba.beginners.

Another alternative would be to modify your .xls source document. You
can easily split the members' names into two fields by selecting the
data in the column then Data->Text to columns. Check "Delimited" as
your "File type". If the names are currently written as "Smith, John"
then use "Comma" and/or "Space" as your delimiters.

If you use only "Comma" you will need to TRIM the second column. If
you use only "Space" you can do a Search/Replace to delete the commas.
Then, you can either leave as two columns/fields (FirstName and
LastName) for your merge, or you can CONCATENATE to have the names in
a single column/field.

Depending on the number of variations you might have in your original
names, e.g. "Smith, John D." or "de Jong, Samuel", if you use the
"Space" delimiter, you will end up with three columns rather than two,
and will need to manually edit.

So, it's a good idea to insert a few additional columns before you do
the conversion or better still - to avoid overwriting any other data
in the sheet - copy your current column to a new worksheet, do the
conversion there and paste back into the original when you have it the
way you want it!

[...]


hro