Project Overview
A production Power Automate cloud flow I designed, built, deployed, and documented for a recurring weekly collections process. Each week, 20 to 30 past-due invoices needed their most recent outreach email located and routed to the Director of Operations for follow-up. The manual version meant opening Sent Items and searching for each invoice number one at a time.
The flow reduced that from roughly 20 minutes of sequential searching to about 5 minutes of review. More useful than the raw time saved: because approvals arrive asynchronously rather than blocking on a search, the work no longer occupies a continuous block of attention.
This was built in a professional setting, so the code and configuration are not publicly shareable. Everything below describes the architecture and the design decisions behind it.
The Design Constraint That Shaped Everything
This automation touches outbound client communication. A wrong match doesn't just produce a bad record, it sends the wrong email to someone. That ruled out a fully unattended flow.
So the flow is deliberately human-in-the-loop: for every invoice where a match is found, it pauses and presents the matched email's subject line and sent date for approval before anything is forwarded. An incorrect match gets caught and rejected before it reaches anyone. The automation handles the searching, which is the tedious part. A person still makes every send decision, which is the consequential part.
Architecture
Trigger (Manual)
→ Build invoice number list
→ Initialize NotFound / Skipped arrays
→ Loop per invoice number:
Search Sent Items
→ Sort matches by date (newest first)
→ Match found?
No → Log to Not Found list
Yes → Request approval
Approved?
Yes → Rebuild original email and send to DOO, with attachment
No → Log to Skipped list
→ Send run summary email (Not Found / Skipped) to self
Built entirely on standard connectors (Office 365 Outlook, Approvals). No premium licensing required.
Engineering Decisions
Nothing fails silently. Two array variables track the two ways an invoice can not get forwarded: NotFound (no matching email existed) and Skipped (a match was found but rejected at approval). Both are initialized outside the loop, since initializing inside would reset them every iteration. After the run completes, the flow emails itself a summary of both lists, so an invoice that quietly fell out of the process is visible rather than simply absent.
Search query syntax. The original query used quoted-phrase syntax (subject:"Invoice 12345"). The connector wraps the entire Search Query value in its own outer quotes, which collided with the inner quotes and produced a malformed request. Switching to an AND-joined unquoted form is functionally equivalent here and avoids the collision.
Sorting for the right match. Outreach emails for the same invoice can use different subject variants, and search results don't return in a reliable order. The flow sorts all matches newest-first so the most recent outreach is always the one acted on. Power Automate's sort() only sorts ascending, so reverse() is required. It sorts on receivedDateTime rather than sentDateTime, because this connector version doesn't expose a separate sent-time field on Sent Items messages.
Rebuilding instead of forwarding. The original design used Forward an email (V2), but that action has no Subject field, since Outlook's forward operation preserves the original subject as-is. The recipient needed "Collections" flagged in the subject line for triage. The flow instead fetches the full original message and composes a new email with a reconstructed header block (From / Sent / To / Subject) above the original body, with the attachment carried across.
Known Limitations
Documented deliberately, since these are the edges a future maintainer would hit:
| Limitation | Impact | Path forward |
|---|---|---|
| Not a true Outlook forward, so no native FW: thread-linking | The recipient gets a fresh message rather than a threaded forward. The manual header block is a substitute for that context. | Migrate to Microsoft Graph's createForward draft pattern via an HTTP action if native threading becomes a requirement |
| Manually triggered rather than scheduled | Requires someone to remember to run it weekly | Convert to a Recurrence trigger if a fixed cadence is preferred over manual control |
| Only with Attachments: Yes filter on the search step | An outreach email sent without the invoice attached would be excluded from results and land in Not Found rather than being forwarded | Acceptable under the current process, where every outreach email includes the invoice. Worth revisiting if that practice changes. |
| Outlook connections can go stale after a password change or MFA re-enrollment | Flow fails with "Unauthorized" until reconnected | Reconnect and verify all Outlook-connected steps point to the same healthy connection |
Documentation
The flow shipped with a full SOP: a weekly operator checklist, a build reference documenting every step and expression, the design notes above, a known-issues table, and a troubleshooting reference mapping symptoms to causes and fixes (stale connections, malformed search queries, connector schema field-name mismatches, and several Power Automate UI behaviors that silently discard edits).
That last part mattered more than it sounds. Most of the troubleshooting entries exist because I hit those failures during the build, and documenting the symptom alongside the fix means the next person doesn't have to rediscover them.
Tech Stack
Microsoft Power Automate (cloud flow), Microsoft 365 / Outlook, Microsoft Graph API, Approvals connector