Excel 2000: Request Suggestions for Mass Mailing

[goopit]

Hello,

The company needs to send a letter to about 600 of our accounts, advising of new case UPC numbers. I welcome any suggestions to make this as trouble-free as possible.

All the account numbers, names, addresses, etc. are on one worksheet. Another worksheet contains the list of account numbers, product names they purchase and the case UPC numbers associated with them.

I thought that perhaps by composing the letter itself on a third worksheet and using the INDIRECT function to address each letter, there would be a way to show in the body of the letter, which products each account purchases and their associated case UPC numbers by referencing back to the account number. Trouble is, the number of products each accounts buys varies. Some buy one, others twenty.

Is there anything I can build in the body of the letter that will show only those products bought by each individual account? Thanks for any help!

goopit

 

[SkipVought]

Hi,

Unless you want to write som VBA code, I'd suggest using MS Word to have your new UPC codes and Mail Merge, linking to your workbook to get the name address etc.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[goopit]

Skip,

I apologize, but I don't understand how Mail Merge will understand to pull only those products that, let's say, account 431340 buys.

How much VBA coding are you talking about, anyway?

Thanks,

goopit

 

[SkipVought]

Sorry, I did not read your post closely enough.

You can use Excel for a mail merge. Check Mail Merge in Excel faq68-4223.

You will need to include some other functionality using MS Query.

Here's the drill.

1) Make a list of unique account numbers (using Advanced Filter - Unique values - copy to another location.

2) use that list as the ListFillRange to drive the process -- ie each acct # is a separate document. Use the For...Next process to put the acct # in a cell that the NEXT process will use to query your data.

3) On the sheet that will be your customer document, insert a query via Data/Get External Data/New database query/Excel Files -- YOUR worbook -- The Sheet containing YOUR TABLE havein Acct # & UPC CODES, [next],[next],[next] -- Select the EDIT option and [finish]

4) in the query editor bring the UPC code into the grid and any other data you want to include. ADD A CRITERIA and in the VALUE, enter [?] This will prompt you for a value.

5) Edit/Return data to Excel

6) before OK to finish, select the Parameter option and in that window select the THIRD option to use the value in a cell AND check the box to RUN THE QUERY each time the cell value changes.

Now when the VBA program sticks a value in that cell, the query will fire and return the corresponding recordset to the sheet. VOLA!

3)

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]