How to send a mail merge with 2 or 3 attachments per person in Power Automate

Create a table with one row per person and the columns ID, Customer, Email, Files and Sent. In Files, list each person's file names separated by a semicolon. Leave Sent empty. Put all the PDFs in one OneDrive folder (in the demo, Invoices) and check that the names match exactly.

In Power Automate, click Create, then Instant cloud flow. Give the flow a name, select Manually trigger a flow and click Create. You run the flow by hand whenever the files are ready.

Click the plus icon below the trigger and add Initialize variable. Set the name to Attachments, the type to Array and the value to []. This empty list will hold the files for one person.

Add List rows present in a table. Choose OneDrive for Business, the document library OneDrive, your Excel file and the table. Under Advanced parameters, select Filter Query and type Sent eq ''. This returns only the rows where the Sent column is empty.

Add Apply to each, rename it to For each customer and set the input to body value from List rows. Inside the loop, add Set variable, select Attachments and set the value to []. Without this reset, the second person would also get the first person's files.

Below Set variable, add another Apply to each and rename it to For each file. For the input, click the fx icon and add this expression, then click Add:

• split(items('For_each_customer')?['Files'], ';')

The loop now runs once for every file name in the row. Leave concurrency control off for both loops, which is the default.

Inside For each file, add Get file content using path for OneDrive for Business (not the SharePoint one). For the file path, click fx and add concat('/Video demos/Invoices/', trim(items('For_each_file'))), changing the folder path to your own. The trim removes the space after each semicolon, and there is no search, so the Excel file is never picked.

Below Get file content using path, add Append to array variable and select Attachments. Build the value in this order:

• Type {"Name": "
• Add the expression trim(items('For_each_file'))
• Type ", "ContentBytes":
• Add the dynamic content File content
• Type }

This is the name and content format the email action needs for attachments.

Click the plus icon below For each file, still inside For each customer, and add Send an email (V2). For To, click the gear icon, select Use dynamic content and pick Email. Type a subject and a body with the Customer dynamic content. Under Advanced parameters, select Attachments, click Switch to input entire array and select the Attachments variable.

Below Send an email (V2), add Update a row and pick the same location, library, file and table. Set the key column to ID and the key value to the ID dynamic content. Click Show all, and in the Sent field add the expression formatDateTime(utcNow(), 'yyyy-MM-dd').

Click Save, then Test, select Manually and click Test. Click Continue, Run flow and Done. In the run, For each customer should show one run per row, and Send an email should run once per customer.

In Outlook, open Sent Items and check that each person got one email with only their own files. The Sent column in Excel now shows today's date. Run the flow again, and List rows returns no rows, so nobody gets a second email.