For merges to email, see: Hyperlinks in data source fields are converted to plain text when you perform a mail merge on e-mail messages in HTML format in Word 2002 and Word 2003 Note: Hyperlink fields modified this way are liable to cease functioning once the merge has been executed. Step 2: Start the mail merge. In Word, choose File New Blank document. On the Mailings tab, in the Start Mail merge group, choose Start Mail Merge, and then choose the kind of merge you want to run. Choose Select Recipients Use an Existing List. Browse to your Excel spreadsheet, and then choose Open. If Word prompts you, choose Sheet1$ OK.
You appear to be using ad blocking software. While I respect your right to do so, please be aware that the minimal advertising on this site helps defray the cost of providing this facility, and I would therefore ask that you turn off the blocker while browsing this site.
Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.
Mail Merge Step by Step Mail Merge is a handy feature that incorporates data from both Microsoft Word and Microsoft Excel and allows you to create multiple documents at once, such as letters, saving you the time and effort of retyping the same letter over and over. A further and more recent addition to the range of merge splitting add-ins available from this site is the Merge to E-Mail add-in. This one is closely based on Word's built-in mail merge function and not only caters for merging to e-mail, but will also split the merge on the fly to separate documents and pdf files. Prepare the address list in Excel for mail merge. In essence, when you mail merge labels from Excel to Word, the column headers of your Excel sheet become placeholders in a Word document denoting the mail merge fields. Each placeholder corresponds to one entry such as first name, last name, salutation, city etc.
Merge and Split Add-in for Word 2007 - 2019
The Individual Merge Letters add-in is by far the most downloaded add-in from this site with many thousands of users world wide. However feedback, while helpful in developing the add-in, has revealed a fatal flaw when presented with CSV data sources.
Thanks probably to Microsoft's interminable updates, it has been found that the process now fails when presented with merge documents using CSV data sources. Why this should occur I have not yet been able to establish, so I have recommended the use of Excel data sources, which do not exhibit this limitation.
Unfortunately CSV data sources have been used as a means of overcoming the 256 field limit for mail merge and while admittedly few users have suffered from this limitation, I felt that I should address it. The solution is the add-in featured on this page.
The add-in works by merging to a new document and then splitting that document to separate files, based on the original merge document, so that formatting should be retained.
This add-in is an alternative to the original Individual Merge Letters add-in, with a different set of strengths and weaknesses to overcome the issues of splitting mail merge to separate documents.
Also included is an option to split documents which have previously been merged to a new document.
I should point out that while there were some formatting issues with the original Individual Merge Letters add-in, this has now been fixed, however where any formatting anomalies remain, where Excel data sources are used, the Many to One add-in, used in One to One mode will split the document without affecting the original formatting, as it does not use mail merge to complete the process and may be considered as an alternative.
The Many to One add-in adds another layer of merge options, should you wish to create a many to one merge or a merge directly to e-mail.
A further and more recent addition to the range of merge splitting add-ins available from this site is the Merge to E-Mail add-in. This one is closely based on Word's built-in mail merge function and not only caters for merging to e-mail, but will also split the merge on the fly to separate documents and pdf files.
Merge and Split
The add-in is a template which should be installed in the Word startup folder. The zip file includes a self extracting installer or you can manually save the template into the required folder - If you have not changed the preferred startup folder it can be located (in English language versions of Windows) by typing
%appdata%MicrosoftWordStartup
in the Windows Explorer Address bar and pressing Enter.
When Word is next started an additional group will be added to the Mailings Tab:
The reset button is provided to clear the program settings which are stored in the registry. The other button runs the program.
The application is error trapped to ensure that it can only operate on a merge document with an attached data source, If use is attempted otherwise you will see a warning message.
When run for the first time, the following dialog is displayed. The add-in can use PDF Creator to create protected PDF files (see later) but it requires the user to indicate whether this third party application is present.
Then following a standard disclaimer message:
you will see the following dialog with the available fields from the current merge document displayed. You may select any item from the dropdown list to be used as the filename. Ideally you would use a filename that is unique to each record, though the application will not overwrite existing filenames where two records share the same data. Instead a number is added to the filename.
The add-in will not allow the root folder of any drive to be the destination folder for the merge documents. This is to deter the use of removable media which could cause data loss, and to cater for recent operating systems which may not allow writing to the root of the C: drive.
As the add-in only works with merge documents, there will always be fields from that document presented in the list when the add-in is run. The first time the add-in is run (and at any time when a selected folder to store the documents is no longer available) the folder path text box will be empty and coloured pink.
If the folder path is typed into the dialog and that path doesn't exist, the background colour will remain pink and the 'Split to Documents' action button will be disabled. A folder selected with the browse function will always be valid, so the background colour will change to green,
The 'Split to Documents' action button will be enabled when the name field is selected.
The add-in provides for a second field to be included in the filenames of the individual documents. This second field is always inserted after the first selected field data, and you may choose from a small selection of separator characters.
The add-in by default creates PDF format files using Word's built-in PDF converter, however for users who have PDF Creator installed, the add-in will detect this and offer the option to create PDF files using PDF Creator, with the additional option of creating those PDF files with a selection of security options which are presented when you leave the main dialog and the relevant option is selected (see second and third illustrations below).
PDF creation using the built-in PDF Converter is much faster than creation with PDF Creator, so unless you need the extra functionality offered by PDF Creator, I would advise the use of the built-in converter.
The process has been configured to use version 2.5 of PDF Creator and will not work with version 3 or the earlier version 1 which require different VBA code sets.
Note that PDF Creator this also includes PDF Architect, which is not required, and unless you want this application, I would recommend that you use the 'Expert' installation settings and uncheck PDF Architect. Note also that the installer will offer assorted plug-ins for your browser, that are certainly not required. Make sure you de-select these during the installation.
When splitting to PDF format with PDFCreator the add-in additionally provides the option to engage PDF security measures. When the 'Continue' button on the main dialog is clicked, a second userform will open to offer that facility. Clicking 'Cancel' on this userform will cancel the whole process.
The items shown disabled in the following illustration become available when a Master Password is entered. If you don't want additional security measures then don't set a Master Password!
Currently the additional security options are only supported when the option PDF is checked. Should you check one of the other PDF format types, the dialog changes as shown below and any security settings entered are returned to their default values.
The add-in adds a temporary field (or fields if a second field is chosen to name the document) to the start (or optionally the end) of the merge document.
As the merge is completed the added field(s) are removed from the merge document before saving.
All that remains is to ensure that all the personal options are set and click the button to split the document.
Excel Workbook Text Converter For Mail Merge
The merge starts by creating a new merge document. In some circumstances e.g. in non-English version of Word, and where there may be more than one unnamed document present, the process may not be able to identify which is the merge document. When that happens you will see the following dialog which will list the unnamed documents. Select the merged document and click 'Continue' which will become available when a selection has been made. Cancel will cancel the entire process.
A progress indicator will display as the merge runs.
Split to E-Mail (as attachment) - Requires Outlook as the default E-Mail application.
A later addition to the add-in is an option to send the split documents to attachments to e-mail addresses listed in the data source (a CC may be sent to a second listed address if required).
Clicking the button produces the following warning. As that can become irritating with continued use, there is an option on the warning message not to show it in the future, until the add-in is reset.
If the option to save as PDF is selected, the attachment will be that PDF, otherwise it will be the selected document type.
When the button to split the document is activated, a second dialog is shown
The messages are created in html format. Paragraph breaks inserted into the Message Body Text field are preserved without the need to use html tags in the text. HTML tags are not translated by the process and will appear as text in the message body if used.
The messages are sent using the default e-mail account in Outlook and the signature associated with this account (if applicable) will be included.
The process includes minimal error checking and does not validate the e-mail addresses. Ensure e-mail; address fields contain valid data in the data source.
The messages are sent directly to the Outlook Outbox, so it is strongly recommended that Outlook is configured not to send messages immediately - especially when testing the add-in.
The e-mail dialog includes a Cancel button. Activating Cancel provides the additional options to Cancel the merge completely or just cancel the merge to e-mail part.
Macro option
Following on from a request from a user familiar with the macro option in my Individual Merge Letters add-in, I have added a similar function to this add-in.
This uses an overlaid panel when the 'Run a macro' button is activated.
When a macro name is selected, the button background changes to indicate that a macro is in use and the check boxes to retain that macro name and to determine when to run it are made available.
When the option to run a macro is employed, the document is first saved and then after running the macro it is saved again (without creating a backup). This ensures that the document is named and thus that name can be used in the macro. This can be useful for adding (say) the name and path to each document.
Excel Text Converter For Mail Merge
When the button is clicked again (note the changed caption) the macro panel is replaced by the PDF panel but the button background remains changed as an indicator of the macro use. This is important as preferred settings are stored for re-use.
For VBA programmers only!
Before the individual documents are saved (or after subject to the check box option), as Word documents or PDF files, you may run a macro on each of those documents.
Such a macro may be used, for example, to apply formatting lost in the merge, e.g. to apply superscript to particular characters.
Macros used by this function MUST have a single named parameter to address the document, i.e. they should be in the format
Sub MacroName(VariableName as Document)
Enter only the 'MacroName' part in the text box. 'MacroName' can be any valid macro name.
To retain the macro for the next merge, check the 'Retain this value?' check box.
Missing named macros will not produce an error, but if the macro contains an error that will cause it to crash, it will crash the whole process, so test your macro thoroughly before using it here.
Please note that I will not be providing a free of charge macro developing service for use with this process.
Split Merged Letter
The add-in also includes an option to split a previously merged document. This is primarily used where the original merge document and its data source are no longer available, but the user has a document that has been created by a 'letters' type of Word mailmerge, where each section of that document contains a similar 'letter' document.
The process has largely similar functions to the Merge and Split option detailed above and offers the addition of a split merge to the printer, where each 'letter' is treated as a separate print task.
This might be useful, for example, where the user wishes to use a printer with a stapling function for each merged 'letter'.
When using the option to split an existing merge to the printer, the document is not saved and thus any macro used here will not be able to access the document filename.
Here there are no fields to select, so the display shows the first 80 characters of each paragraph in the first section.
Select the paragraph that contains the identifying information for each 'letter' for use as a filename
The other features of this part of the add-in are similar to those described previously, though there are some differences with the e-mail option.
Split merged document to e-mail
As has already been indicated there is no access to the merge data from a document that has already been merged. Thus if you wish to split a merge to e-mail, you must include the e-mail address in the original merge document BEFORE it is merged to a new document.
The add-in will look for an e-mail address in the last complete paragraph of the merge document. It is therefore necessary to add the e-mail address field in a paragraph on its own at the end of the mere document and followed by a paragraph break. i.e. as shown below:
The e-mail dialog is different and requires only the addition of a subject (common to all the messages) and where the merge is to attachments, a covering body text is also required.