Word/Excell Mail Merge Problem

[BT]

Can anybody help me with a mail merge problem please?



What we do now

When we have a product recall at work, we can run a report from our Sales
Order system, which outputs to a comma-separated text file, a list of
customers together with the Batch Number, Order Number, Description, Date
etc. of their order.



We import the text file into Microsoft Excel and use it as a mail merge
list. The relevant fields are inserted into a Microsoft Word mail-merge
document, printed and sent to the customer.



The Problem

Some customers may have several orders of the same recalled product, often
more than ten. If a customer has more than one order for the product, then
they get a letter for each order e.g. 10 orders = 10 letters telling them
same thing. The only thing that changes is the order number and date. The
customer details, product description and batch number stay the same.



Required Solution

Is there any way to have the mail merge print one letter per customer and
simply list the Order Numbers and Purchase Dates within the same letter?



Office Version

Office XP



I would appreciate any help on this, even to point me in the right
direction. My guess is that you somehow read the contents of the
spreadsheet cells into resizable arrays and somehow print the contents into
the mail merge Word document, adding a new line to the word document for
each Order Number found for a particular customer, and then move on to the
next letter/customer.



The trouble is my VBA isn't that good.



Please send any help to (e-mail address removed)



Thanks in advance for any help you can give me.



P.S. I have sent this email to a few word/excel groups since it involves
both progames - I hope this is all right.

 

[Mark Tangard]

BT,

First, if you must send to multiple groups, do it with *one* post
and place all the groups in the 'To' header separated by commas.
That way all groups will see all responses and people won't waste
time duplicating effort.

The high-minded answer to what you want is to use Access instead
of Excel. If that curdles your blood as much as it does mine,
you can still do what you want in Excel: Add 20 new columns to
the Excel file for the order numbers and dates of the potential
multiple items. Then in the merge document, use { IF } fields
to *conditionally* print -- if the fields are nonblank -- the
those items. It's a bit of work to set up the codes but once
it's done you don't have to think about it. You *do* have to
be sure you've set up enough of these extra column pairs to
handle any usual multiple order.

A typical { IF } field, say for the second multiple item, would
be (assumes your order # fields are named ord1, ord2, etc., and
the dates are orddate1, orddate2, etc):

{ if { mergefield ord2 } <> "" "{ ord2 } dated { orddate2 }" "" }

As you probably know, you insert the field braces with CTRL+F9,
not with the "{" and "}" keys.

 

[Cindy Meister -WordMVP-]

Hi Bt,

Some customers may have several orders of the same recalled product, often
more than ten. If a customer has more than one order for the product, then
they get a letter for each order e.g. 10 orders = 10 letters telling them
same thing. The only thing that changes is the order number and date. The
customer details, product description and batch number stay the same.

Take a look at the short discussion on this topic, with links to sample
files, in the Mail Merge FAQ on my website. There are basically three
approaches you can use.

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

 

[BT]

Thanks for the help - I will try your suggestions
"BT" wrote in message news:[email protected]...