How to mail merge with individual attachments using Power Automate and Excel

Put each recipient's file in one OneDrive folder. The video uses demo invoice PDFs, but any file type works. In an Excel Online workbook, list the contacts with an Attachment column holding each person's file name.

File names can be written with or without the extension. If two people have files with the same name but different extensions, include the extension so the search finds the right one.

Select all the data, choose Format as Table, make sure My table has headers is ticked and click OK. On the Table Design tab, rename the table (the video uses ContactsData). The flow reads the data through this table.

Power Automate ignores Excel's display format. A date column formatted as a date comes through as a serial number, and a currency column loses its symbol.

Format those columns as Text instead. For the currency column, copy the values into Notepad, set the column to Text, and paste the values back. The cells now hold text like $250.00 that the flow sends exactly as shown.

Open Power Automate at make.powerautomate.com. Go to Create, select Instant cloud flow, name it Mail merge with separate attachments, choose Manually trigger a flow and click Create. An instant flow runs only when you press Run, which is what you want for a mail merge.

Add List rows present in a table from Excel Online (Business). Set Location to OneDrive for Business, Document Library to OneDrive, browse to the contacts workbook and select the table.

Add Apply to each from Control. For the input, pick body/value from List rows present in a table. Rename this loop Contacts. The name matters because the expressions later refer to it.

Inside the loop, add Find files in folder from OneDrive for Business.

• Search Query: the Attachment column (click See more if it is not listed)
• Folder: the folder with the attachments
• File Search Mode: OneDrive Search

Inside the first loop, add another Apply to each and set its input to the output of Find files in folder. The search returns a list, so this loop handles the file it found. If no file is found, this loop does nothing and no email is sent for that contact.

Inside the second loop, add Get file content from OneDrive for Business and set File to Id from Find files in folder. This gives the flow the actual file to attach.

Still inside the second loop, add Send an email (V2) from Office 365 Outlook. Paste your email template into the body. The video uses a short invoice reminder with placeholders for first name, due date and amount.

Inside the nested loop, the dynamic content does not list the contact columns properly, and the Excel dynamic fields will not follow the Contacts loop. Use expressions that point at the loop instead.

In To, switch to advanced mode and insert:

items('Contacts')?['Email']

Use the same pattern for the other fields:

• Subject: items('Contacts')?['Due Date']
• Body: items('Contacts')?['First Name'], items('Contacts')?['Due Date'] and items('Contacts')?['Amount']

The name inside the brackets must match your Excel header, including spaces. Capital letters do not matter.

Under Advanced parameters, add Attachments and click Add new item.

• Attachments Name: Name from Find files in folder
• Attachments Content: File content from Get file content

In Advanced parameters, add CC. Switch to advanced mode and insert items('Contacts')?['CC']. Do the same with BCC if you have a BCC column.

Save the flow, click Run, confirm the connections to OneDrive and Outlook, then click Continue and Run flow. Check your Sent Items. Each contact gets one email with their own attachment and their own name, due date and amount.