Microsoft Flow – parse email and extract information

  • Post Category:Flow
  • Post Comments:58 Comments
6+

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:

6+

This Post Has 58 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+
  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

Leave a Reply