Capturing details from a Delivery Status Notification (failure)

(0) Share

Report

Posted on by CS-27051201-0

I recently used Power Automate to send an email batch. For the failures, we get back a cisco email with an attachment.
The attachment has the email address and the failure reason.

I have a rule to move all of these emails to a subfolder in outlook.

I'd like to have a flow that will loop through all of the emails in that subfolder, get the details, add it to a row in an excel file saved to my OneDrive and then archive the email. First image shows the email with the attachment. The second shows the content of the attached email. I want to get the email address from the first line and the error message from the 3rd line.

I think I need to do this

Get all emails in the subfolder with an attachment
Use a For Each loop
- check if the attachment is a .eml
  - if so do something with the body text to parse the two lines into an array?
  - add the array as the contents of the first two columns at the end of the saved excel file
  - save the excel file

Is this basically correct? Can someone point me in the direction of some good references?

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

Vish WR 3,290 on at

Like (0)

Report
Copy link

Link copied!

@CS-27051201-0

Your overall approach is on the right track! Here is a production-safe flow design with a few important corrections to make it reliable.

CORRECT FLOW STRUCTURE:

1. Trigger: Recurrence (daily or on demand)

2. Get emails from subfolder

   Action: Get emails (V3) — Office 365 Outlook

   Set Folder to your DSN subfolder, Has Attachments = Yes

3. Apply to each email (loop emails)

4. Get attachments (inside email loop)

   Action: Get attachments (V2) — returns a list of attachments for that email

5. Apply to each attachment (nested loop on attachments)

6. Check if attachment is .eml

   Condition using:

   endsWith(items('Apply_to_each_Attachment')?['Name'], '.eml')

   NOTE: Reference the attachment loop name, not the email loop name.

7. Get attachment content

   Action: Get attachment (V2)

   This requires BOTH the Message ID AND the Attachment ID — not just the email ID.

   - Message ID: from the email loop item

   - Attachment ID: from the attachment loop item

8. Decode the attachment body

   Use a Compose action with:

   base64ToString(body('Get_attachment_(V2)')?['ContentBytes'])

9. Split into lines — USE CRLF, not just LF

   .eml files commonly use CRLF line endings (\r\n), so use:

   split(outputs('Compose_decoded'), decodeUriComponent('%0D%0A'))

   Using only %0A (LF) will work sometimes but fail on many real DSN emails.

10. Extract email address and error using MARKERS, not fixed line positions

  Fixed line numbers like [0] and [2] are unreliable because DSN format

  varies between Exchange, Gmail, Postfix, SendGrid, and SES servers.

  Use marker-based extraction instead:

  For the recipient email address:

  trim(first(split(last(split(outputs('Compose_decoded'), 'Final-Recipient:')), decodeUriComponent('%0D%0A'))))

  For the error/diagnostic message:

  trim(first(split(last(split(outputs('Compose_decoded'), 'Diagnostic-Code:')), decodeUriComponent('%0D%0A'))))

  For the status code (optional but useful):

  trim(first(split(last(split(outputs('Compose_decoded'), 'Status:')), decodeUriComponent('%0D%0A'))))

11. Add a row to Excel

  Action: Add a row into a table (Excel Online - OneDrive)

  Map extracted values to your columns.

12. Archive the email

  Action: Move email (V2) — move from DSN subfolder to Archive subfolder

QUICK SUMMARY OF WHAT TO WATCH OUT FOR:

- Always use a nested attachment loop — Get Attachment (V2) needs both

  Message ID and Attachment ID, not just the email ID.

- Use %0D%0A (CRLF) for splitting .eml lines, not just %0A (LF).

- Never rely on fixed line positions [0], [2] etc. — DSN formats differ

  by mail server. Use marker strings like Final-Recipient: and

  Diagnostic-Code: for reliable extraction.

- The endsWith() check must reference the attachment loop variable,

  not the outer email loop variable.

Was this reply helpful? Yes No
Suggested answer

RaghavMishra 40 on at

Like (0)

Report
Copy link

Link copied!
Hi,

Your plan is sound — every step you outlined maps to an existing Office 365 / OneDrive / Excel connector action documented on Microsoft Learn. Here's a reference architecture that aligns with the docs:

1. Trigger

Use a scheduled Recurrence trigger (e.g., hourly). Reference: Run tasks on a schedule.

2. Get the bounce emails from the subfolder

Use Office 365 Outlook → Get emails (V3) and set the Folder picker to your subfolder. Set Include Attachments = Yes and a sensible Top count. Reference: Get emails (V3).

3. Loop and filter to .eml attachments

Add an Apply to each over value (the email list).

Inside it, add a second Apply to each over the message's attachments array.

Add a Condition: endsWith(items('Apply_to_each_2')?['name'], '.eml'). Reference: Apply to each loops and expressions in conditions.

4. Read the .eml content

The attachment's contentBytes is base64. Decode and convert to a string with:
base64ToString(items('Apply_to_each_2')?['contentBytes'])
Reference: base64ToString function.

5. Parse the two lines you need

Initialize a string variable with the decoded body.

Use split(variables('Body'), decodeUriComponent('%0A')) to split into lines, then index [0] for the email and [2] for the error reason. Reference: split and decodeUriComponent.

6. Append to the Excel table on OneDrive

Use Excel Online (Business) → Add a row into a table. Make sure the destination is a real Excel table (Insert → Table) — the connector won't work against a raw range. Reference: Add a row into a table.

7. Archive (move) the email

Use Office 365 Outlook → Move email (V2) with the message ID and target folder ID. Reference: Move email (V2).

Tips

Set Excel "Add a row" actions to run sequentially (concurrency control = 1 on the parent Apply to each) — concurrent inserts against the same table commonly hit InsertDeleteConflict. Reference: change loop concurrency.

Add retry policies on Excel actions.

Found this helpful? Please mark ✅ "Does this answer your question?" so others searching for the same issue can find it quickly. A 👍 on "Was this reply helpful?" or a ♥ Like is also much appreciated!

Raghav Mishra — LinkedIn | PowerAI Labs

Was this reply helpful? Yes No