I recently needed to send a few hundred customized email messages to a list of vendors.
The requirements were relatively simple on paper. Each vendor needed to receive the same general message, customized with their company name. Two PDF documents needed to be attached to every message, the message needed to be sent as another Microsoft 365 user, the email needed a proper company logo in the signature, and I wanted some way of tracking which recipients had actually been processed so that an interrupted or failed run could be resumed without blindly starting over.
This seemed like something Outlook should be able to do.
It cannot.
At least, not without getting considerably more creative than I think should be necessary.
A Small Microsoft 365 Rant
I cannot help but feel that this entire exercise is a good example of how Microsoft 365 has become somewhat stale when it comes to adding genuinely useful features.
For what Microsoft 365 costs, I continue to find substantial parts of it surprisingly unimpressive and rough around the edges.
Organizations pay recurring subscription fees month after month, and Microsoft generates an extraordinary amount of revenue from those subscriptions. I would expect that kind of recurring revenue to result in a platform that feels relentlessly maintained and polished.
It often does not.
There are still parts of the Microsoft 365 administration experience that look like they were designed in the early 2000s, if not the 1990s. There are interfaces, workflows and little corners of the product that seem to have been sitting untouched for years and desperately need somebody to blow the dust off, rethink them and give them a thorough polish.
And then there are the missing features.
Sending a personalized message to a list of recipients is not exactly an exotic business requirement.
I would expect to be able to open Outlook, select an Excel or CSV file, map a couple of fields into a message, attach a few files, optionally send as another mailbox I already have permission to use, and click Send.
Outlook has existed for decades.
Mail merge has existed for decades.
None of this should be particularly novel.
Instead, accomplishing it properly required:
Excel
Power Automate
Microsoft Graph
Outlook connector HTTP requests
HTML
Base64 encoding
CID attachments
Send As permissions
and a small amount of prayerThe frustrating part is not that Microsoft provides tools capable of building this. Power Automate and Microsoft Graph are genuinely useful tools, and I am glad they exist.
It is infuriating that they increasingly seem to be required to fill gaps in Microsoft 365 itself.
There are a growing number of things I find myself looking at in Microsoft 365 and thinking:
Surely this should already be a feature.
Instead, the answer is often some variation of:
Build a Power Automate flow.
Or:
Write an Outlook add-in.
Or:
Call Microsoft Graph.
That is perfectly reasonable when I am building something unique to my organization.
It is much harder to justify when I am recreating functionality that feels like it belongs in the product in the first place.
For the price of Microsoft 365, I expect more polish, more attention to the old and neglected parts of the platform, and more useful functionality in the core applications without having to build it myself.
Power Automate should be there to automate my business processes.
It should not have to be the feature-development department for Outlook.
Anyway.
The good news is that Microsoft does provide all of the pieces required to build this ourselves.
So, in the interest of never having to figure all of this out again, here is the process that ultimately worked.
Create the Excel Mailing List
The Excel workbook acts as both the source of the mail merge and a record of what Power Automate has already processed.
The columns I use are:
RowID | Email | Company | Sent | SentAtFor example:
| RowID | Company | Sent | SentAt | |
| 1 | vendor@example.com | Example Vendor | ||
| 2 | accounts@example.com | Another Vendor |
Email and Company are used to construct the message.
Sent and SentAt are populated by Power Automate after a message has successfully been sent.
RowID is a unique identifier for each record and becomes important when Power Automate updates the spreadsheet.
Create an Excel Table
One thing that was new to me during this process was the concept of an Excel Table.
Power Automate does not simply work with arbitrary rows and columns in a spreadsheet. The data needs to be contained in a defined Excel Table.
Start with the column headings:
RowID | Email | Company | Sent | SentAtSelect the data in Excel and press Ctrl + T.
Make sure My table has headers is selected and click OK.
Excel will create a Table, usually named something exciting like Table1. You can rename it under Table Design if desired.
This is the Table you select in Power Automate's Excel actions.
That is really all there is to it.
Keep the Workbook Permanent
I initially experimented with changing which Excel workbook Power Automate referenced.
This turned out to be a bad idea.
When I changed the workbook and reselected the Excel Table, some of the mappings in Update a row were cleared or changed. In one case the key value changed and the Sent and SentAt mappings disappeared.
Nothing about the designer made this particularly obvious.
I eventually decided that the much safer approach was to keep one permanent workbook connected to the flow and simply replace the data inside it whenever I needed to run another mailing.
For example:
Vendor Notification Email Files
VendorList.xlsx
Letter.pdf
Agreement.pdf
Logo.pngThe Power Automate flow always points to the same workbook and the same Excel Table.
When I need to prepare another mailing, I clear the previous records and paste the new records into the existing Table.
I do not replace the workbook.
I do not recreate the Table.
I do not change the Excel references inside Power Automate.
Power Automate can remain happily attached to the same file forever.
Think of the workbook as a tiny, somewhat questionable database that Power Automate has become emotionally attached to.
One other lesson: after replacing or significantly changing the data in the workbook, wait a minute or two before running the flow.
The Excel connector appears to cache data for a short period. I encountered situations where I had just changed values in the workbook and Power Automate behaved as though the previous values were still present.
My process therefore became:
Replace or update the data in the existing Excel Table.
Save the workbook.
Close Excel.
Wait a minute or two for the cache to expire.
Run the flow.
Leave the workbook alone until the run finishes.
That proved much more predictable than changing the workbook associated with the flow.
List Only Messages That Have Not Been Sent
Add an Excel Online action:
List rows present in a table
Select the workbook and Table and add the following Filter Query:
Sent ne 'Yes'This tells Power Automate to retrieve only rows that have not already been marked as sent.
If the flow is run again later, completed rows are ignored and processing continues with whatever remains.
This makes the flow resumable, which turned out to be extremely useful.
For larger mailings, I also like processing the list in controlled batches.
For example:
Top Count: 50The first run processes 50 unsent records.
Those records are marked as sent.
The next run retrieves the next 50.
This makes a mailing much easier to monitor and troubleshoot than simply throwing several hundred records at Power Automate and hoping for the best.
It is also worth remembering that List rows present in a table does not necessarily return an unlimited number of rows by default. If working with larger lists, pay attention to pagination and row limits.
Retrieve the Attachments Before the Loop
The PDFs and logo are identical for every recipient, so there is no reason to retrieve them from OneDrive hundreds of times.
Before the Apply to each loop, add one Get file content using path action for each file:
Get PDF attachment 1
Get PDF attachment 2
Get PNG logoThese run once.
Their output can then be reused for every email inside the loop.
Why I Did Not Just Use Send an Email (V2)
My first version of this flow used the normal Outlook:
Send an email (V2)
It actually worked very well.
Except for the logo.
I initially converted the company logo to a Base64 data URI and inserted it directly into the HTML email.
Outlook on Android happily displayed it.
Desktop Outlook did not.
Of course.
I did not particularly want to host the logo on a public website. Remote images can be blocked by email clients and there is really no reason for a basic email signature logo to depend on an external web server.
What I wanted was a proper inline MIME attachment referenced using a Content-ID:
<img src="cid:company-logo">Microsoft Graph supports this.
The normal Power Automate Outlook email action does not make it particularly convenient.
So I switched to Microsoft Graph.
Create the Message with Microsoft Graph
Inside the Apply to each loop, add:
Office 365 Outlook → Send an HTTP request
Configure it approximately as follows:
Method: POST
URI:
https://graph.microsoft.com/v1.0/me/messages
Headers:
Content-Type: application/jsonThe request creates an Outlook draft.
The request body can contain the recipient, subject, message body, sender, importance, file attachments and inline image.
A simplified example looks like this:
{
"subject": "Important Vendor Update",
"importance": "high",
"from": {
"emailAddress": {
"name": "Sender Name",
"address": "sender@example.com"
}
},
"toRecipients": [
{
"emailAddress": {
"address": "@{items('Apply_to_each')?['Email']}"
}
}
],
"body": {
"contentType": "HTML",
"content": "<div style='font-family: Arial, Helvetica, sans-serif; font-size: 15px; line-height: 1.5; color: #000000;'><!-- Outlook could have just had a button for this, but here we are. --><p>Hello,</p><p>I am reaching out regarding our relationship with @{items('Apply_to_each')?['Company']}.</p><p>This is where the actual message content would go.</p><p>Please admire the inline logo. It was harder than it looks.</p><p>Best regards,</p><div style='font-weight:700;'>Sender Name</div><div>Job Title</div><img src='cid:company-logo' alt='Company Logo' style='display:block;border:0;'></div>"
},
"attachments": [
{
"@odata.type": "#microsoft.graph.fileAttachment",
"name": "Letter.pdf",
"contentType": "application/pdf",
"isInline": false,
"contentBytes": "@{base64(body('Get_PDF_attachment_1'))}"
},
{
"@odata.type": "#microsoft.graph.fileAttachment",
"name": "Agreement.pdf",
"contentType": "application/pdf",
"isInline": false,
"contentBytes": "@{base64(body('Get_PDF_attachment_2'))}"
},
{
"@odata.type": "#microsoft.graph.fileAttachment",
"name": "company-logo.png",
"contentType": "image/png",
"isInline": true,
"contentId": "company-logo",
"contentBytes": "@{base64(body('Get_PNG_logo'))}"
}
]
}Obviously the names inside the Power Automate expressions will depend on whatever the actions have been named in the flow.
The important part of the inline logo attachment is:
"isInline": true,
"contentId": "company-logo"The HTML then references it using:
<img src="cid:company-logo">This gives Outlook a proper inline image instead of trying to convince desktop Outlook to render a Base64 data URI.
The:
"importance": "high"property also marks the resulting message as important.
Sending as Another Microsoft 365 User
In my case, the messages needed to appear as though they had been sent by another employee.
The account associated with the Power Automate Outlook connection therefore needed the appropriate Exchange mailbox permissions.
I used Send As.
The account running the flow must have Send As permission on the mailbox being used as the sender. Full Access alone is not sufficient.
There is also a separate Send on Behalf permission, which allows a user to send as “User A on behalf of User B.” That permission must also be explicitly granted and is different from Send As. For this use case, I wanted the message to appear as though it came directly from the other user, so Send As was the appropriate permission.
The message contains:
"from": {
"emailAddress": {
"name": "Sender Name",
"address": "sender@example.com"
}
}One distinction worth understanding is that /me/messages creates the draft in the mailbox associated with the Power Automate connection.
Changing the from property changes who the message is ultimately sent as.
It does not cause the draft to appear in the other person's Drafts folder.
This actually became useful during troubleshooting because messages that failed partway through the process could sometimes be found sitting in my own Drafts folder.
Send the Draft
The Graph request creates the message but does not send it.
On success, the request returns the newly created message, including its ID.
Immediately after the HTTP action, add:
Send a Draft message
For Message Id, use:
body('Send_an_HTTP_request')?['id']The sequence is therefore:
Create Graph draft
↓
Receive message ID
↓
Send Draft messageThis worked very reliably once the source data had been cleaned up.
Mark the Excel Row as Sent
After Send a Draft message, add:
Update a row
I recommend using a unique RowID as the key:
Key Column: RowID
Key Value: [RowID from the current Apply to each item]Then update:
Sent: Yes
SentAt: utcNow()The ordering matters.
The row should only be marked as sent after the email has successfully been sent.
If the HTTP action or the send action fails, Power Automate never reaches the Excel update.
That row remains blank.
The next execution of the flow sees it again and retries it.
This turned out to be one of the most useful parts of the entire system.
A successful row looks something like:
RowID Email Sent SentAt
142 vendor@example.com Yes 2026-10-09T02:14:27ZA failed row remains:
RowID Email Sent SentAt
143 bad@example.comWhen I ran into problems later, I could immediately identify the problem records by filtering for blank Sent values.
No digging through hundreds of Power Automate iterations was required.
Do Not Use the Email Address as the Row Key
Originally I used Email as the key for Update a row.
That worked until there were duplicate email addresses in the source data.
Imagine this:
Email Sent SentAt
vendor@example.com Yes 10:00
vendor@example.comThe second row is still unsent.
On the next run, Power Automate sees that second row and sends the message.
It then tries to update:
Key Column: Email
Key Value: vendor@example.comBecause more than one row has that address, Excel can update the first matching row:
Email Sent SentAt
vendor@example.com Yes 10:15
vendor@example.comThe second row is still blank.
The next run sends it again.
And again.
And again.
Using a unique RowID avoids this problem entirely.
It does not prevent duplicate email addresses from receiving duplicate messages. If the same address appears twice in the source table, Power Automate will quite reasonably assume that there are two messages to send.
If that is not desired, deduplicate the source list before running the flow.
RowID identifies the Excel record.
Email identifies the recipient.
Those are not the same thing.
Slow the Flow Down
I added a Delay action after each completed email:
5 secondsI also left Apply to each running sequentially rather than enabling concurrency.
The loop therefore looks like:
Apply to each
Create Graph draft
↓
Send Draft message
↓
Update Excel row
↓
Delay 5 secondsThere is no particular reason I need to fire hundreds of administrative messages through Exchange simultaneously.
A five-second delay makes the process easier to monitor, keeps Excel writes sequential and gives Microsoft 365 a little room to breathe.
At five seconds per message the delay itself only adds a little over eight minutes per 100 recipients.
I can live with that.
Excel Is Not a Database
One of the more important lessons from this exercise is that Excel Online should not be treated as though it were a transactional database.
Which, admittedly, should not be shocking.
I experienced situations where I changed a Sent value in Excel and immediately ran the flow again, only for Power Automate to behave as though the old value was still present.
Waiting a minute or two and trying again resolved it.
Consequently, my process is now:
Finish changing the data in the Excel Table.
Save the workbook.
Close Excel.
Wait a minute or two.
Run the flow.
Do not edit the workbook while the flow is processing it.
I would particularly avoid immediately launching another execution when the safety mechanism depends on recently updated Sent values.
Give those updates a chance to exist everywhere Microsoft apparently needs them to exist.
Watch for Garbage in Email Addresses
After more than 300 messages had processed successfully, I ended up with exactly 27 that repeatedly failed.
The strange part was that they were always the same 27.
They were scattered throughout the spreadsheet.
Waiting 15 minutes did nothing.
Running the flow again did nothing.
Every other message worked.
Even stranger, Power Automate would sometimes create the email in my Drafts folder but the HTTP action would fail to provide a usable message ID to the following action.
Initially, this looked like some bizarre Microsoft Graph or Power Automate problem.
It wasn't.
The email address field contained garbage.
Some of the problem cells had trailing spaces.
Others contained hidden line breaks.
Visually, several looked completely normal in Excel.
After manually cleaning those 27 addresses, they all worked.
So if a particular set of records fails consistently while hundreds of others work, inspect the source data carefully before tearing the automation apart.
Look for things like:
- Trailing spaces
- Leading spaces
- Line breaks
- Carriage returns
- Multiple addresses in one cell
- Unexpected punctuation
- Other invisible characters
In this case, manually correcting the 27 records took a couple of minutes.
That was much easier than adding another layer of sanitization logic to the flow.
Sometimes fixing the data really is the better solution.
The Run Details Interface Can Lie to You
This one nearly caused me to send part of the mailing twice.
When running the flow using Test from within the Power Automate designer, I can watch the Apply to each loop progress normally.
As each iteration completes, the child actions change to green checkmarks and the interface advances through the records.
This is useful.
During a normal flow run, however, I found that the Run details interface could appear stuck on an early iteration even though the flow was continuing to process records in the background.
For several minutes I was staring at an action that appeared to still be running.
Naturally, I assumed the flow was hung.
It wasn't.
It had already processed considerably more records than the interface suggested.
This is not merely a cosmetic annoyance.
If an administrator sees a flow apparently stuck and starts another execution, both flows can begin processing the same source data.
That can result in duplicate messages.
Ask me how I know.
Fortunately, in this case the duplicated portion was relatively small and the world continued to rotate.
I submitted feedback to Microsoft because the behaviour of the normal Run details interface should match the live status information shown when monitoring a test execution.
A monitoring interface that incorrectly makes a healthy process look dead can cause the person monitoring it to take exactly the wrong action.
For now, I would not start another execution solely because the child actions appear stuck.
Check whether the workbook is continuing to change.
Check Drafts or Sent Items where appropriate.
Check the overall run status.
And generally give Power Automate a chance to prove that it is actually dead before attempting to resurrect it.
The Finished Flow
After all of that, the finished flow is actually fairly straightforward:
Manually trigger a flow
↓
List rows present in a table
Filter: Sent ne 'Yes'
↓
Get PDF attachment 1
↓
Get PDF attachment 2
↓
Get PNG logo
↓
Apply to each
├── Create Graph email draft
├── Send Draft message
├── Update Excel row
│ Key: RowID
│ Sent = Yes
│ SentAt = utcNow()
└── Delay 5 secondsThe Excel Table is:
RowID | Email | Company | Sent | SentAtThe resulting system gives me personalized HTML email, proper inline images that render in desktop Outlook, multiple static file attachments, Send As support, message importance, automatic tracking of successful records, easy identification of failures, resumable processing and reasonably controlled sending.
There are certainly dedicated products designed to send bulk personalized email.
For an occasional administrative mailing to a few hundred existing contacts, however, I really did not want another service, another subscription, another database and another place to upload company contact information.
Excel, Power Automate, Outlook and Microsoft Graph already did the job.
It just took considerably more convincing than it probably should have.