Email from Excel with Attachments

In a previous post, Mail Merge from Excel, we demonstrated how to create individual email messages using data from Excel and a template from Word.

Although useful, it lacked one important ability: attaching user-specific files (i.e., attachments) to each outgoing email message.

Having each email recipient open a unique set of attachments is ideal for a multitude of scenarios, like sending out customized invoices or order information.

This can be accomplished by using Microsoft Excel and Power Automate, both part of a Microsoft 365 subscription.

No VBA and no external add-ins to purchase.

We’ll use Excel to store the information about which files are attached to which emails, and Power Automate will be used to perform the “mail merge”-esque process of sending each custom message to the recipients.

Let’s see how this Dynamic Duo will get the job done.

Examining the Support Files

Located in our OneDrive is a folder called “Contracts” that holds a series of uniquely crafted PDF files along with an Excel file.

The Excel file has a table named “TableFiles” that holds information about the user’s name, email address, and name of the PDF file for that user.

The PDF files are contracts with information specific to each recipient’s negotiated details.

These files can be stored in OneDrive, SharePoint, or a different folder from the one holding the Excel file.

If you are curious about how to create multiple PDF files from Word just by saving or placing the Word document in a specified folder, check out my post/video for Power Automate: Beginner’s Guide.

Our goal is to select the PDF files in the Excel document and then run a Power Automate Flow to perform all the merging and distribution of the data to the users.

All excel courses Leila Gharani

Master it ALL!

Whether you're a beginner or advanced Excel user, there is a course for you in our academy

GET ACCESS

Setting Up the Power Automate Flow

The first thing to do is to open your favorite browser, like AOL Explorer or Flock (HA!  Gotcha.) and log into your Microsoft Office account.  Form there, scroll down the icon list on the left and select Power Automate.

Since we are creating a Flow from scratch, we select “Create” from the list of Power Automate categories on the left of the screen.

Flows start by defining the “trigger”; what is going to cause this Flow to execute its instructions.

Because our Flow is triggered based on cells selected in an Excel file, this falls under the purview of an Instant Cloud Flow.

Give the Flow a name, such as “Mail Merge with Attachment”.

We scroll down the list of Instant Cloud Flow triggers to locate and select “For a selected row – Excel Online for Business”.

Click “Create” to move to the next steps.

Defining the Excel File Data Location

The next step is to tell the Flow where the Excel file is located and which table in the Excel file to consult when performing the email distribution.

Click “New Step” to proceed.

The Test

We need our Flow to check the filename listed in the Excel table to see if there is a PDF in a folder that matches the filename.  If so, we want to attach that folder’s file to the outgoing email message.

We want to loop through all files in a folder, checking for a matching name.

This is an action supported by OneDrive.

In the “Choose an Operation” panel, search for “OneDrive” to locate the “OneDrive for Business

In the list of Actions, scroll down to locate “List Files in Folder”.

Click the Browse button to locate the folder holding the PDF files.

Click “New Step” to move on to setting up our looping operation.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Looping Through the Folder’s Files

In the “Choose an Operation” panel, select “Control”.

Select the control labeled “Apply to Each”.

We need to use an output from the previous step.  Scrolling down the list of Dynamic Content, we select “Value” from the list.

Next, click “Add an Action” to add another “Control”.  This time we will use a “Condition”.

This allows checking to see if something occurs based on some form of logic and then act in one of two fashions.  This is like writing an IF statement in Excel except it will be created using all point-and-click actions.

Our objective is to cross-check the name of the PDF file against the current row’s defined filename.

We need to point to the column in the Excel file labeled “File Name”.

Scrolling down the list of Dynamic Content, we come across an entry for the “File Name” field from the Excel file’s table.

Next, we set the logical operator to “is equal to” and set the comparator to the “Display Name” option.

Adding the “Yes” Action

To define what happens if one of the PDF file’s display names matches the listed filename in the Excel cell, click the “Add an Action” located in the “If Yes” panel.

Like we did previously, in the “Choose an Operation” panel, search for “OneDrive” and select “OneDrive for Business”.

Scroll down the list of Actions to locate the “Get File Content” action.

In the “Get File Content” panel, scroll down the list of Dynamic Content and select “Id”.

Click “Add an Action” to begin setting up the email operation.

Creating the Dynamic Email Message

Search for “Outlook” to locate all Outlook-related operations.

Select the Action labeled “Send an Email (v2)”.

Define the TO: field

We need the TO: field to be dynamic based on the “Email” column in the Excel table.

Click “Add Dynamic Content” and scroll down to select “Email” from the list.

Setting the Subject

The subject can be a fixed statement for all email recipients, or it can be dynamically customized for each recipient.  Making it dynamic would require an additional column in the table to hold all the unique subject lines.

For this example, we will go with the fixed statement “Your Contract”.

Defining the Email Body

For the body text, we will use a combination of static text and dynamic content to give the reader the illusion that each email was hand-crafted just for them.

The result of the To:, Subject:, and Body: elements appears as follows.

The only thing missing is the actual PDF attachment for each outgoing email message.

Attaching the PDF

In the “Send an Email” panel, click “Show Advanced Options”.

The Advanced Options allows you to exercise several email-related actions, such as:

  • Defining a sender’s email address
  • Add CC or BCC email addresses
  • Attach files
  • Redirect replies to an alternate email address
  • Set Importance levels

All of which can be defined dynamically.

To attach a PDF file:

  1. Set the “Attachment Name” option to the “Name” field in the Dynamic Content list
  2. Set the “Attachment Content” option to the “File Content” field in the Dynamic Content list

Click the Save button (upper-right) to save the Flow to our Power Automate library.

Testing the Power Automate Flow with Excel

Return to OneDrive and open the Excel file that contains our table of email address and PDF file preferences.

To execute the Flow, select the Data tab and click Flow from the Automation group.

NOTE: If you do not have the Flow option, you can add the feature by selecting Insert -> Get Add-Ins, search for Flow, then add it to Excel.

Once you have activated Flow the following panel appears to the right of the Excel window listing all of the Power Automate Flows that are compatible with Excel.

If you select a single “File Name” cell in the table, the Flow will only process that single row’s PDF comparison and deliver a single email message.

If you select all the rows in the table (you can get away with only selecting the column of PDF filenames), the Flow will process all selected rows.

With the cell(s) selected, click the “Run” arrow.

If this is the first time you have used the Flow, you may need to sign in to your OneDrive account.

Click the “Run Flow” to execute the Flow process.

You can monitor the execution and completion status of the Flow by visiting the Flow Runs Page or click Done to exit the Flow panel.

Checking Out the Results

The received email will appear as follows with the included attachment.

Opening the attached PDF, we see that it is the PDF file that was designated for the intended recipient.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials