Printing multiple copies of a specific records in a mail merge

F

Francisco Buella

Hi, I'm new to mail merges. I have searched the group for an answer,
and have found some similar questions, but I haven't been able to
figure this out.

I have a letter that goes out to clients that I have been able to use
a mail merge for. The problem is that some clients need up to 5
additional copies of the letter to be sent out. It is different for
each client in the data set. I would like to print out all the
letters at once, with the correct number of copies printing for each
client.

Please help!
Thanks
 
D

Doug Robbins - Word MVP on news.microsoft.com

The only way to do this with Mail Merge alone would be to have the necessary
number of copies of the records in the data source.

Another way, if you had a field in the data source that contained the number
of copies that were required for each client, would be to use some Visual
Basic Code to interate through the data source and create the required
number of copies of each letter.

How was your data source created?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
M

macropod

Hi Francisco,

If you're is outputting the print job to a postscript printer, you could use a PRINT field, with an embedded MERGEFIELD linked to a
data field containing the # copies. The PRINT field could be coded as:
{PRINT \p page "/#copies {MERGEFIELD CopyCount} def"}
where 'CopyCount' is the name of the data field holding the # copies to be printed. This field could be inserted anywhere on the
body of the page to be duplicated. A 'CopyCount ' value of 0 prevents the page from printing.

Although each record will only appear once in the merged output, you'll get the required number of copies in the print job. This
approach also has no effect on page numbering or TOCs, as the duplicate pages don't exist in the merged document.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.
 
F

Francisco

I can set up my data source to have the number of copies per record.
It is created through regular data entry, so I can manipulate it to
include the number of copies. I guess the mail merge only solution
would be the simplest? I have no experience with visual basic.

Thanks,
Francisco
 
F

Francisco

I don't think I have a postscript printer, is there any other way to
do it? I did do a google search and came across a "virtual postscript
printer" - would this be an option? I'll look into it a little more,
but in any case thanks for your help!

Thanks
Francisco

Hi Francisco,

If you're is outputting the print job to a postscript printer, you could use a PRINT field, with an embedded MERGEFIELD linked to a
data field containing the # copies. The PRINT field could be coded as:
{PRINT \p page "/#copies {MERGEFIELD CopyCount} def"}
where 'CopyCount' is the name of the data field holding the # copies to be printed. This field could be inserted anywhere on the
body of the page to be duplicated. A 'CopyCount ' value of 0 prevents thepage from printing.

Although each record will only appear once in the merged output, you'll get the required number of copies in the print job. This
approach also has no effect on page numbering or TOCs, as the duplicate pages don't exist in the merged document.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]

Francisco Buella said:
Hi, I'm new to mail merges.  I have searched the group for an answer,
and have found some similar questions, but I haven't been able to
figure this out.
I have a letter that goes out to clients that I have been able to use
a mail merge for.  The problem is that some clients need up to 5
additional copies of the letter to be sent out.  It is different for
each client in the data set.  I would like to  print out all the
letters at once, with the correct number of copies printing for each
client.
Please help!
Thanks
 
M

macropod

Hi Francisco,

If you don't have a postscript printer, you could instead embed the control codes for whatever printer you're using in the PRINT
field. The code that would be required depends on the control code language (eg HPCL).

--
Cheers
macropod
[MVP - Microsoft Word]


I don't think I have a postscript printer, is there any other way to
do it? I did do a google search and came across a "virtual postscript
printer" - would this be an option? I'll look into it a little more,
but in any case thanks for your help!

Thanks
Francisco

Hi Francisco,

If you're is outputting the print job to a postscript printer, you could use a PRINT field, with an embedded MERGEFIELD linked to
a
data field containing the # copies. The PRINT field could be coded as:
{PRINT \p page "/#copies {MERGEFIELD CopyCount} def"}
where 'CopyCount' is the name of the data field holding the # copies to be printed. This field could be inserted anywhere on the
body of the page to be duplicated. A 'CopyCount ' value of 0 prevents the page from printing.

Although each record will only appear once in the merged output, you'll get the required number of copies in the print job. This
approach also has no effect on page numbering or TOCs, as the duplicate pages don't exist in the merged document.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]

Francisco Buella said:
Hi, I'm new to mail merges. I have searched the group for an answer,
and have found some similar questions, but I haven't been able to
figure this out.
I have a letter that goes out to clients that I have been able to use
a mail merge for. The problem is that some clients need up to 5
additional copies of the letter to be sent out. It is different for
each client in the data set. I would like to print out all the
letters at once, with the correct number of copies printing for each
client.
Please help!
Thanks
 
F

Francisco

Hi Francisco,

If you don't have a postscript printer, you could instead embed the control codes for whatever printer you're using in the PRINT
field. The code that would be required depends on the control code language (eg HPCL).

--
Cheers
macropod
[MVP - Microsoft Word]


I don't think I have a postscript printer, is there any other way to
do it?  I did do a google search and came across a "virtual postscript
printer" - would this be an option?  I'll look into it a little more,
but in any case thanks for your help!

Thanks
Francisco

Hi Francisco,
If you're is outputting the print job to a postscript printer, you could use a PRINT field, with an embedded MERGEFIELD linked to
a
data field containing the # copies. The PRINT field could be coded as:
{PRINT \p page "/#copies {MERGEFIELD CopyCount} def"}
where 'CopyCount' is the name of the data field holding the # copies tobe printed. This field could be inserted anywhere on the
body of the page to be duplicated. A 'CopyCount ' value of 0 prevents the page from printing.
Although each record will only appear once in the merged output, you'llget the required number of copies in the print job. This
approach also has no effect on page numbering or TOCs, as the duplicatepages don't exist in the merged document.
Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

Looks like I'm out of luck for my printer - from what I googled, the
Canon IP3000 PCL is not available...
 
P

Peter Jamieson

There are several possible approaches to this - probably a bit of VBA
code in Excel or Word to produce a new data source with the correct
number of copies for each record would actually be the best, but another
approach that /may/ work, but does not require VBA is as follows. It may
not work with large data volumes, or if you have over about 64 columns,
some with multiline data, and so on.

1. Let's say your workbook is in a file called c:\mywbs\myworkbook.xlsx
and that your data is in a sheet called "Sheet1"

2. Create a new workbook called c:\mywbs\mycopies.xlsx, put the
following data in column one of "Sheet1" (so the text "copies" is in
cell A1)

copycount
1
2
2
3
3
3
4
4
4
4
5
5
5
5
5
6
6
6
6
6
6

This is based on your statement that you need up to 5 copies, and that
you may mean "an original plus up to five copies". If you need more
copies, you can (if you mean "original and up to five copies" then add
6 rows with the number 6 in it.

3. Save and close that workbook.

4. In Sheet1 in "myworkbook.xlsx" you will need
a. a column that contains the total number of copies (i.e.
original+copies) for each letter. Let's say that column is called "copies"
b. preferably, a column that uniquely identifies each letter (let's
call that column "mykey"

First, as a test, in a new Word document, insert the following field -
notice that the backslashes in the pathnames are all doubled up.

{ DATABASE \d "c:\\mywbs\\myworkbook.xlsx"
\c "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=c:\\mywbs\\myworkbook.xlsx;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=37"
\s "SELECT * FROM [Sheet1$]" \h }

Select that field code and press F9 to execute it. If necessary use
Alt-F9 to view the results. If it has worked, you should see all the
rows in your data source in a table (If you have more than around 64
columns in your worksheet, the results should be tab-delimited rows of
data). If you save this document and close it, you should be able to use
it as the data source for a merge.

Now change the SELECT to be as follows:

"SELECT s1.* FROM [Sheet1$] s1 INNER JOIN
[c:\\mywbs\\mycopies.xlsx].[Sheet1$] s2 ON s1.copies = s2.copycount
ORDER BY s1.mykey"

Select and re-execute that field, and you should see the correct number
of copies of each row. Again, you should be able to save and close that
document and use it as a data source.

If you only need some of the columns from the data source, you can list
them in the SELECT, e.g.

"SELECT s1.mykey,s1.copies,s1.field1,s1.field2 FROM [Sheet1$] s1 INNER
JOIN [c:\\mywbs\\mycopies.xlsx].[Sheet1$] s2 ON s1.copies = s2.copycount
ORDER BY s1.mykey"

Worth a try anyway?

Peter Jamieson

http://tips.pjmsn.me.uk
 
D

Doug Robbins - Word MVP on news.microsoft.com

If the first field in the data source contains the number of copies that are
require for each client, if you run the following macro after setting up the
mail merge main document, it will print the required number of copies for
each client:

Sub MutliCopyMailMergefromExcel()
'The field containing the number of copies required for each record
'must be the first field in the datasource
'A reference must be set under Tools>References in the Visual Basic Editor
'to the Microsoft DAO 3.6 Object Library
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
End With
' Open the database
Set db = OpenDatabase(dSource, False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
Do While Not .EOF
'Create variables in the document with the names and values of the
fields in each record
j = .Fields(0).Value
For i = 1 To .Fields.Count - 1
If .Fields(i).Value <> "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.PrintOut Background:=False, copies:=j
End With
.MoveNext
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

What is the data source?

Peter Jamieson

http://tips.pjmsn.me.uk

The data source is an excel file
 
M

macropod

Hi Francisco,

You could try a PRINT field coded as:
{PRINT \p page "27"&l{MERGEFIELD CopyCount}X"}
where 'CopyCount' is the name of the data field holding the # copies to be printed. Note that the 'l' is a lower-case L, not the
digit 1

--
Cheers
macropod
[MVP - Microsoft Word]


Hi Francisco,

If you don't have a postscript printer, you could instead embed the control codes for whatever printer you're using in the PRINT
field. The code that would be required depends on the control code language (eg HPCL).

--
Cheers
macropod
[MVP - Microsoft Word]


I don't think I have a postscript printer, is there any other way to
do it? I did do a google search and came across a "virtual postscript
printer" - would this be an option? I'll look into it a little more,
but in any case thanks for your help!

Thanks
Francisco

Hi Francisco,
If you're is outputting the print job to a postscript printer, you could use a PRINT field, with an embedded MERGEFIELD linked
to
a
data field containing the # copies. The PRINT field could be coded as:
{PRINT \p page "/#copies {MERGEFIELD CopyCount} def"}
where 'CopyCount' is the name of the data field holding the # copies to be printed. This field could be inserted anywhere on the
body of the page to be duplicated. A 'CopyCount ' value of 0 prevents the page from printing.
Although each record will only appear once in the merged output, you'll get the required number of copies in the print job. This
approach also has no effect on page numbering or TOCs, as the duplicate pages don't exist in the merged document.
Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy &
paste
them from this message.

Looks like I'm out of luck for my printer - from what I googled, the
Canon IP3000 PCL is not available...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top