Batch Printing

[athertm]

I have a sheet that contains prices for goods depending on the customer number entered in to a particular cell. This is then printed off and sent to each customer. I would like to know how to get this cell to change automatically and print the document for each customer in turn (the customer numbers are held in a column on the sheet.)

Many thanks.

 

[Anne Troy]

There are several options:

Make a mail merge to Word provide the automation for you:
http://www.theofficeexperts.com/word.htm#MailMerge

Use VBA to automate right from Excel, but there's a few questions...

You don't want all your customer's data going to every customer, right? So the values for each customer would have to be copied to a new sheet, and the links broken.

Do you want to send one worksheet, or the entire workbook?

Do you want to pick from your contact list, store the email address with your customer names, or just type an address into a box that pops up?

 

[Jack in the UK]

Hi

I feel you want to run a program within Excel and then get a file / excel document for each customer IE independant.

MAIL MERGE Will never do thi sby its very difinition and also mail merges are not so good if there are many differen letters / documnets to produce, sounds like you have variables ie customer A will be differed products from customer B, Word will fall flat on its face on this one unless you really VAB her heavy heavy.

Excel however could be made to do this from a few cood VBA scripts linking your array table to the leter document, this will be in each document so no wat each customer see others details, Un Like word which is 100 letters one folled by the next and so on. this would give 100 docunents in Excel.

Advantages does as you want and also you can find them, mail merges are dreadful to find a letter if you have 500 a day and save the word doc for 3 years you have to find the doc then teh letter from ??? I dont know.

If you name the Exel doc uniquley then you can search for say Jack and all my docs pop up and look for teh date and BINGO!!

Anne Troy has gone for Mail Merge and Jack says stay in Excel self contain it. Anne offers good ideas and as such i would chase these and then decide what way to go, as im a bit mix from the above post whats best

Just showing the difference between Excel documents and mail merge both have their place

Jack

 

[Yogi Anand]

Hi athertm:

Following up on the valuable suggestions from Dreamboat and Jack in UK, I present one common Excel solution through the following simulation ...

y021102h1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Jack1	2					CustID	CustIndex	Name	Company	Place
2	JackinUK						MrExcel	1	MrExcel	MrExcelConsulting	Ohio
3	JackConsulting						Jack1	2	JackinUK	JackConsulting	London
4	London						Dream1	3	Ann	WordExpert	PA
5
6	Informationfor						Informationtotheleftiscustomizedforeachcustomerbyentering
7							only1pieceofinformationincellA1
8	JackinUKofJackConsulting						thenalltheinformationisreadfromtheCustomerdatabaseabove
9
10
11
12
13
14
Sheet6

</SPAN>

I hope this helps.

Regards!
Yogi Anand

 

[Jack in the UK]

Nice one Yogi!!!

This wll work without the problems and could if only a few save off to documents as needed

Poss to hide data in another sheet or have the print area as the part you want to post

Nice!!

 

[athertm]

Thanks for the responses everyone!

I've got to admit, it sounds very complicated. I thought it would be easy as maybe running some script that says.

1) put cell a2 (this is the 1st customer in the list) in to cell c7 (this is the cell that the lookups use to change prices/poducts etc)

2) Update/Print the sheet

3) add 1 to the row number for the customer list ie. a2 + 1 (don't laugh the last time i did any coding I was in shorts)

4) Goto no. 1

Over Simple?

I'm back in work tomorrow so maybe I can post the sheet.

Thanks again everyone!