Introduction

In this article, we will see how to use Power Automate to extract data from specific placeholder locations within the body of an email and use the data to create SharePoint items in a list where content approval is enabled.

Business Use case

We have a shared mailbox where the marketing team would send details of the new customers, their product, and the cost of the product. Based on the received mail, upon approval of the new customer, the procurement team will go ahead and raise a Purchase Order for the item from that customer/vendor.

Implementation

We will first create a Shared mailbox which will act as the receiver of the mails that contain the customer information. We can create this by heading over to SharePoint Admin Center -> Teams & Groups -> Shared mailboxes. Specify the name of the mailbox and create a new shared mailbox.

We can as also add members to the newly created Shared Mailbox. Members are the people who will be able to view the incoming mail to this shared mailbox, and the outgoing replies.

I have added myself as a member to the shared mailbox and as per requirement, we can add more members as well.

In case you want to visit the mailbox to check on the incoming mails, it’s a good thing to reset the password first by going to the user’s list where the newly created shared mailbox will be listed

As we have the shared mailbox in place, let’s create a Power Automate cloud flow that will get triggered when a mail is received by the shared mailbox.

Before proceeding, let’s create 3 variables that will hold the values of the Customer Name, Product, and Cost

To process the email body, we will convert the HTML body into plain text using the HTML to text action. To this action, we will pass on the Body output of the trigger

The plain text cannot be passed through a couple of compose actions to split the text based on delimiters to derive the values of Customer Name, Product, and Cost. First, let’s take a look at the sample mail that we will be using in this demo :

So, the text is:

Hi Procurement Team, Please find the details of the new customer : Customer Name : Vattenfall Product : Industrial Water Pump Cost : 2000 USD

So as to fetch the customer name, we will first add a compose action and split the text using the delimiter “Customer Name :” .

split(outputs(‘Html_to_text’)?[‘body’],’Customer Name :’)

This will split the text into an array with Index 0 and Index 1. The respective values in the indices would be:

Index 0	Hi Procurement Team, Please find the details of the new customer :
Index 1	Vattenfall Product : Industrial Water Pump Cost : 2000 USD

The runtime output from this action will look like below:

To pick the Customer Name value, we will again do a split using compose action on the value in Index 1 of the above action’s output. This time, the delimiter would be “Product :” which will get us the value of customer name “Vattenfall”

split(outputs(‘Compose_-_Split_by_Customer_Name’)[1],’Product :’)

The split expression will further split the text and place the content in Indices 0 and 1 as :

Index 0	Vattenfall
Index 1	Industrial Water Pump Cost : 2000 USD

Thus, we have obtained the customer name in the Index 0 of the Output from the above action. We will trim the output to cut off the trailing white spaces and save them into the customer name variable

trim(outputs(‘Compose_-_Split_by_Product’)[0])

Let’s do one final split using another compose action. This time the delimiter would be “Cost :”

split(outputs(‘Compose_-_Split_by_Product’)[1],’Cost :’)

This will split the remaining text into an array with the below values in Index 0 and 1

Index 0	Industrial Water Pump
Index 1	2000 USD

Thus we have the Product and Cost values in Index 0 and 1 of the Output of the above action (Compose – Split by cost). We will add the Product value to the variable by enclosing it within the Trim function to cut off the trailing white spaces

trim(outputs(‘Compose_-_Split_by_Cost’)[0])

Same way the cost value is present in the index 1 of the Output of the Compose – Split by cost action and we will trim out the whitespaces and add it to the cost variable

trim(outputs(‘Compose_-_Split_by_Cost’)[1])

Finally, let’s use the extracted values and create the item in the SharePoint List

As part of the business process, the extracted mail data which has been created as a SharePoint list item will have to go through an approval process for a final review validation and hence let’s add the Start and wait for approval action and add the link to the item for approval as below:

This will generate the approval mail to the approver for approval/rejection of the item.

Based on the approval/rejection, we will set the content approval status in the SharePoint List

Testing the flow

So as to test the flow we will send a mail to the Shared Mailbox with the values filled in for the template fields: Customer Name, Product, and Cost

The flow has run successfully by extracting the mail contents, creating the list item, and kickstarting an approval process. On approval, the content approval status has also been updated. The entire flow looks like below:

The Data has been created and approved in the list as well:

Summary

Thus, we saw how to use Power Automate to extract the data from an incoming email using Compose and Split actions to isolate the placeholder-specific data in a templatized mail and use the extracted data to create records in SharePoint List where content approval is enabled.