Solved

VBA to mail merge multiple documents from one data set

Posted on 2016-08-24
3
37 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

786 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