Solved

VBA to mail merge multiple documents from one data set

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

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 17

Expert Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now