EmailToSharePointA 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 InboxCreate New Inbox In Mailparser.io
  2. Give it a name and add some notes:Name Mailparser Inbox
  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.Get Mailparser Email
  4. Once your emails are received, you can set up your Parsing Rules:Add Mail 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.Automatic Mail Parsing Rule Set Up
  6. Now, we start setting up our Generic Webhook. Click Webhook Integrations in on the left menu, then click Add New Integration.
    Click Webhook Integrations
  7. Click Generic Webhook.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.Save And Test Webhook With Sample URL
  9. Next, click Save and test.
  10. Then Send test data. We expect this to fail, though it will give us the JSON payload.Send Test Data With Sample URL
  11. Copy the text from Body Payload into Notepad or Visual Studio Code.Sample URL Fails, Get Body Payload

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 AppSearch For Logic App
  3. Click CreateCreate Logic App
  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.Enter Logic App Details
  5. Click Edit to start editing your logic app.Edit Logic App
  6. Search for Request and click the Request TriggerCreate 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.JSON Body In Visual Studio Code
JSON Request Body Schema

  1. Paste the Schema into the Request Body Schema and click Save.Save Request To Get POST URL
  2. You will then receive the URL that you can use in Mailparser.io to send your requests:
  3. Next click + New step.Add New Step To Logic App
  4. Type SharePoint and click SharePoint – Create item.Create SharePoint List Item
  5. 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.
  6. 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.Enter SharePoint Site And List Details
  7. 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.Add Outputs To SharePoint List
  8. Once you’re finished, go back to the Request Step in your Logic App and copy the URL from the Request stepCopy Request URL
  9. Return to MailParser.io, go back to Webhook integrations, and click Edit.Edit Webhook Integration
  10. Paste the URL from your Logic App Request step into the Target URL.Update Webhook Target URL
  11. Click Save and test.
  12. Click Send test data.Test Custom Webhook
  13. You should receive a response code of 202 to confirm it was sent successfully.Confirm Webhook Works
  14. You can now check Azure Logic Apps to confirm that it ran correctly.Logic App Runs Correctly
  15. You should also see the new entry in your SharePoint Online list.New Item In SharePoint

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.Right Click Rules Create Rule
  2. Choose a condition that matches all emails sent via your form, eg. Subject. Then click Advanced Options…Tick Subject Click Advanced Options
  3. Click Next.Click Next On Outlook Rule Wizard
  4. Tick forward it to people or public group, then click people or public group.Forward To People Or Public Group
  5. Enter the email address from Mailparser.io, click OK, then click Next twice.Paste Email From Mail Parser
  6. Turn on the rule, and choose whether you want to run it on mail already in the same folder.Turn On Outlook Rule

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.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.