Microsoft Flow – parse email and extract information

  • Post Category:Flow
  • Post Comments:76 Comments
7+

If you have a 3rd party system that sends standardized notifications and you would like to automatically process those notifications, then this Flow can be helpful. The flow below parses a predefined email, extracts information for you to take further actions with it.

Those actions can be:

  • saving data into a SharePoint List;
  • forwarding part of an email to another system;
  • removing all formatting and so on.

The flow is pretty simple and consists of these steps:

1) Trigger – When a new email arrives.

2) Action – Html to text – convert email body from HTML to plain text.

3) Action – Initialize variable – store a carriage return value in it. Peek code view:

{
    "inputs": {
        "variables": [
            {
                "name": "CRLF",
                "type": "String",
                "value": "\n"
            }
        ]
    }
}

4) Action – Compose – split output from #2 into an array. Expression:

split(
    trim(
        uriComponentToString(
            replace(
                uriComponent(
                    body('Convert_email_Html_to_Text')
                ),
                '%0A',
                '~~'
            )
        )
    ),
    '~~'
)

5) Action – Filter – remove empty lines from #4. Code for Edit in advanced mode:

@not(equals(item(), ''))

6) Action – Filter – find all lines that start with NAME word. Code for Edit in advanced mode:

@startsWith(item(), 'NAME')

7) Action – Filter – find all lines that start with EMAIL word. Code for Edit in advanced mode:

@startsWith(item(), 'EMAIL')

8) Action – Filter – find all lines that start with PHONE word. Code for Edit in advanced mode:

@startsWith(item(), 'PHONE')

9) Action – Compose – combine and output #6, #7, #8. Expression:

concat(
	body('Get_NAME_line')?[0],
	variables('CRLF'),
	body('Get_EMAIL_line')?[0],
	variables('CRLF'),
	body('Get_PHONE_line')?[0]
)

Please note how I’ve used three different ways to show the code as one works better than the other in each situation – Expression, Edit in advanced mode, and Peek code.


UPDATE 8/5/2020:

Since I’ve been getting similar questions and issues reported in the comments, it might be better to post screenshots with more consistent look (not how I used Expressions, Peek code, Edit in advanced mode above):

Step 3

Step 4

Step 5

Step 8

Step 9


RESULT

This is how a sent message looks like – it has an image, a bit of formatting, some extra info (that should not be processed per the flow):

And here is the result of the flow only extracting what it needed to:

7+

This Post Has 76 Comments

  1. Ben Hiron-Grimes

    thank you very much for this, very useful
    When I test it I get an error on step 4:

    BadRequest. The ‘from’ property value in the ‘query’ action inputs is of type ‘String’. The value must be an array.

    please advise

    2+
    1. Pavel Bludov

      Hi Ben,
      I will need more info, so I am about to email you.
      Thanks!

      0
  2. Hunter

    I could use some help as well, Im dealing with something similar.

    0
    1. Pavel Bludov

      Hi,
      Sure, I can try to help – about to email you.

      1+
      1. Nick

        Hi Pavel,

        I am wanting to do exactly as you are mentioning in the article, basically wanting the body of the email below, into a sharepoint list under the correct columns. just having trouble getting the flow right in order to extract the data correctly. Any help would be very much appreciated. Here is the example of the email which comes through:

        username:74138
        password:funlove37
        description:Future Parent (Senior School) title:Future Parent
        email:
        salutation:Mr
        givenname:Mark
        surname:Darng
        addressLine1: 87 Wine Terrace
        suburb:MODBURY
        state:SA
        country:_
        postcode:5000

        And the list is basically needed in those columns in a sharepoint list online

        0
        1. Pavel Bludov

          Hi Nick,
          All you need to do is use my example and add one more step for each line you would like to process: Replace(‘Previous_Action_Here_That_Finds_username:74138′,’username:’,”)
          So what this will do is remove “username:” from the line leaving you with pure data such as username value, password value, etc.
          Technically you don’t need to have that many Compose actions, they are just much easier to deal and debug with.
          Please let me know if any questions.

          0
          1. Aaron Green

            I tried this and it didn’t work:
            replace(body(‘Get_Name_Line’),’Name ‘,”)

            I am only a beginner here so I’m probably missing something, but I don’t know what. Please help.

            0
          2. Aaron Green

            Can you give specifics as to how the Replace works? I don’t quite understand your note.

            0
          3. Pavel Bludov

            Hi Aaron,
            Replace(‘text value in which to search and replace something’,’what to replace’,’value to replace with’)
            E.g. Replace(‘Name: Aaron’,’Name: ‘,’’) will result in ‘Aaron’ as an output.

            0
          4. Aaron Green

            In your explanation what is “‘Previous_Action_Here_That_Finds_username:74138′”? What would be the working solution in your example flow above?

            0
          5. Pavel Bludov

            Aaron, I will reply to your email.
            Update: Aaron is all set. He needed to use a formula similar to this:
            Replace(body(‘Get_NAME_line’)?[0],’Name ‘,”)

            0
  3. Amandaoldham

    I did trial and error for 10 hours. This article finally helped. Thanks!

    0
    1. Pavel Bludov

      Hi Amanda(?),
      Thanks for stopping by, good to hear!

      0
  4. Steven

    Hey Pavel,
    Do you know a way to extract a particular value from a message in teams.
    MessageCards do not allow a mention to work so I was thinking of parsing out a persons name between two values <> and then having a flow mention that NAME in the same channel to say there is a message they need to look at. The messages arrive via the O365 connector and it HAS to use MessageCards.

    0
    1. Pavel Bludov

      Hi Steven,
      Did you mean an Adaptive Card?
      Can you also please give more details on your case – how you would like it to work. Not sure I understood the concept.
      Thanks!

      0
  5. Karl

    Hi Pavel,

    I got stuck at 3 Action – Initialize variable – store a carriage return value in it. Peek code view:

    The Peek code view wouldn’t allow me to edit the code – I presume I need to install an editor?

    0
    1. Pavel Bludov

      Hi Karl,
      You will be surprised how trivial that step and value is. What you do is put your mouse cursor into that value field, press Enter on the keyboard so it adds the line/carriage return. That is it.
      Thanks!

      2+
      1. Czarina

        HI Pavel! When do we use this step CTRLF? i mean, i can’t find it’s correlation in step 4-9?

        0
        1. Pavel Bludov

          Hi Czarina,
          It’s only used in the Step 9 to combine some outputs and separate them with a carriage return.
          Thanks!

          0
  6. Mikola

    On number 4 I got an error, the same one as Ben Hiron-Grimes, has anyone found a solution for that issue?

    0
    1. Pavel Bludov

      Hey Mikola,
      Replying to your email for more details.
      Thanks!

      0
  7. Kevin

    I am also having the same error on step 4, as Ben and Mikola

    0
    1. Pavel Bludov

      Hi Kevin,
      Mikola had other issues and I believe he is all set.
      Step 4 is where you split your data into an array. As long as your HTML to Text works well (check the output of it) and CRLF is correctly initialized (it’s just a carriage return stored in it), then Step 4 should have no errors.
      Also make sure that step is Compose type action.

      0
  8. Franky

    Hello and thank you for your article. It is right what I wanted.

    I tried couple of time but could not complete it. 🙁

    Step 4 a 5 !!

    0
    1. Pavel Bludov

      Hi Franky,
      I’ll send you an email to get more details. Otherwise, hard to troubleshoot.
      Thanks!

      0
  9. Ram Soni

    I got the same error on number 4 as Ben Hiron-Grimes mentioned. Could you help me on this?

    0
    1. Pavel Bludov

      Hi Ram,
      I think most if not all issues have been resolved posted in the comments. So I’m about to send you an email for more details.
      Thanks!

      0
  10. Marcel

    ;Hi Pavel,

    I recon i doing something wrong but don’t know what. I cant get step 4 right when i add the compose it gives me inputs and i do not see how to change that in splits or something.

    0
    1. Pavel Bludov

      Hi Marcel,
      I’ll reply through an email.
      Thanks for checking my site.

      0
  11. Emilio

    I was just looking for a way to identify an email address in a forewarded message so that I can take it and work with it. Your post sets me in the right direction..Thanks. It is not pretty but it works.
    As far as you know, is there a way to identify strings through regular expressions? It would have been way more easier that way.

    0
    1. Pavel Bludov

      Hi Emilio,
      I wish there were a better way but it looks like currently it’s either using these not so pretty workarounds or using 3rd party actions (e.g. Plumsail).
      Thanks!

      0
  12. Dave

    Hey Pavel
    Thanks for putting this up for us. Could you please put an image up that shows each of the edit steps expanded? I am trying to create the same parameters as you show in step 3&4 (for example) however I can’t seem to edit it the same way. I appreciate the peak codes and such but rather than showing me what I’m doing wrong it just tells me I’m doing something wrong.
    Thanks in advance

    0
    1. Pavel Bludov

      Hi Dave,
      Good suggestion! Please see the updated post.

      1+
  13. Sean Holland

    Hello Pavel , I am getting errors on the “Compose Output” step.

    Error states:
    “Correct to include a valid reference to ‘Get_NAME_line’ for the input parameter(s) of action ‘Compose_Output’.

    I get 2 more errors just like this for the other inputs “EMAIL” and “PHONE”.

    Any Advice?

    Best Regards,
    -Sean

    0
    1. Pavel Bludov

      Hi Sean,
      Please make sure all steps have exactly the same name as in my example; otherwise if you don’t (which is totally fine), you will need to rename those within steps/code as well.

      0
  14. Aaron

    I have set something up very similar to this and it works great. I am wondering though if it is possible to get multiple lines of text and save it to a string? I know what the line will start with but it can span up to 255 characters and the parser will only pick up the first line.

    0
    1. Pavel Bludov

      Hi Aaron,
      Thanks for stopping by!
      Why not using Join() function in the Expression or am I missing something? Please let me know.

      0
  15. Dave

    Thanks Pavel for including the steps in images, it has helped a lot. The only thing I need to find now is how to compose the output without the “NAME ” part. I know it’s going to be stupid easy but I have been wrestling with this last hurdle too long.

    Needed Output:
    Pavel Bludov

    Instead of:
    NAME PAVEL Bludov

    0
    1. Pavel Bludov

      Hi Dave,
      There are several methods to achieve what you need:
      – substring() function
      – replace() function
      Replace is probably the easiest:
      Replace(yourTextValue,’Name ‘,”) – two single quotes here at the end.

      1+
  16. Emmanuel

    Hi,

    I am having issues with step 4, please can you help?

    0
    1. Emmanuel

      now working, NVM, thanks for this though massively helpful

      0
      1. Pavel Bludov

        Hi Emmanuel,
        Great, time heals! 🙂
        Let me know if any questions.

        0
  17. Carsten

    HI Pavel,
    This is a great help, and thank you for posting this.
    In my example, I receive automated emails containing a list of names as such:

    Files list:
    Filename 1
    Filename 2
    Filename 3

    I would like to print these filenames to a spreadsheet, but they are not preceeded by “Name” or something similar. Any suggestions?

    0
    1. Pavel Bludov

      Hello Carsten,
      When splitting data into an array I would think that you can calculate the position of each neighboring line (in my example below those would be “Files List:” and “Phone:”). After getting their index you would be able to get lines in between them.
      Files List:


      Phone:

      0
  18. Bharath Kumar

    HI Pavel,
    I have similar issue with retrieving user name from the email address and assigning that as reporter to my JIRA story. Everytime i submit a formvia msform, flows will retrieve the data and should create an issue/story in my JIRA. Wherein my flow breaks at creating the story with follwing error:

    {
    “errorMessages”: [],
    “errors”: {
    “reporter”: “Account ID does not exist.”
    }
    }

    0
    1. Pavel Bludov

      Hi Bharath,
      Could you maybe send me an example of your email with all private data disguised or adjusted?
      I’ll reply to your email so that you can reply back.

      0
  19. Dominic

    This is awesome. While i’ve almost got the end result i’m looking for, my issue is that the carriage return (#3 and consequently #9) do not appear to function. The result is that it does not insert the line break.Thoughts?

    0
    1. Pavel Bludov

      Hi Dominic,
      What does your “Peek code” show for Step 3?
      Thanks!

      0
      1. Dominic

        Identical to yours! (just looked at it again). That is what has me scratching my head. Also looked through my concat statement in #9 again, also mimics yours (my action names are slightly different of course).

        0
        1. Dominic

          Answering my own question 🙂
          I added another action “append to string”, then chose my CRLF variable and appended
          Now i get the correct formatting.

          0
          1. Dominic

            appended “”

            0
          2. Dominic

            appended the html break (posts don’t like that !!)

            0
  20. Cory

    Pavel,
    Is it possible to get Power Automate to repeat these steps for similar values in a single email? For example, the message I receive from our 3rd party system might look something like what I’ve listed below, with a dozen listings or more. I can get it to deliver each output in the Filter Array action, but then I can’t get it to actually place each line into Excel; only the first or last in the list.

    Name: Cory
    Email: cory@email
    Phone: 555-555-5555

    Name: Pavel
    Email: pavel@email
    Phone:555-555-5556

    0
    1. Pavel Bludov

      Hi Cory,
      That shouldn’t be hard to do.
      First you calculate the length of your array, e.g. array of lines that start with “Name:”.
      Then you create a variable “i” that starts with zero. You increment it by 1 until that number of elements above.
      That is all within “Do until” action.
      I am using a similar concept in this blog post – https://365basics.com/microsoft-flow-extremely-flexible-email-merge-without-any-hardcoded-mapping/
      It is all assuming the length of your arrays of Names, Emails, Phones is the same.

      0
      1. Cory

        Thanks Pavel! I’ve gotten really close on this, but still can’t get it to deliver the output to Excel for each line of the array. I’ve added the variable “i” and the Increment Variable steps, all wrapped within a “Do until”. It feels like the answer is within the “Add a row to Excel” step. Shouldn’t it just be delivering the output of the Filter array step? I don’t see that as an option in dynamic content, and when I Compose it first, that output includes all of the lines for the array on each row in Excel. I’ve been pulling my hair out figuring I’m close, but I can’t get it across the finish line. What am I missing!?!

        0
        1. Pavel Bludov

          I’ll send a message to your email.

          0
  21. David Torres

    Pavel. This is great. Hoping you can help. I get a notification from a certain platform and they come in with a generic noreply@company.com email. However when you hit reply they go to user1@company.com. im trying to setup flow to auto reply to this email but it keeps going to noreply creating a bounce back. I was working on parsing the mailto from body of email. Im new to this so I can get but so far. Was using first(split(variables)) type of expression and was getting somewhere. However i can’t figure it out. In the body of email there’s a cpl mailto: with emails . Mailto: with the email I need shows up like 4 times and mailto: with the email sending me a notification shows up once.

    I just need mailto:user1@company.com

    Id really appreciate your help

    0
    1. Pavel Bludov

      Hi David,
      I’m not seeing a reply to property in the body of the email. Where do you get it?
      I’ll send you an email so that maybe you can reply with your data/example.
      Thanks!

      0
  22. Ian

    Hi

    this is great stuff, New to flow, but have a bit of VBA experience. This really helped me make large steps in a small amount of time.

    thanks

    1+
    1. Pavel Bludov

      Hey Ian,
      I’m glad it was helpful, thanks for stopping by!

      0
  23. Corey

    Hi Pavel. I don’t have a lot of experience so I love this walkthrough and I am having some trouble with step 4. When trying to input the split variable, I get an error, “The expression is invalid.” I am hoping you have some time to give me some insight into what I need to do to correct it.

    0
    1. Pavel Bludov

      Hi Corey,
      What code exactly do you have in this step?
      Thanks!

      0
      1. Corey

        Hi Pavel,

        Got through step 4, now stuck on step 9 with the output. I’m using a “Getting Source” naming convention for the action filter but am getting a “The expression is invalid.” error with the below.

        concat(body(‘Gettting_Source’)?[0],variables(“CRLF”))

        0
  24. Noelia Guzman

    Hi Pavel,
    I’m also having issues with Step 4. I confirmed I initialized the variable (CRLF) correctly because my peek view code looks like yours. In the next step, I make sure to choose compose and then choose Split and the other expressions from the Expression list but my peak code does not look like yours and this is what’s causing my flow to fail. Please help! This is what my peak code looks like for step 4
    {
    “inputs”: “@split(trim(uriComponentToString(replace(uriComponent(outputs(‘Html_to_text’)?[‘body’])))))”
    }

    FYI – My email contents looks similar to yours. I just need to extract the 4 items listed (Score, Name, Email, Dept.)

    A user has completed their daily questionnaire.

    Score: Passed
    Name: John Doe
    Email: johndoe@email.com
    Dept: Sales

    To manage these emails, log in to your account.

    0
    1. Pavel Bludov

      Hi Noelia,
      I think your code
      split(trim(uriComponentToString(replace(uriComponent(outputs(‘Html_to_text’)?[‘body’])))))
      is missing some important parts to it. Please check my post, especially the bottom part of it where I did a better explanation and more screenshots.
      Thanks!

      0
  25. Aleksander

    Thank you so much for this, it saved us a bunch of time and worked like a charm!

    0
    1. Pavel Bludov

      Thanks for stopping by, Aleksander!

      0
  26. Gavin Keith

    Hey Pavel,

    I’m getting a similar error to someone else earlier on step 4. I’m getting BadRequest. The ‘from’ property value in the ‘query’ action inputs is of type ‘String’. The value must be an array.
    Any idea why this would be happening? Thanks!

    0
    1. Pavel Bludov

      Hi Gavin,
      At the bottom of my post I have more screenshots, have you seen those?
      As long as you used a “Compose” action rather than a “Filter” one I don’t see any reason for that error message.
      That step/action splits a string into an array.

      0
  27. Ryan

    Hi Pavel,

    Thank you so much for this.
    After much trial, I have manage to get most of the action working and need some help with the following issue.
    During HTML to Text conversion, my output is skew hence affecting the final result.
    eg.

    email html format
    Arrival:11/12/2020 2:00 PM
    Departure:11/12/2020 4:30 PM

    HTML to Text format
    Arrival:11/12/2020 2:00 PMDeparture:11/12/2020 4:30 PM

    Any advise on how to resolve this?

    much appreciated.

    0
    1. Pavel Bludov

      Hey Ryan,
      Well for that particular example you can you indexOf() function to find where “Departure:” text starts. Then only extract info starting from “Departure:” using Substring(). It’s not the best workaround though.
      If things are more complicated than what you showed us, then it will require more digging into the subject.

      0
  28. Sebastian

    Hi Pavel,
    Not sure if you can help me out. I have an email with a PDF attachment and some HTML plain text in the body of the email. Is there a way in which Power Automate can save the attachment into a SharePoint list and parse the HTML info into columns on the item on that list?
    Thanks!

    0
    1. Pavel Bludov

      Hi Sebastian,
      The short answer is yes but there are lots of moving parts you will have to take care of:
      – filter out all other attachments (images from signatures?) and leave just a single PDF to be saved;
      – save PDF correctly encoded;
      – convert email from html to plain text;
      – parse plain text content;
      – save info either into a SharePoint list and its columns with the file attached or doing the same with a Document Library (the Document Library will need to get extra columns/metadata).

      0
  29. Ryan

    Thank Pavel.

    I will try in out. Cheers

    0
  30. Neil A

    Hi Pavel, I am trying to set this condition up and I am getting stuck on number 4, the text is exactly as you have written but but I get the error “Correct to include a valid reference to ‘Convert_email_Html_to_Text’ for the input paramater of action ‘Splitting_email_into_array’…….I also am struggling to figure out my next steps of how I can automate the text it pulls to appear in new rows in an excel table. Kind Regards, Neil

    0
    1. Pavel Bludov

      Neil,
      Please reply to me through email. Thanks!

      0

Leave a Reply