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.
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.
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”.
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.