Mail merge from Excel spreadsheet data

Have you ever had to send out similar messages to a substantial number of recipients with just a few slight alterations in the body and the subject of the message? Have you ever had to deploy a personalized marketing campaign or send special offers to your most loyal customers so that any of them would feel truly special? Then you might have probably made use of the mail merge technology, or, at least have heard of it. In a nutshell, mail merge works as an inverse template or, rather, super-template, when, instead of using the same template to create similar messages over and over again, a user creates just one template with a number of placeholders which propagates itself across all the messages adding specific values for each recipient from a pre-set database, such as the recipient’s email address, first name, date, price, city or position.

What is a mail merge source database supposed to look like? The up-to-date version of Microsoft Office provides for mail merge from a number of data sources, such as Microsoft Access databases, Active Directory entries, Microsoft Outlook Contacts, and Microsoft Excel spreadsheets. In the present article we will give a brief overview of how to prepare and use Excel mail merge source files and also dwell on a few extra features of mail merge from Excel performed through Mail Merge Toolkit, a handy add-in for Microsoft Outlook.

Mail merge from Excel spreadsheets is a powerful and efficient working tool enabling to automate personalized mass mailing with the use of integrated Microsoft Office functionality. First, Microsoft Excel spreadsheets is a popular way of storing and editing corporate data, such as lists of important customers and partners, especially for small and medium businesses. Second, the creation of a template is rather transparent, for all you have to do is to make sure you have a separate column for every field in the template. For instance, separate first names and last names in the spreadsheet if you choose to address the recipient by his first name only. And last, but not least, figures and dates from Excel mail merge source files retain their formatting throughout the process, which is convenient if you have dates and prices.

To begin with, you need to create a data source for the mail merge in Excel, or format the existing spreadsheet with data to suit our needs. We have already mentioned that it must contain a column for every placeholder you would like to use in your future letter or document. Next, it is advisable to use homogeneous formatting for all the values in a column. Mail merge is generally able to retain source formatting; it is also possible to make some changes during the process. However, all the changes from now on will have to be made in a bulk, so it is high time to decide on a single format for each column, especially those with figures and dates. There are also a few handy tips regarding the merge of numerical data, as it might sometimes fail to run as smoothly as the user had expected, due to certain features that need a workaround. Thus, in order to merge postal codes starting with ‘0’ and not lose the leading zeroes, the respective column should be formatted as text. After you have prepared the source spreadsheet data, save the changes and launch Microsoft Word proceeding to the second step.

Now make sure that the Mailings tab is shown on the ribbon and choose from a number of mail merge options. For beginners the easiest way would be the Mail Merge Wizard, carefully ushering you through every stage and prompting for solutions about every detail. You can choose from existing letter templates or create a new one; you need not worry if the words you have used for template fields differ from the respective columns of the source file. For instance, if you have a ‘Job’ column that you would like to link to the ‘Position’ field, it can be done through the ‘Match Fields’ option. And finally, before completing the mail merge from Excel data, it is possible to preview the results using the tool called Auto Check for Errors (from the Preview Results group).

However comprehensive and sophisticated may it seem to be, the mail merge functionality of Microsoft Office still lacks a number of useful – and sometimes essential – options, leaving some space for specialized solutions, such as Mail Merge Toolkit by MAPILab. This Outlook add-in is totally compatible with the Mail Merge Wizard and expands the range of sending options quite significantly. Probably the most important add-in feature is the ability to send personalized messages with attachments, including individual attachments for every recipient. For this, you need to include file paths to the Excel mail merge source file as a separate column. Another useful tool is the CC option, enabling to notify the recipient’s manager or secretary, for instance. Next, with Mail Merge Toolkit you can also personalize the message Subject, entering the necessary variables. What is more, the Toolkit is a good solution if you need to perform mail merge with mismatched versions of Word and Outlook, which is sometimes the case in medium-sized corporate environments. And finally, the add-in enables to send messages in RTF, HTML and PDF formats straight from Microsoft Word, and GIF messages from Microsoft Publisher, including the image mapping technology. This feature provides for better compatibility with the variety of mail clients used by recipients, which means that the addressee gets exactly the message you want them to see displayed in the desired way.

8 thoughts on “Mail merge from Excel spreadsheet data

  1. Quick question – I used the demo version of mail merge toolkit last night to send emails to a list of our Ski Club members using an excel spreadsheet to pull the data and word to complete a form that was an attachment to the email. It all worked perfectly after extensive testing to figure it all out. However, when I completed the process, it indicated that 3 recipients were excluded, but I can’t seem to find any way to figure out which 3 recipients were excluded without having to go line by line through my spreadsheet to match the members to the sent emails. Any ideas how I can more easily identify those members who were excluded?

    1. Hello, it is possible that certain emptied rows of data in the spreadsheet might have been processed as recipient records, and then omitted as not containing valid e-mail addresses. But in order to know for sure, please enable the logging feature: https://www.mapilab.com/blog/wp-content/uploads/2015/06/1679.png
      This should enable the logging. Two important text files will be created.

      You can find these files by clicking the “Browse Microsoft Outlook add-in logs” (or by hitting the “Windows Key” and “R” simultaneously on your keyboard, and typing %AppData% (along with the percentage symbols) in the resulting window). A folder should open, where you can see the “MAPILab Ltd” folder, among the others. Inside the “MAPILab Ltd” folder should be “Mail Merge Toolkit” folder. It contains the files required to trace the skipped records. The first one is called the same as your Outlook profile name (or, by default: “Outlook.log”). The other one is inside the “Word” folder – it is called “mmt.log”.

      You can re-send the same mailing when Outlook is in offline mode (Outlook > File > Options > Advanced > disable “Send immediately when connected” option) to populate and browse the logfiles.

  2. Will this add-in work if the recipients have varying numbers of attachments? For instance, recipient 1 may need to get only one attachment; recipient 2 might need two attachments; recipient 3 might need three. (??)

    1. Yes, you can add multiple columns as attachment data fields to an Excel file – and in each column, specify your files for each recipient’s record.
      The cell may contain either single full filename (correct format: %PATH%\filename.EXT), or file mask: %PATH%\*.* (and all files in the specified folder will be attached).
      Or, you can keep some cells blank – and no files will be attached for such recipients from the blank cells.

  3. Can you use this add in to do an email merge using an Excel spreadsheet as the data source file but put more than one row on one email based on whether or not a merge field is blank. I tried using NEXT RECORD IF but that doesn’t seem to work.

    I want to send a list of servers to an application owner that need to be updated to SQL Server 2012 SP3. I left a blank row between the rows of the various apps but the Office 365 Mail Merge (from within Word, Mailings) wants to generate a separate email for each row even though 5 rows may belong to the same application owner.

    Thoughts? Will your add in accommodate this?

    Thanks!

    1. Hello, the add-in does not support the NEXT RECORD IF statement, unfortunately, and does not differ from the standard Word mail merge in that regard (creating separate messages for each row), so this particular scenario might not be possible with such data-source. Dividing it into separate data-sources might be the solution.

  4. Hello, the Mail Merge Toolkit is an extension to the native MS Word mail merging process by design, and the said functionality depends more on the actual MS Office settings. You can only choose between the mail “Profiles” (each may consist of different accounts) rather than *accounts* themselves in terms of performing a mail merge. There is a solution, however:

    Please create a new mail profile (Start-> Control Panel-> Mail) and configure a single alternative account for the alternative mail profile there, then select “Prompt for a profile to be used”. – This way, you will be able to specify the mail profile with the single alternative account instead of the account when performing mailing from Word.

    You can find a more detailed description of this procedure in the following Microsoft article: https://support.microsoft.com/en-us/office/create-an-outlook-profile-f544c1ba-3352-4b3b-be0b-8d42a540459d

    For all subsequent questions and notes, please visit our trouble ticket area: https://www.mapilab.com/support/

Leave a Reply

Your email address will not be published. Required fields are marked *


eight − = 5