Mail merge: Guide to sharing web form links via email, the smart way! – SurveyCTO Support Center

This guide is accompanied by this sample spreadsheet file. To follow along, you can either save a copy of the sample form in your Google Drive, or download as an Excel workbook.

Let's say you have a web form you would like to send out to a number of respondents, and each respondent will receive a unique link used to identify the respondent. You can manually send each email, but that can be tedious. Instead, you can perform what is called a mail merge, where you create a spreadsheet that lists each respondent's information, such as their unique form URL, and then use an email client to mass-send the emails for you!

1. Preparing your spreadsheet

First, you need to prepare a spreadsheet of the respondent information. Take a look at the spreadsheet below (you can also view the full version at this link):

You can include as many columns as you need, but the most important are the Email Address and Unique Link columns.

The actual spreadsheet application you use can depend on the email client you will use. However, it is usually easy enough to convert data between Excel, Google Sheets, and other applications.

It is a good idea to choose your mail merge application before starting your spreadsheet, since many mail merge applications have their own spreadsheet template style. We put that section last because using the mail merge application is the last step.

Further reading: Prepare your Excel data source for a Word mail merge (Microsoft link)

1.1 Formulas for constructing your spreadsheet

The example spreadsheet contains several formulas to make creating the spreadsheet easier. If you prefer to enter all of the data manually, you can skip this section.

1.1.1 Form link

If collecting data anonymously, the unique web link to fill out a form, which you can obtain using the Share button on the Collect tab of your server console, is the same for each respondent. The generic unique web form link is your starting point here too. That link looks something like this:

https://SERVERNAME.surveycto.com/collect/FORM_ID?caseid=

In this example, the generic web form link is repeated in the Form Link column, as part of building the unique link.

In the example Google Sheet, the ArrayFormula() function is used to complete the columns more easily. This function applies the formula to all cells beneath it, so it automatically expands as you add new data to the sheet. To learn more, check out the Google documentation. Excel does not have a similar function, but you can instead select the cell, click-and-hold the square dot on the bottom-right, and then drag it down as far as you need it to. You can also double-click that dot to autocomplete the formula down through the rows you have data for (also possible in Google Sheets).

1.1.2 Unique ID

There should be another column for the unique identifier of the respondent. Unlike the form link, each unique ID will be different.

1.1.3 Unique link

Once the form link and the unique identifier is stored in a cell, you can construct the unique link that will be sent to the respondent. In both Google Sheets and Excel, the ampersand ("&") is used for concatenating (joining). For example, on row 2, if the generic link is in column D, and the unique ID is in column E, you can use this formula for the unique link:

=if($D2 = "", "", $D2 & $E2))

There are of course other approaches in spreadsheets. You might also use the CONCATENATE() function in either Excel or Google Sheets as part of a solution.

2. Creating an email template

You will need to create a template for the email you are going to send that includes a spot where the unique URL will go. It should include other pieces of information as well, such as the name of the respondent. Here is an example of a template that can be used by Mail Merge with Attachments for G Suite (more details below):

Hi {{First Name}}, This is a survey from Example Organization, and we would really appreciate it if you filled it out. To start, click the link below. Make sure you complete and submit the form. <a href="{{Unique Link}}" target="_blank">Click here to begin the survey</a> If you cannot click on that link, copy-and-paste this URL into your browser, and then go to that link: {{Unique Link}} Thanks for your participation, Example Name 

Here, HTML is used to style the sheet, and double-curly-brackets are used for variables, in this case column names. If this template is used, then {{First Name}} will be replaced by the first name of the respondent, and {{Unique Link}} will be replaced by their unique URL. Here is what that will look like for the first respondent:

Hi Andrew,

This is a survey from Example Organization, and we would really appreciate it if you filled it out. To start, click the link below. Make sure you complete and submit the form.

Click here to begin the survey

If you cannot click on that link, copy-and-paste this URL into your browser, and then go to that link: https://example.surveycto.com/collect/example_form?caseid=1

Thanks for your participation,
Example Name

This is one example, but different mail merge applications may use different syntax. For example, with Microsoft mail merge, you insert each field as you need it. Even if you don't know the precise syntax yet, planning out what your email should say can help you work out the necessary syntax.

3. Using the spreadsheet in your email client

Once your spreadsheet is ready, you can link it to your email client to start sending emails. The exact process depends on your email client.

When selecting your service, there are a few things to keep in mind:

Security of the service (see 3.1 below)
Ease of use
Features

Many services allow you to track who has opened your emails, and even who has clicked on the links in your emails. That way, you can see who has opened the links to view the form, even if they have not submitted it yet.

3.1 A note about data security

Whichever mail merge solution you use, make sure that it seems to be a service you can trust. Remember that you'll be sharing the email addresses and names of survey respondents, which is personally identifying information that you may not want to share widely. Less scrupulous mail merge providers might take it upon themselves to retain and repurpose contact information used in mail campaigns run using their service.

Further, consider the permissions and level of access that the service you're considering requests from you, if it integrates into your own email service. This could include permissions to read your contacts, and other account information. Some of these permissions may be required for the software to do its job though.

That said, mail merge email campaigns are a very common and convenient practice. There are many excellent and trustworthy solutions out there. We've listed a few below for consideration. We have not done a comprehensive security review, but if you are critical and look into questions around security yourself, you should be able to find an option you can trust that works for you.

3.2 Microsoft Outlook

If you have Microsoft Outlook, Excel, and Word (all are included in Office 365), you can create a mail merge with these products. First, prepare your data spreadsheet using the details above. Then, create a template using Microsoft Word. Finally, use Word to "Mail Merge to Outlook". For details, check out this Microsoft documentation:

Use mail merge to send bulk email messages

Their documentation on how to use mail merge for bulk email, letters, labels, and envelopes may also be helpful.

3.3 Gmail

Unlike Outlook, Gmail does not have mail merge built in. However, many users and developers have created tools that integrate with Gmail and G Suite to help you with mail merge. Most of them are free for basic use with premium services available. Here is a short list:

3.3.1 Mail Merge with Attachments

This is a highly-rated add-on for G Suite. What's great about this add-on is that it works right in Google Sheets, meaning you can set up your template and send emails all from within the spreadsheet.

3.3.2 Other mail merge option

That is just one example, but there are many other great mail merge applications out there for G Suite: