Use Power Automate to Send a Mail Merge With Personalized Attachments Stored in Teams

When you create a Power Automate mail merge flow, using Teams SharePoint to store your Excel data source and attachments folder allows you to share your mail merge responsibilities with individual colleagues or even with the members of a Microsoft 365 group (e.g. a Microsoft 365 group is created at the time an MS Team is created).

 

Create a Mail Merge Through Power Automate

Before working through the steps below, you should create a folder under Shared in one of your Teams Channels (e.g. the Team's General channel) to keep everything you need for your mail merge Flow in one place.  Note: all Teams files saved to Shared and files attached to Channel posts are actually stored in sharePoint.

Create Your Personalized File Attachments

Use our article, Use Adobe Acrobat to Split a Mail Merge File into Separate Files With Custom File Names, to generate your attachments that have been personalized for each recipient. Copy the folder containing these attachments into the Teams folder you created for your Flow mail merge.

 

Prepare Your Excel Merge Data

1. Use the list below to help create your Excel data file (.xlsx), Then save your file into the Teams folder you created for your Flow mail merge. DO NOT add it to the folder of attachments.
   
   Make sure that you include the following when creating your Excel file:

• An email address column containing each recipient's email address.
• Your UWSP alias ("spointer" from "spointer@uwsp.edu"). To easily add just the alias to the column, use the Excel function "TEXTBEFORE()".  Then copy the formula down the column.

e.g. =TEXTBEFORE(<Cell containing the email address>,"@")

• A column for Filename. This should contain the exact filenames with file extensions that were used when you created your personalized attachment files. To easily reconstruct the filenames in the columns, use the Excel function "CONCAT()".  Then copy the formula down the column.
  
  e.g. 
  
• Separate first and last name columns. Separate columns helps when setting up your salutation format (e.g. "Dear Stephanie, or more formally, Dear Mr. Nelson).
• Columns for any other information that can change for each recipient such as the person's title (Mr., Ms, Dr. etc).
• Easily identifiable column headers that clearly identify each columns' purpose.

2. Convert the Excel data into a Table.
   1. Click a cell inside the data.
   2. On your keyboard press Ctrl+T.
   3. In the Create Table box place a check in My table has headers then click OK.
   4. Make sure that your cursor is still inside the new Table then on Excel's menu at the right select Table Design.
   5. At the left on the Table Design ribbon type in a Table Name (no spaces, dashes and underscores are allowed).
   6. Press Enter to assign the name.  Power Automate will ask you to select this table when you get to that step.
   7. Close the Excel file.
      

 

Make a Second Data File for Testing

It is very strongly recommended that you first run your Power Automate mail merge flow with test data.

The easy way to do this is to:

1. Make a copy of your data file.
2. Remove all but 2-3 rows in addition to the column headers.
3. Change the email addresses to your personal email. Or/and to a couple volunteers in your office.

We use a copy of the original data to make sure there are no differences between the real and test files in column names or the number of columns. When you are satisfied that the Flow runs as expected with your test file, you just need to change the data source to run your actual mail merge.

 

Create Your Power Automate Mail Merge Flow

1. Sign in to your UWSP Office 365 account at office.uwsp.edu.
2. At the right on the Apps screen click All Apps ->.
3. Select Power Automate.
4. On the Power Automate screen at the left, click Create.
5. At the right select Instant cloud flow.
   
6. Give your new Flow a name (e.g. Mail Merge with Attachments").
7. From the Choose how to trigger this flow list select Manually trigger a flow.
8. Click Create.
   
    

Configure the Flow

Add the "List Rows Present in a Table" Action

1. Below Manually trigger a flow, click the New Action plus sign  ( + ). This opens the Add an action pane at the left.
2. At the left, search for Excel Online (Business).
3. In the Excel Online (Business) section click See more.
   
4. Select List rows present in a table.
5. At the left, in the List rows present in a table pane connect the following fields to your data:

• Location: Click the dropdown and select OneDrive or your Teams/SharePoint folder.
• Document Library: Click the dropdown and select OneDrive or the Teams/SharePoint document library (Typically "Documents").
• File: Click the Open folder icon at the right then select the Team Channel (e.g. General"), then locate and select your data file (remember to select your test data if you are in the testing phase).
• Table: Select the table you named in the prepare data step.
  

6. Click outside of this pane to collapse it.
7. Click Save (upper-right). Get in the habit of regularly saving your work, especially if you are orienting to the Power Automate environment.
   

 

Add the "Apply to each" Action

1. Below List rows present in a table, click the New Action plus sign  ( + ).
2. At the left, search for Apply to each and select it from the Control section. Once added, "Invalid parameters" may temporarily display under the control name.
3. At the left in the Apply to each pane click in the Select an output box then select the lightening bolt icon to the right.
4. Under the List rows present in a table section select body/value.
   
5. Click outside of this pane to collapse it. This removes the "Invalid parameters" warning for the control.
6. Save.
    

Add the Get files (properties only) Action

1. Directly below Apply to each inside the gray rectangle, click the New Action plus sign  ( + ).
2. At the left search for Get files (properties only), and select it from the SharePoint group.
3. At the left, in the Get files (properties only) pane connect the following fields to your data:

• Site Address: Click the dropdown and select the Team containing your Excel data source and folder containing attachments.
• Library Name:  Select Documents. "Documents" is the document library where the Team's files are stored.
• From Advanced parameters select "Limit Entries to Folder" then tab to close the dropdown. In the new "Limit Entries to Folder" click the folder icon at the right and select the folder that contains the attachments (Typically under "Shared Documents" > "General"...)
  

Add the "Send an email (V2)" Action

1. Directly below the Get files (properties only) Action (still inside the Apply to each rectangle) click the New Action plus sign  ( + ).
2. Search for Outlook 365.
3. In the Office 365 Outlook section click See more.
4. Select Send an email (V2).
5. At the left, update your email fields for the mail merge:

• To (address the email)

1. At the right of the To box, click the settings cog and select Use dynamic content.
2. Click in the To box then at the right select the lightening bolt icon.
3. At the top of the field list that opens, to the right of List rows present in a table, select See more. The list rows action added earlier connects to your Excel data fields.
4. Scroll down through the List rows present in a table section and select the column that contains the recipient email addresses.

• Subject

Type in a Subject.  Or, if you have a column with a custom Subject line for each record in your data table, you can add that column just as you added the recipient email addresses to the To field.

• Body

1. Add the mail merge template text. To add custom data from your table for each recipient (for example, name, etc) place your cursor where the custom data should be added.
2. At the right select the lightening bolt icon.
3. At the top of the field list that opens, to the right of List rows present in a table, select, See more.
4. Scroll down through the List rows present in a table section and select the column that contains the custom value you want to add. For example add a custom email salutation with <Title> and <Last Name> fields, and add other data columns within the email body that contain information specific to each recipient.

• Add a Personalized Attachment
  1.  Under Advanced Parameters select Attachments.
  2. Click Add new item.
  3. Click in the Name box then at the right select the lightening bolt icon.
     1. To the right of List rows present in a table, select See more.
     2. Select the column in your Excel data source that contains the attachments' filenames.
  4. Click in the Content box, then at the right select the lightening bolt icon.
     1. Under Get files (properties only), select Body/Value.

5. Save.
    

Test Your Power Automate "Mail Merge" Flow

1. At the upper-right click Test.
2. In the Test Flow pane that opens, select Manually.
3. At the bottom, click Test (You may also see Run Flow).
4. Click Done when prompted.

If your flow is set up correctly, in a few seconds (this will probably take longer with real data) you should see a notice that, "Your flow ran successfully", at the upper-left. Check your mailbox or ask one of your volunteers for your test to check.

• To resume editing your Flow, click Edit at the top-right.
• To Create a new Flow or see other Flows you may have previously created, at the upper-left click <- Back.  Create and My Flows are listed at the left.

 

Run Your Power Automate Flow With Your "Real" Data

1. Return to editing your Flow.
2. Select the List rows present in a table action.
3. At the left, update all fields as needed to connect the new file and table name. 
4. Click Save.

Troubleshooting: if you see any errors at this point, select List rows present in a table again. Even if fields like Location and Document Library did not change, reselect for each field, then Save again.

5. Click Test.
6. Select Manually.
7. Then click Test or Run Flow.

Wait until your Flow has completed successfully then check your mailbox Sent Items to see the copies of emails sent to the recipients.
 

Send The Same Email To New Recipients

You can remove old rows from the Excel data Table and update with new recipients and new data, then easily re-run your Flow whenever needed as long as you do the following:

• Make sure that any new rows you add to the data source are included in the data Table. New rows added at the bottom of a data Table should automatically be included in the Table. To verify that all rows are included, click inside the Table and press Ctrl+A on your keyboard to highlight the entire Table.
• If you change the file or Table name you must update the location fields in the List rows present in a table action.
• If you change a column name you must remove the old field and re-add it where it is located in the Send an email (V2) action. 
   

To Run Your Flow With Your Updated Data

1. Sign in to your UWSP Office 365 account at office.uwsp.edu.
2. At the right on the Apps screen click All Apps ->.
3. Select Power Automate.
4. On the Power Automate screen at the left select My flows.
5. Move your mouse cursor over the flow and click the Run arrow.