Mailmerge, but with a twist... 1

[henio]

Mailmerge, but with a twist...
I want to mailmerge a Word document with data in an Excel file, which is OK, but I want to print a number of copies acording to a value in the Excel file. e.g. the first merged document prints one copy, the next say 3, then whatever. I guess this probably needs VBA, but I can't find any clues how to do this in the helpfiles.

Cheers,
henio

 

[PBAPaul]

Dear Henio

Rather than using VBA in Word to mailmerge, you could use a quick work around in your Excel spreadsheet. Rather than have a value in Excel for how many copies you need printed, simply duplicate the rows of data as necessary.

The type of thing that I mean is as follows:

Record1
Record1
Record2
Record3
Record4
Record4
Record4

This would mean that you could do a standard mail merge operation on your Excel database and you would end up with 2 copies of Record1, 1 copy of Record2, 1 copy of Record3 and 3 copies of Record4.

A simple VBA in Excel would automate the generation of duplicate rows from your copies value.

I hope this work around helps.

Paul

 

[henio]

Paul,

this is a good idea for small volumes (as implied in my posting). Unfortunately the actual application requires 100's of copies:-(

Cheers,
Henio

 

[PBAPaul]

Henio,

What are we talking about in terms of records etc.

How many records? Maximum number of copies?

If you let me know then I can have another think about it. In any case the print run would be very long. Wouldn't it be more effective to xerox copies as required?

Paul

 

[VBAjedi]

If you could "push" the data to Word from Excel (code in Excel opens Word template and uses Word object model to trigger mailmerge) then it would be easy to print off a given number of copies based on the contents of one cell in each row. I suggest exploring the Word object model from Excel. . .

HTH!


VBAjedi

 

[PBAPaul]

Henio,

I have written some Word VBA code below that might help.

First setup your Word mail merge document with all the fields necessary. Ensure that you also include the field that gives the number of copies required - if you don't want to see it printed, just either set the Font as hidden or White ink on white paper.

Put the following code into your Word document ensuring that you alter the figure in the line NumberCopies = so that the Field number is the sequence number of the Field stating the number of copies you want. For example if your copies field is the third field entered into the document, then the statement would be Fields(3).

Sub MergeCopies()
Dim myMerge As Object
Dim NumberCopies As Integer
Dim TotalRecords As Integer
Dim X As Integer

Set myMerge = ActiveDocument.MailMerge

myMerge.ViewMailMergeFieldCodes = False
myMerge.DataSource.ActiveRecord = wdLastRecord
TotalRecords = myMerge.DataSource.ActiveRecord

For X = 1 To TotalRecords
    ActiveDocument.MailMerge.DataSource.ActiveRecord = X
    NumberCopies = ActiveDocument.Fields(FieldNumber).result
    Application.PrintOut Copies:=NumberCopies
Next
End Sub

When you run the macro, the system will be set to show the merge data rather than the field codes. The macro will then run through each record, get the number of copies from your field and then print out the document that number of times.

Play around with it! It does work as I have run it on my system.

Regards

Paul

 

[SkipVought]

HI,

What you can do is build your merge table on the fly using Paul's suggesion of multiple records for multiple copies and driving it from a master list that controls the number of copeis per record.





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[henio]

Paul,

thanks for your code - it worked perfectly

Cheers,
Henio

 

[henio]

Hi all,

just as I had an excellent solution from Paul, it now needs to be refined. To save a few trees, the user now wants to print these documents 2 up. I tried to add the required parameters to the Application.PrintOut Copies:=NumberCopies
statement, but Word seems to either ignore them or print 1 copy per page but in in the correct landscape format. Result - the same number of sheets:-(

What I think I need to do is to send the the correct number of multiple copies to a new mailmerge file and then issue the print command with necessary formating parameters against the new document, probably deleting it after printing. Can anyone shed some light on how to do this? As always any other suggestions are always welcome.

Desperation is setting in - please help.

Cheers,
Henio

 

[PBAPaul]

Henio

Sorry I don't understand what you mean by 2 up.

If you mean 2 copies, then change the code by inserting a line before the Print command
NumberCopies = NumberCopies * 2

If this isn't what you need then please specify what 2 up means.

Paul

 

[henio]

Paul,

I was refering to the pages per sheet and scale to paper size parameters. I recorded a macro to see how to set it up and added the details as described before.

Ignoring mailmerge for the moment... If you try to print a single page, set the number of copies to 4, pages per sheet to 2 and scale to paper size to A4, Word objects violently with various errors. I was hoping to get 4 pages on 2 sheets. If your document has 4 pages, set pages per sheet to 2 and scale to paper size to A4, you get 4 pages printed landscape on 2 sheets - just what I am trying to achieve.
So, Word treats printing 4 pages once differently to printing 1 page with copies = 4.

So, the only way I can see to get around this problem is to send my calculated number of sheets as individual pages to an interim file and then print this with pages per sheet and scaling parameters set in code as per your example.

Hope this helps,
Henio

 

[PBAPaul]

Henio

Thanks for the explanation. Why can't you come up with a simple question?? Just kidding!

I will get my brain into gear and have a think about your question.

Paul