Forward email form entries into SharePoint Lists

A common requirement for our customers is to forward emails to SharePoint Online lists. This email data usually comes from website forms or enquiry pages, though there’s no out-of-the-box way to extract the form data from an email, and upload it to separate columns in SharePoint list.

Previously I was using Cloud2050 Email Sync, though it relied on software installed on a PC to work, and only worked while that PC was operational and Outlook was open.

Here’s a solution that operates completely in the cloud using Outlook Rules, MailParser.io and Microsoft Azure Logic Apps.

The solution looks like this:

1. Office 365 forwards email from your website’s form to your mailparser.io address via an Outlook Rule or Exchange Transport Rule.
2. MailParser.io receives the email, extracts the form data and sends it to an Azure logic app using a Generic HTTP Webhook.
3. Your Azure Logic App receives the form data, connects to SharePoint Online and adds the form data into the appropriate SharePoint list columns.

Prerequisites:

• Sign up for MailParser.io – a free 30 day trial is available
• Sign up for Microsoft Azure – use your Office 365 account, a free 30 day trial is available
• A SharePoint List set up with the fields required for your form

Setting up MailParser

1. Once you’ve signed up for mailparser.io, sign in and click Create New Inbox
2. Give it a name and add some notes:
3. You’ll be given an email address to forward your form emails to. Keep track of this address, as you’ll need it to receive the emails you send from Outlook or Exchange mail rules. Forward a couple of sample form emails to the address to get started.
4. Once your emails are received, you can set up your Parsing Rules:
5. Usually, the mailparser will be able to automatically identify the field names and values from your forwarded email. If it doesn’t, click Try Something Else to give it some help, otherwise click OK, start with this.
6. Now, we start setting up our Generic Webhook. Click Webhook Integrations in on the left menu, then click Add New Integration.
   
7. Click Generic Webhook.
8. Give it a descriptive name and type in a sample URL (I used http://google.com) into the Target URL field. We need to use a sample first so that we can copy the webhook’s JSON payload. We then use this JSON payload to help generate the actual TargetURL from Azure Logic Apps in the next steps.
9. Next, click Save and test.
10. Then Send test data. We expect this to fail, though it will give us the JSON payload.
11. Copy the text from Body Payload into Notepad or Visual Studio Code.

Set up the Azure Logic App

1. Log onto Azure at portal.azure.com. If you don’t already have a subscription, you can sign up using your Office 365 account.
2. Click New, search for Logic App, and click Logic App
3. Click Create
4. Complete the fields, placing the Azure Logic App in the region of your choice. You can name the Resource group whatever you like, or use an existing one. Click Create.
5. Click Edit to start editing your logic app.
6. Search for Request and click the Request Trigger
7. Now you can use your copied JSON Body Payload from MailParser.io as a reference for your Request Body JSON Schema.You’ll need to define the data type for each Key-Value Pair in your JSON payload. This allows you to use the separate fields in your Azure Logic App, and add the field data into the appropriate SharePoint columns.The syntax of the Request Body JSON Schema is as follows:

{
    "type": "object", 
    "properties": {
        "name": {
            "type" : " string"
            },
        "email": {
            "type" : " string"
            }
    },  
    "required":["name", "email"]
} 

You can use Visual Studio Code, Notepad++ or Notepad to edit this schema so that it describes your JSON Payload.

Replace the properties values with the name of the keys in your JSON payload. Not all fields need to be added to the required array, only the ones that you need to create a valid SharePoint list entry.

In my case, this JSON body becomes the following JSON Schema.

10. Paste the Schema into the Request Body Schema and click Save.
11. You will then receive the URL that you can use in Mailparser.io to send your requests:
12. Next click + New step.
13. Type SharePoint and click SharePoint – Create item.
14. You may need to add a Connection to SharePoint Online. If you’re prompted, add a connection using an Office 365 account that has permission to write to the required SharePoint list. If you don’t have a SharePoint list available to accept the data, you’ll need to set one up now before proceeding.
15. Next enter your site URL. The List Name drop down will be populated with the available lists. You should also see that the Outputs from the Request step are available to use.
16. The list columns that can accept strings, as well as a few other column types will be available for you to modify. Click in each relevant column and select the relevant output.
17. Once you’re finished, go back to the Request Step in your Logic App and copy the URL from the Request step
18. Return to MailParser.io, go back to Webhook integrations, and click Edit.
19. Paste the URL from your Logic App Request step into the Target URL.
20. Click Save and test.
21. Click Send test data.
22. You should receive a response code of 202 to confirm it was sent successfully.
23. You can now check Azure Logic Apps to confirm that it ran correctly.
24. You should also see the new entry in your SharePoint Online list.

Setting up the Outlook Rule

Once you’ve confirmed it’s working, you can set up your mail rules in Outlook or via Exchange to automatically forward emails to your mailparser.io email address.

1. Right click on an email sent via your web form. Click Rules, then Create rule.
2. Choose a condition that matches all emails sent via your form, eg. Subject. Then click Advanced Options…
3. Click Next.
4. Tick forward it to people or public group, then click people or public group.
5. Enter the email address from Mailparser.io, click OK, then click Next twice.
6. Turn on the rule, and choose whether you want to run it on mail already in the same folder.

And that’s it. From now on, any mail sent by your website’s form will be automatically forwarded into mailparser.io, broken up into the relevant fields, and added to SharePoint Online. You can also use Azure Logic Apps to automate a bunch of other business processes. Check out the documentation here.

Similar services to Azure Logic Apps include Microsoft Flow, Zapier and IFTTT.