Solved

VBA to mail merge multiple documents from one data set

Posted on 2016-08-24
3
46 Views
Last Modified: 2016-09-13
Hi All,
I need some guidance on the mail merge to create multiple pdf documents using vba. Example: From the below  sample table i need to create letters in PDF ( if possible with password protection) based on the country column. The result should be 2 PDF seperate documents USA file will have 5 and UK 4 letters ( based on number of records in each country).

First Name      Title                                       city            Country
Davolio            Sales Representative      Seattle            USA
Andrew              Vice President, Sales      Tacoma            USA
Janet               Sales Representative      Kirkland            USA
Margaret      Sales Representative      Redmond      USA
Steven              Sales Manager            London            UK
Michael              Sales Representative      London            UK
Robert              Sales Representative      London            UK
Laura              Coordinator                      Seattle            USA
Anne              Sales Representative      London            UK

Appreciate your help.
0
Comment
Question by:S Kumar
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
xtermie earned 400 total points (awarded by participants)
ID: 41769845
You can actually do this without VBA by using query/filter parameters in the mail merge Select recipients where you can set Country = USA and do the merge, save the resulting document into a single PDF and name it USA and then change the query/filter parameter to UK and repeat and save that as UK.pdf - see attached screen shot for query parameter.  To do this:
1)  On Mailings click Edit Recipient List
2) Click Filter
3) Select the Field you want to filter by and the value and press OK
4) Press OK

If you need each document to be separate or something more detailed there is a free add in that you can use named MergeTools – 20150422.dotm Add-in that Doug Robbins - Word MVP has created from the following page of hisOne Drive: http://bit.ly/1hduSCB

The MergeTools – 20150309.dotm file needs to be saved in the Word Startup folder.  In Windows Vista and Windows 7, 8 or 8.1, the default location for that folder is

 C:\Users\[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

The requirements for using Doug's add-in/system are:

1.The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.  

2.For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.  For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility

3.For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.

4.For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

5.For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.
0
 

Assisted Solution

by:S Kumar
S Kumar earned 100 total points (awarded by participants)
ID: 41770511
Thank you for the solution. I had tried this method for small database. However for merging  large database this will be a repetitive work. I will also try the add in suggested above.
Any other solutions will be appreciated.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41795683
Good Comments and recommendation made
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question