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.
Featured Course
Black Belt Excel Package
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.
Featured Course
Excel Essentials for the Real World
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:
- Set the “Attachment Name” option to the “Name” field in the Dynamic Content list
- 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
