Chapter 5: Outlook Mail Merge

There are many features of Microsoft 365 that allow you to automate several of your tasks. Mail Merge is included in this. If you find yourself writing the same email over and over to different recipients each time, then this is a chapter you won’t want to skip. You can save hours upon hours by utilizing the techniques taught in this chapter. Mail Merge is used to send bulk emails, letters, labels, or envelopes to several email accounts and you will be able to personalize the information for each email ID.

We will start by creating a simple mail merge using our contacts information from within Outlook. After that, we will create a more advanced mail merge by first creating an Excel spreadsheet to act as a container to hold our merge information to start our mail merge to send custom invoices to our clients. This will include changing the invoice for each client based on the last name, which we will process through Power Automate.

We will be covering the following topics in this chapter:

• Creating a mail merge in Outlook
• Mail Merge custom attachments, Cc, Bcc, and Subject

Let’s begin with examining Mail Merge.

Mail Merge

Mail Merge in Outlook allows you to send a message to a large group of people at once without the recipient knowing who else received the message. In many cases, the recipient will not know you sent it to others and will think they were the only one who received the message from you. With Mail Merge, the recipient will only see their own name in the To field of the email. It is also possible to personalize other fields within the message or even have attachments personalized as well. Many people try to be discreet in not allowing the recipient to know who the other recipients are by entering the email IDs in the Bcc field.

Creating a mail merge in Outlook

There are 10 steps involved in creating a mail merge in Outlook that can be sent via email. These steps are very straightforward and will be described here as we create a mail merge to be sent by email:

1. The first step is to select Contacts in the navigation pane.
2. Select the contacts that you want to send the email to, or you can select the location of an Excel list or another type of list that contains those names and data. In order to select the Mail Merge feature inside of Outlook, you need to select the Contacts folder. Another option for this step is to start Mail Merge from Microsoft Word.
3. The next step is to start Mail Merge. Go to Home | Mail Merge.

Figure 5.1 – Mail Merge button

4. Next, we must verify that the correct options are selected for Mail Merge Contacts.

Figure 5.2 – Mail Merge Contacts

The following are the selections that should be made for our mail merge:

• (A) Contacts: Select Only selected contacts because we selected the contacts at the start of this mail merge.
• (B) Fields to merge: Select All contact fields to include all of the Outlook fields to use as a possible field in the mail merge. We do not have to use all the fields but they will be available to us if wanted. If you filtered the contacts information, you could choose Contact fields in current view.
• (C) Document file: Select New document, which will start a new document in Microsoft Word. If you have already created a document with the message entered or have a previous email saved in your system, then you should select Existing document and you need to specify the location of the file.
• (D) Contact data file: No selection is needed in this section for this mail merge. You should use Permanent file and enter the location for the file to be saved if you want to save this contact data for future use, or you could click Browse... to find a name and location to save the file to.
• (E) Merge options: Select Email from the Merge to drop-down options to have an email created from the mail merge. Also, you can supply a message subject line if you prefer. You will be given that option later as well; this box will appear after selecting the Email option.

5. Click OK and wait for Microsoft Word to open. If you have included a contact group within your selection, a dialog box will appear, where you have to acknowledge that the contact group will not be merged in the Mail Merge process. Click OK to acknowledge this.
6. Now, a new document will open in Microsoft Word. In Word 2007 and later, the MAILINGS tab will be activated automatically when Word opens.

Figure 5.3 – MAILINGS tab in Word

7. Create your document as you normally would in Word. Use the Mail Merge features on the toolbar to insert a special character for inserting the text that is specific for each email (such as a name). If you selected to get this information from another document, such as Excel, the headers from that file will be available to you under the Insert Merge Field dropdown.

Figure 5.4 – Insert Merge Field in a Word document

8. Upon completion of the email, it is best to preview what it is going to look like when merged on each email. For this, select the Preview Results button, then you can use the arrow buttons in the ribbon to browse through all the personalized emails to check that they’re correct. Click Finish & Merge | Send Email Messages….

Figure 5.5 – Send Email Messages...

9. In the Merge to E-mail dialog box, you can enter text in the Subject line field if you did not specify this earlier. You can also change and/or verify that the other settings are correct.

Figure 5.6 – Merge to E-mail

10. Click OK to complete the mail merge to send the emails. Be sure you are ready to have the emails delivered or sent, as once you click OK, the emails will be generated and sent without warning.

Mail Merge is a technique that sounds more complicated than it is in my opinion. The time efficiency that is saved with this process can enhance your productivity. Many people know that a mail merge can also be created in Word, but few people think of sending the document created as an email through Outlook.

Figure 5.7 – Merge to E-mail

We will now create a mail merge using Power Automate to allow us to send merged attachments along with the mail merge.

Note

Verify with your ISP or mail administrator that you aren’t restricted regarding how many email messages you can send within an hour or in total in a day. You may have this restriction set on your account limits. If you keep your emails to under 100 messages a day, you probably will not run into any issues and there is probably no need to contact your ISP in this case. If there is a limit set, you may want to consider sending out a mail merge in smaller groups over several days or using a third-party email marketing company.

Mail Merge custom attachments, Cc, Bcc, and Subject

One disadvantage of the Mail Merge process is we cannot send attachments with the emails that we send out automatically. You can download some VBA/macro code to do this, or you can download some paid or free add-ins that will allow this as well. I’m going to teach you how to do this for free using Microsoft Power Automate.

For this example, we want to have a customized PDF invoice file as the attachment in the email that goes to each customer. For this example, Phil Barr will have a file called Invoice_PhilBarr.pdf as the attachment. If you want, you can also customize the subject line to say Barr Invoice. This will be customized for each email as the name changes.

Power Automate is a Microsoft app that can save you a great deal of time in the automation process between your apps. The user interface is easy and fun to use. This application used to be called Microsoft Flow and has now been rebranded as Power Automate, so you may see some documentation referring to it as Microsoft Flow.

Creating a spreadsheet

Before you open Power Automate, you will want to have a spreadsheet created that includes all the information that you want to include for the attachments or dynamic text that you will use in the Cc, Bcc, and Subject lines.

Figure 5.8 – Excel document for Mail Merge with Power Automate

For the attachments on the Excel spreadsheet, you will include the filename of the documents that you want to be merged with the email IDs that will be completed. The actual file needs to be saved in OneDrive in the cloud along with the Excel file to be merged.

You must also select the data in the Excel file and convert it into a table. To do this, click inside the data (assuming you have no blank rows or columns; if you do have these, clean up your data first to remove the blanks), then select Insert | Table. The table will now be highlighted, and you can confirm that the Where is the data for your table? option has provided the correct cell address. In this example, we will have cells A1:E6, written as $A$1:$E$6. Ensure that the box is checked for My table has headers and then click OK. Once you do this step, it will be formatted as a table. You can adjust the styles as desired.

Figure 5.9 – Where is the data for your table?

Assign the table a name by clicking on Table Design | the Table Name box. I have named my file TableData. If you do not see the Table Design tab, ensure you have clicked inside the table in Excel. This tab is only viewed when you have clicked on your data in Excel.

Figure 5.10 – Table Name

Mail Merge with Power Automate

Let’s see how to use Mail Merge with Power Automate:

1. Open Microsoft Power Automate, which you will find in the apps menu for your office.com account. This will also work for the free version of outlook.com. I will be demonstrating with screenshots from a Microsoft 365 account. You can access the apps menu from the waffle icon in the top left of your screen. You typically must do this the first time you use a new app. Click on Power Automate.

Figure 5.11 – Power Automate icon

You may see some links for what you are trying to complete if you were looking up this topic outside of Power Automate.

2. Click on the Create button to start a new flow or process to create.

Figure 5.12 – Creating a flow

3. Next, decide what you want to create and what trigger you want to set. Our trigger will be from an Excel file with rows that we will select, so we need to go to the Start from blank section and select Instant cloud flow.

Figure 5.13 – Instant cloud flow

4. Scroll down the list of options on the right-side menu.
5. Select For a selected row, which will trigger a flow for a selected row in an Excel table. Before you click Create, give the flow a name in the Flow name box; name it Mail Merge with Attachment.
6. Now, click Create.

Figure 5.14 – Flow for a selected row in Excel

7. In the For a selected row dialog box, enter text for Location, Document Library, File, and Table for the Excel file. After filling in the data requested, click the + New step button, as shown in Figure 5.15:

Figure 5.15 – For a selected row

Now, we need to add the ability to look through files in a folder and find the appropriate invoice to attach to each email. This is a OneDrive operation. Click on the Standard tab, as shown in Figure 5.16, and select OneDrive for Business:

Figure 5.16 – Choosing OneDrive for Business

8. The action that we need is List files in folder, which appears in the list of actions for OneDrive for Business.

Figure 5.17 – List files in folder action

9. You will now be prompted to sign in to OneDrive and then populate the List files in folder box. To do this, click on the folder icon and then select the folder in OneDrive where you have the attachments saved.

Figure 5.18 – Folder location of files

10. Now, we need to click + New step to apply a control that will check the attached file too.
11. Click Control | Apply to each.

Figure 5.19 – Apply to each

12. In the Apply to each dialog box, click on the Add dynamic content button and choose value. What this is indicating is that for each row in the Excel spreadsheet (Apply to each control), complete a loop that will apply an invoice (value: list of items) to the email.

Figure 5.20 – value

13. Now, add another condition control by clicking + New step | Control | Condition to cross-check the name with the name you have in the Excel file. This time, we will select the File_Name column from the Excel file. If it finds the value in the OneDrive folder that matches the filename in the Excel spreadsheet, the Yes condition will be met; otherwise, the No condition will be met, so we want to choose a dynamic field here. For File name, choose is equal to and then choose Display ….
14. Next, select the Add an action button to add a Yes condition. We will not need to set a No condition as we don’t want anything to happen if there is not a matching file.

Figure 5.21 – Condition

15. There are two conditions or actions to apply here under the Yes condition:
    • Retrieve the file content for the attached copy if the filenames match.
    • Send the emails. Click + Add an action under the If yes condition, then search for OneDrive for Business and choose Get file content.

Figure 5.22 – Get file content

16. The unique identifier for this will be Id, so click the Add dynamic content button and choose Id.

Figure 5.23 – Unique identifier

17. The next condition is for Outlook 365 to send the email. Click Add an action | Office 365 Outlook | Send an email (V2).

Figure 5.24 – Send an email (V2) action

This dialog box is where you write your email. Fill out the fields that you wish to include and be sure to use dynamic fields where desired. You also need to include spaces where you want a space between the dynamic field and the text you are typing in manually. An example here would be on the Subject line: after selecting the Last Name dynamic field, add a space and then type Invoice. Next, enter the email in the Body field of the message using dynamic controls as desired.

In this example, we have also filled in Attachment Name as well as Attachment Content for the attachment. This will show us the name of the file as well as the attached file content. We are simply telling Outlook what to attach for each email ID in the spreadsheet. You may also change the Importance field to Normal as it will email out as low importance if you do not change this control.

Figure 5.25 – Send an email (V2)

That will complete our flow. There is no need to change the If no condition, since we do not want anything to happen if there is not an invoice name to match the filename in Excel. That will be left blank, as shown in the following figure:

Figure 5.26 – If no condition

18. Click Save at the bottom of the new flow that is being created.

Figure 5.27 – Saving the flow

Now, let’s discuss the Mail Merge flow.

Running the Mail Merge flow

Now you are ready to test or run this flow. Open the Excel file from the OneDrive account and be sure to close the desktop version if it is running so that the two files are not open at the same time:

1. You will need to install Flow in Excel if you have not used this feature before. Click on Insert | Office Add-ins within the Excel environment, then search for Flow, which is a Microsoft add-on and will be installed quite quickly. Once the installation is complete, click on Data | Flow (if added previously), which will appear at the right side or end of the ribbon. Once you click on Flow, the Flow pane will appear on the right, next to the spreadsheet.
2. With the email selected that you want to process, click on Flow and all the flows attached to this spreadsheet will appear in this list. You may be prompted to sign in if you are doing this for the first time. Click on the flow you want to run.

Figure 5.28 – Flow within Excel

3. Click on the run arrow to have the flow process the emails or click on the three vertical dots to open the options menu to access the Edit, Save As, Turn off or Delete options for the flow. Verify that there are green checkmarks next to the programs, indicating that the application is ready and you are logged in and have permission, then click Continue.

Figure 5.29 – Run flow

4. Click the Run flow button to confirm that you are ready to run the flow.

Figure 5.30 – Confirming running the flow

If everything went well and the flow was processed successfully, you will see a dialog box indicating that it was successful.

5. Click the Done button to complete the flow. For more information on the flow, you can open it in the original setup in Power Automate, where information about the flow will be displayed.
6. Click on the tab for My flows to view a list of the flows available that you have created.

Figure 5.31 – Flow done

I would suggest that you test drive this with your accounts before sending emails out to your customers. This is an easy way to send out customized attachments in emails, as well as customizing the Subject, To, Cc, and Bcc fields, which cannot be done otherwise inside Outlook without using code or add-ons.

This is only one of several ways of using Power Automate with Outlook. Seven actions are available inside Power Automate to use with Outlook. They are as follows:

• Launch Outlook.
• Close Outlook.
• Retrieve email messages from Outlook.
• Send an email through Outlook.
• Process email messages in Outlook.
• Save Outlook email messages.
• Respond to Outlook mail message.

I encourage you to look around and get familiar with all these available actions. Outlook is a very powerful tool, and this exercise demonstrated what a great tool it is to utilize with Outlook.

Summary

When you have many personalized emails that you need to send out, but not enough to justify using an email marketing company, Mail Merge can be your best productivity tool. You can save hours and hours and headaches by learning how to utilize this technique correctly. Combine this with Power Automate and the sky is the limit in what you can accomplish.

In the next chapter, we will cover the variety of ways that you can manage your calendars using Outlook Calendar. The way you use calendars can be different for every individual. You may find yourself not needing every tool available but maybe only one or two. However, I have always been taught that you don’t know what you don’t know. Let’s dive into the next chapter and show you the magic behind calendars. That could be in the form of a new view you didn’t know you could display or a new way to set up a meeting or color code an event. If you’ve never used the calendar inside Outlook before, you are in for a big surprise and your productivity is about to soar.

Questions

1. What documents can be created through Mail Merge?
2. How do you start Mail Merge in Outlook?
3. Can you send emails in Outlook by using the Word Mail Merge tool?
4. Can you set up Mail Merge to have an email sent on a future date?
5. How many emails can be sent in a day?

Answers

1. Emails, letters, envelopes or labels, and directories.
2. Many people try to find the Mail Merge tool on the ribbon in the email object view, but it is not located there. You can find the Mail Merge tool in the Contacts object view in the actions section.
3. Yes, you can send emails through Outlook by selecting email as the output type for the merge.
4. You can set the date for the Mail Merge to a future date using the delayed delivery feature. This, however, will only work if you are using Microsoft Exchange.
5. For Office 365, you can send a maximum of 10,000 emails per day. A single email can be addressed to a maximum of 500 recipients. In Outlook.com you can send up to 300 emails per day with a maximum of 100 recipients per message. These limits can be adjusted by your administrator, so it is best to check the limits with that person. To view all of Microsoft Exchange’s limits, go to https://docs.microsoft.com/en-us/office365/servicedescriptions/exchange-online-service-description/exchange-online-limits.