Email Generator Automation

What we've confirmed

What this replaces

Today, one generic VBA routine (BuildTradeEmailFromSheet) runs against whichever of 19 worksheet tabs is active. Each tab hard-codes To/CC/BCC, a subject formula, attachment paths, and an optional delayed-send time in fixed cells. The macro displays the draft and never sends — so a live desktop Outlook session merges the user's signature, and a person then clicks Send by hand.

Where this sits in the pipeline

This design covers Steps 5–7 of the nightly run — the email being drafted, approved, and sent & archived. Steps 1–4 (files arriving over SFTP, renamed & sorted, workbooks refreshed, reports created) are covered elsewhere; they hand this step a finished report sitting in the Working library.

The email flow, step by step

One Power Automate flow per report row, triggered independently — a stall on one client's report doesn't block another's draft. These are the sub-steps inside pipeline Step 5.

How a report gets approved and sent

The Send Mode branch is the same either way — Manual (approval required, the default) or Automatic (sends without approval). What differs is how the email is built and sent, and there are two scenarios. Pick per the decision below.

The flow reads the config lists, gets the finished report from Working, and (for Manual clients) raises an approval showing a preview. On approval it composes and sends the email with the Office 365 Outlook Send an email from a shared mailbox (V2) action — recipients, subject, body and attachments in one step. No Graph and no Azure Function; sending from reporting@trailmark.us still leaves a proper Sent Items record. Trade-off: the reviewer approves a preview, not the actual outbound email, and attachments are capped at 25 MB.

This is the draft as it sits in the shared mailbox before approval. The $20.46 is an example figure; the real subject and body use that night's actual NAV and date, formatted exactly as they are now. Recipients and body reflect USVC's current setup — please correct the body wording if it doesn't match what you send today.

Workflow diagram — Scenario B (Steps 5–7)

The implementation-level view of Scenario B (editable draft via Graph): build the draft, branch on Send Mode, log the run. Each row is one pipeline step. Scenario A's flow is the simpler Outlook path shown above.

Action set — Scenario B

The concrete actions the Scenario B flow runs, in order — which tool performs each, and the endpoint or note that pins it down. (Scenario A's action set is in the section above.)

Power Automate vs. one Azure Function

Orchestration stays in Power Automate. Exactly one small Azure Function is introduced — and only where Power Automate genuinely can't do the job.

The lists you'll manage

Everything client-configurable lives in SharePoint: one row per report in Notification Assignments, one shared row in Email Sender Settings, a dated history in NAV Values, and a row per run in the Run Log. Editing any of them is a list edit — no workbook, no code deploy.

Editing who a report goes to, its subject line, or whether it needs approval is a one-row edit here — no workbook, no code.

One shared footer for every report, kept current in one place instead of relying on whoever's personal signature happened to be on the email.

Not a single overwritten "current price". Each night a new dated row per fund is appended from the end-of-day file, so a full price history builds up, and that night's value is read and merged into the subject/body. Populated by the Step 3 workbook refresh (see open items).

Appended after the email sends or an approval resolves. This is also what a nightly dashboard can be built from — what's in progress, waiting for approval, or already gone out.

Sample records — two real clients

What the lists look like with data, using two real clients from your config workbook: USVC (share price in the subject) and Champion (no share price). Recipients, subjects, and the sender are verbatim from Trailmark Support_Nightly Reports.xlsx and the sample emails.

Approver, Body Template, and Delayed-Send Time are omitted here for width — Body is a Rich Text field per row; Approver is set per client; USVC has no delayed-send set. Champion has no {{nav}} placeholder, so no price appears in its subject. Both are Manual, so both wait for approval.

Confirmed from the sample emails: reports already send from reporting@trailmark.us as one shared mailbox.

Only the funds that show a price in the email. The 7/14 values are real, taken from the sample-email subject lines (USVCX $20.46, COIDX $9.69, Connetic $10.29). The earlier USVC row is illustrative, to show the dated history building up. Champion isn't here because it doesn't show a price.

Where each old cell reference goes

A direct mapping from today's worksheet layout to the new list-driven flow, so nothing behavioral gets lost in translation.

Open items — pending confirmation

A few choices are shown with a recommended default so the design isn't blocked, but none are locked in yet.

Costs aren't covered here — if the question comes up, it's addressed in a separate document.

What doesn't change

• A person approves by default. Manual is the default Send Mode — the human checkpoint is preserved; individual clients opt into Automatic per the setting.
• SharePoint is the whole interface. Every config value and every file lives in SharePoint — nothing hardcoded in a Function or Flow, for Operations or IT to edit.
• Report, don't block. A missing attachment is noted in the draft and logged to the Run Log, not treated as a hard failure that stops everything.
• Delayed delivery is honored. DeferredDeliveryTime is still set from config, exactly as today's worksheet cell.
• Archiving still happens. The finished report is served to the email from the Working library (a folder per client) and also archived to the Archive library, in year and date folders (Archive / {year} / {date}).