Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Extract failure email addresses from postmaster

  • 1.  Extract failure email addresses from postmaster

    Posted Oct 05, 2020 03:29 AM
    Dear,
    I'm struggling in extracting the failure email addresses list from the postmaster email to our portal email. These multiple failure email addresses are in email boby and text attachment as well.
    For as example:
    In the email body:

    In the text attachment:
    Original-Recipient: <email1>
    Action: failed
    Diagnostic-Code: smtp; 442 Unable to determine IP address for delivery.
    Remote-MTA: 0.0.0.0

    Original-Recipient: <email2>
    Action: failed
    Diagnostic-Code: smtp; 442 Unable to determine IP address for delivery.
    Remote-MTA: 0.0.0.0

    Original-Recipient: <email3>
    Action: failed
    Diagnostic-Code: smtp; 442 Unable to deliver message within specified time.
    Remote-MTA: 0.0.0.0

    Original-Recipient: <email4>
    Action: failed
    Diagnostic-Code: smtp; 442 Unable to deliver message within specified time.
    Remote-MTA: 0.0.0.0

    My flow is failed when I try to use Parse Json to extract each email and update the excel mailing list row table.
    Appreciate with any help please.

    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------


  • 2.  RE: Extract failure email addresses from postmaster

    Top Contributor
    Posted Oct 08, 2020 11:45 AM
    Edited by Sam Duval Oct 08, 2020 11:45 AM
    Parse JSON is not what you want. I don't believe the attachment content outputs as JSON. I think you're best bet is to create an array variable by means of splitting the attachment on a new line character compose with the value being a press of the enter key.  This will convert each line of your text file into a row as though it were a table. You can then filter that array for lines that start with "Original-Recipient" you can then split that output again with the colon ":" then filtering again for anything row containing an "@" symbol.

    If you have access to a newer (2017 maybe?) SQL database or azure SQL DB, personally I find it alot easier to do this in SQL than it is with arrays in flow. using the select value from string_split([Attachment content],Char(10))

    I use this function to then split the rows into columns:
    CREATE FUNCTION [dbo].[UFN_SEPARATES_COLUMNS](
    @TEXT varchar(8000)
    ,@COLUMN tinyint
    ,@SEPARATOR char(1)
    )RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @POS_START int = 1
    DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

    WHILE (@COLUMN >1 AND @POS_END> 0)
    BEGIN
    SET @POS_START = @POS_END + 1
    SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
    SET @COLUMN = @COLUMN - 1
    END

    IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
    IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

    RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
    END



    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 3.  RE: Extract failure email addresses from postmaster

    Posted Oct 08, 2020 02:55 PM
    Dear Sam Duval,
    Many thanks for your response.
    Could you kindly provide more details on what you said :"I think you're best bet is to create an array variable by means of splitting the attachment on a new line character compose with the value being a press of the enter key.  This will convert each line of your text file into a row as though it were a table. You can then filter that array for lines that start with "Original-Recipient" you can then split that output again with the colon ":" then filtering again for anything row containing an "@" symbol." please?
    I'm not ready for SQL.
    Thank you very much.
    Best regards,
    Nam


    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------



  • 4.  RE: Extract failure email addresses from postmaster

    Posted Oct 22, 2020 11:29 AM
    Is there further help please?
    Many thanks.
    Best,

    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------



  • 5.  RE: Extract failure email addresses from postmaster

    Top Contributor
    Posted Oct 22, 2020 03:15 PM
      |   view attached
    @Nam Nguyen here is a flow I created for this purpose. It's not the most efficient, but should give you an idea how to do it.   ​

    From the "My Flows" tab next to the "new" option, there is an option to choose import. Click that and import the below package

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------

    Attachment(s)



  • 6.  RE: Extract failure email addresses from postmaster

    Posted Oct 27, 2020 05:45 PM
    Edited by Nam Nguyen Oct 27, 2020 05:48 PM
    Dear Sam Duval,
    Thank you very much for your valuable time. I do appreciate that.
    I would like to share here for anyone who might need or amend my flow.
    I have to move the initialize variable before apply to each attachment and condition.
    P2.1: condition to find text attachment
    P2.2: formate date
    P2.3: convert text attachment =base64ToString(outputs('Get_Attachment_(V2)')?['body/contentBytes'])
    P2.4: Newline:2 empty inputs
    P2.5: Spit body line (my email in <>)so=split(outputs('Compose_-_Full_text_attachment'),'Original-Recipient: <')
    I can't make FileSplitOutput with Append to array variable because I move the initialize variable to the top of the flow
    P3.1: Pre-set var each line=union(variables('SplitLine'),outputs('Compose_-_split_body_line'))
    P3.2: Set variable Splitline=P3.1
    P3.3: @​
    P3.4: apply to each - split ',' each line =split(string(variables('SplitLine')), ',')
    P3.4.a Split each line = split(items('Apply_to_each_-_split_each_line'), '/N')
    P3.5 apply to each - Split '>' =split(items('Apply_to_each_-_split_each_line'), '>')
    P3.5.a Compose- get each email string = P3.5

    P4.1 Condition to filter email sign and not contain \n
    P4.1.a Append to string variable- Get cleaned each email string from the condition

    However, I'm struggling here to find each email above P4.1.a in my excel table. if it's matched (always false although there's), update a row, else add a row into a table.

    Many thanks again.
    Best regards,





    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------



  • 7.  RE: Extract failure email addresses from postmaster

    Top Contributor
    Posted Oct 28, 2020 08:24 AM
    is the problem that you are not getting the emails from your excel file or the comparison/filter array is not working?​

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 8.  RE: Extract failure email addresses from postmaster

    Posted Oct 31, 2020 12:16 AM
    Dear Sam Duval,
    Many thanks for your kind consideration.
    I'm not sure where is the problem.
    Hope you could help to figure it out.
    I do appreciate your help.
    Again thank you very much.
    Best,
    Nam

    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------



  • 9.  RE: Extract failure email addresses from postmaster

    Top Contributor
    Posted Nov 02, 2020 06:45 AM
    @Nam Nguyen does list all rows in table ​(step above the filter array) return all the email address you are expecting. What is you filter condition in the filter array?

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 10.  RE: Extract failure email addresses from postmaster

    Posted 30 days ago
    Dear Sam,
    Please find picture below which I don't think it return all the email address in the table.

    However, there're 4850 rows in the table but the filter array got only 256 rows.
    Thank you very much for your time. Hope you could figure it out.
    Best regards,
    Nam

    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------



  • 11.  RE: Extract failure email addresses from postmaster

    Top Contributor
    Posted 30 days ago
    For the list rows in table (excel) check the settings and turn pagination on and set it to the max value. Not sure what that is off the top of my head, but if you do 9999999999 it will error out and tell you the max allowed value. Try that and see if it solves your problem

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 12.  RE: Extract failure email addresses from postmaster

    Posted 29 days ago
    Dear Sam,
    It works finally. I greatly appreciate you taking the time to help me.
    Many thanks, Master Sam.
    Hope to study from you again.
    Best regards,

    Nam

    ------------------------------
    Nam Nguyen
    1227
    Carouge
    ------------------------------