?
Solved

VBA to mail merge multiple documents from one data set

Posted on 2016-08-24
3
Medium Priority
?
123 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 1600 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 400 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

589 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