Update Sharepoint List with Specific Values from Outlook Email

[Anonymous]

I am trying to add new rows into this Sharepoint list whenever a shared inbox receives an email (the emails are inputs coming from a form to register for interest).

So basically, I have to add the respective data into the correct columns in the list, e.g. Response ID value from email will be plotted into the Response ID column in the list.

This is how the list looks like:

And this is how the emails look like:

How do I get the specific data that I want from the email into the Power Automate fields below?

Any help will be much appreciated, thanks! 🙂

[Ajinder31 · Accepted solution]

Hi @Anonymous , We can do something like below to extract content from email body.

1. Html to text action - to convert HTML body to plain text.
   
2. Find Text Position action to get index of a field. For eg. Response ID
   
3. Find Text Position action to get index of field next to Response ID i.e Timestamp
   
4. Substring action - To retrieve string in between two fields i.e. Response ID and Timestamp field positions.
   Starting Position will be - Index of field + Length of field name. 

add(outputs('Find_text_position')?['body'], length('Response ID'))​

Length will be - Subtraction of next field position - Starting Position

sub(outputs('Find_text_position_2')?['body'], add(outputs('Find_text_position')?['body'], length('Response ID')))

Above steps are to only extract one field. We need to repeat same steps to retrieve each required field.

--------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

[Anonymous]

Thank you!

After that, I just put in the value of substring into the Sharepoint "Create item" fields right?

[Ajinder31]

Right @Anonymous , I will also suggest to trim the response as I noticed whitespace present in the email content. We can use trim expression to do that.

[Anonymous]

I could use this method for the Response ID field, but when it reaches the Timestamp field this error pops up:

The timestamp I'm retrieving from the email is like this:

[Ajinder31]

Hi @Anonymous , Please share a screenshot of your flow and how Substring action configured. It seems Substring Length value is incorrectlly configured.

[Anonymous]

starting position -> add(outputs('Find_text_position_2')?['body'], length('Timestamp'))

length -> sub(outputs('Find_text_position_21')?['body'], add(outputs('Find_text_position_2')?['body'], length('Timestamp')))

Flow

Create item fields

[Ajinder31]

Hi @Anonymous , Expressions looks good on quick glance. Please check output of substring action in flow history and share screenshot of it as well. I will give it a try on my side as soon as get chance.

[Anonymous]

It doesn't show any output and only gives this error:

Thank you 🙂

[Ajinder31]

Hi @Anonymous , I tried it on my end and it seems to be working as expected.
Please share output of actions before the substring actions showing the content extracted, Position of the Timestamp and Please tell us about yourself fields.

[Anonymous]

Flow:

Output for Timestamp

Output for Please tell us about yourself

Not sure why the second output is -1 since the text I searched for is exactly the same as in the email.

I think it might be because the timestamp contains multiple words, instead of a single string like in response ID. I'm not sure though.

[Ajinder31]

Hi @Anonymous , Please confirm if there is no extra space added in case of Please tell us about yourself Or probably only try with only Please tell us. I believe once we resolve this position issue it work fine for Timestamp.

[Anonymous]

Hi, I tried with "Please tell us" only and the error is gone. But the output for the Substring is everything after "Please tell us".

[Ajinder31]

Hi @Anonymous , Please share output of previous actions. The starting position seems okay but Length is still incorrect.

[Anonymous]

The first steps are getting the email and converting HTML to text, which works fine.

After that it's these following steps:

Find text position 1:

Substring 1:

Length and output for these are correct.

----------------------------------------------

Find text position 2:

Substring 2:

The output is everything in the form after Please tell us about yourself.

So the output is wrong and does not fit into the Sharepoint field.

Thank you

[Ajinder31]

Hi @Anonymous , The problem is the Length value passed i.e. 1584. Can you please check the email text and confirm there are not multiple instance of searched text "Please tell us" ? If Yes, Please change the find text accordingly to get position of correct text field.

[Anonymous]

Hi I solved it by using "Please" only and it worked!

Thank u so much