Use Power Automate to Send a Mail Merge From a Department Mailbox Account

The following instructions show how to use Power Automate to send a mail merge from a department or other shared mailbox. 

Tip: To use Power Automate to send a mail merge from your personal UWSP Outlook mailbox, see: Use Power Automate to Send a Mail Merge From Your Outlook Mailbox.

Once you become comfortable with Power Automate, mail merges through Power Automate Flows are actually easier than traditional mail merges. An Excel data source containing the recipient email addresses is used in the following Power Automate instructions on how to send a mail merge from a shared mailbox. 

Using a Power Automate Flow is especially useful if you need to personalize the Subject line for your mail merge recipients since the new version of Outlook no longer supports adding merge fields to customize email Subject lines.

Important: To run a mail merge through a shared mailbox you MUST have permissions to send email from the shared mailbox and have added it in your Outlook.

Create a Mail Merge Through Power Automate

Prepare Your Excel Merge Data

1. Create and save your Excel data file in your OneDrive. Note: You can also access other file storage locations like Teams/Sharepoint.
   Make sure that you have included the following:

• An email address field.
• Separate first/last name fields if you intend to address your email recipients by first name or more formally (e.g. Mr. Nelson).
• Any other fields containing information that may 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 for...").
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.
• Document Library: Click the dropdown and select OneDrive.
• File: Click the Open folder icon at the right 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" will 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. 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 "Send an Email From a Shared Mailbox (V2)" Action

1. Directly below Apply to each inside the gray 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 from a shared mailbox (V2) then at the left, update your email fields for the mail merge.

• Original Mailbox Address

Begin typing the name of the shared mailbox or the mailbox alias. Select the mailbox from the list that opens.

• To

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. This gives you access to the list rows data from the previous step.
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 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 CC and other fields

 Add other fields from Advanced Parameters (below the message Body). 

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 volunteer for your test to check their mail for an email from the shared mailbox.

You can also check the Sent Items folder of the shared mailbox to see copies of all emails sent by your Power Automate Flow.

• 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 the shared 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 from a shared mailbox (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.