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

Extracting email contents to Sharepoint

Jump to Best Answer
  • 1.  Extracting email contents to Sharepoint

    Bronze Contributor
    Posted Sep 09, 2019 11:23 AM
    Please help!

    We've got a system that auto generates email when people have submitted a course.

    Body of the email looks a bit like this:

    Name: John Smith
    Certificate: ICT mandatory Course

    How can I use flow to extract the information and automatically create a sharepoint list item?

    Thank you

    Would you like to be kept up to date with Blackpool Council news? Sign up here - www.blackpool.gov.uk/YourBlackpool http://www.blackpool.gov.uk/EmailDisclaimer/ This message has been scanned for inappropriate or malicious content as part of the Council's e-mail and Internet policies.

  • 2.  RE: Extracting email contents to Sharepoint
    Best Answer

    Top Contributor
    Posted Sep 10, 2019 05:03 AM
    Trigger when an email is received based on whatever criteria is unique to the emails (sender, subject etc). Use the HTML to text action to convert the body of the trigger if necessary then use the substring() function in compose actions to extract the relevant text. See my reply, message 12 of 12 in this thread. Finally create item and add an email step to run after your Create item has skipped or failed so you know if it hasn't worked.

    Will Page
    Technical consultant

  • 3.  RE: Extracting email contents to Sharepoint

    Posted Sep 10, 2019 09:59 AM
    What if your company block "html to text". What would be the workaround?

    Rosemary Edmunds
    IT Business Analyst
    Houston TX
    832 341 6253

  • 4.  RE: Extracting email contents to Sharepoint

    Top Contributor
    Posted Sep 10, 2019 01:45 PM
    What a strange thing to block.

    In that case you will need to examine the output of the trigger: Create a 2 step flow with the trigger then put the body of the email in a compose, run it then look in the raw HTML in the compose action. The text you want to extract might be within HTML tags. If so, you will simply need to include the HTML tags in the indexof() functions within the substring() function.

    I'd suggest asking your IT department what the rationale behind blocking that connector is, just out of curiosity.

    Will Page
    Technical consultant

  • 5.  RE: Extracting email contents to Sharepoint

    Bronze Contributor
    Posted Sep 10, 2019 10:35 AM
    You, sir, are a life saver! Thank you so much!

    Kassie Navarasi
    TV Studio & Digital Media Manager

  • 6.  RE: Extracting email contents to Sharepoint

    Bronze Contributor
    Posted Sep 11, 2019 11:05 AM

    Hello! So the fix worked however. html to text is removing lines on my email. This is return breaks up the full name. Because of this, it add an extra "/n" in between the first and last name and causes issues when trying to create an item on sharepoint under the "person" column.

    Kassie Navarasi
    TV Studio & Digital Media Manager

  • 7.  RE: Extracting email contents to Sharepoint

    Posted Sep 10, 2019 04:00 PM
    Hi Kassie,

    I do something similar. Here are the first steps of the Flow:
    I have a rule in Outlook where certain emails I want to process go into a folder and my Flow is triggered off the arrival of an email. This is followed by converting the Body with Html_to_text. The next step is a little odd but was a tip I found after searching - create a variable named here as CRLF The input is made by hitting enter between double quotes.

    The next step is the real work where the desired fields are parsed out of the email structure into a JSON structure.
    Here are the the functions which search for fixed text strings and use offsets from those to pull the desired fields:
    first(split(trim(substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'s AGA ID'),20),5)),outputs('CRLF')))
    substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'of game'),19),10)
    substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'r white'),19),1)
    first(split(trim(substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Your AGA ID'),23),5)),outputs('CRLF')))
    substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Who won'),19),1)

    Same idea for all of them but vary depending on what is being pulled. The one's that use CRLF are pulling a string of 5 or less characters at the end of a line, and when it is less need to split the piece before the CRLF.

    I don't put these into SharePoint (but you could). Here is an example in a later step where one of the fields is referenced and concatenated with another string:


    Hope this helps,

    Gurujeet Khalsa
    Cloud Solution Architect
    PBI Microsoft

  • 8.  RE: Extracting email contents to Sharepoint

    Posted Sep 10, 2019 04:14 PM
    Hi Kassie

    We have a process my which we monitor our inbox for the receipt of an email from a specific user and move the attachments to a defined location in sharepoint which follows a similar process. To achieve what you are looking for, create an automated flow, using the trigger of "When a new email arrives". Specify the folder the mail will be received in to (the default for this is usually the inbox) and from the advanced options you can then enter additional information such as the  "from" or if its possible from the email you currently receive, any specific phrases you would expect to see in the subject line (there doesn't appear to be a way of checking the body). Once you've set that up, add an action for "Create Item" from the sharepoint connections. You can then specify the sharepoint site and list and map across the information from the email to the fields of the list. That should be it but if you get any issues give me a shout and I'll help where I can

    Kelvin Cowell
    PE1 5FQ
    Caterpillar Inc.