Import Email Messages to SharePoint List

14 October 2013

We have a great new provider in Data Sync that can be used to import email messages into a SharePoint List. You can even extract data from the message body and update SharePoint Metadata columns with the results.

The Email provider is for Exchange Server only (sorry no generic IMAP support) and works with Office 365 as well as On Premise.

Requires: Data Sync Release 3.0.752+ and Ouvvi Release: 2.0.264+

This is an example of using Email with SharePoint to create a simple Purchase order Request/Approval type application. Users will Email the PO request and Data Sync will import them into SharePoint where a standard Approve/Reject workflow can be used to manage the approval process.

Importing Email needs to be incremental so that we are not continuously downloading your mailbox each time. Therefore we're going to use Ouvvi to manage the project. Ouvvi will automatically add some project properties to the project when the project is run which we can use to manage the point in time where we sync from.

First Create a new Ouvvi Project

New Project

Next add a Data Sync Step

Add Step

You can now open the Project from Ouvvi

Project

Now open this project in Data Sync and connect Data Source A to the Email Provider, enter your Exchange Server Credentials and URL to the Exchange Web Services.

Connect

Once we have connected the Email provider, we need to adjust some settings. As we're interested in PO requests we are going to filter the Mailbox and only return messages who's Subject is 'PO Request'. You will also notice a FilterByRecievedDateTime field we will look at that later.

Settings

You can then Preview the Data to check that Data Sync returns the messages correctly.

Preview

Next we need to connect the Target to a SharePoint Document Library that were using to store the Email Messages. Use the SharePoint Client API provider since this has better support for SharePoint 2010 and 2013.

Connect

Choose the List 'PO Requests' from SharePoint Object Explorer.

PO Requests List

We need a minimal Map for this to work however you can extend your SharePoint List with additional columns if you want to capture more information.

Mapping

Click Compare A->B to Load your Email and compare it with what's in SharePoint.

Compare

Then Synchronise the Results, in SharePoint you will see that the actual message is stored as a document which can be opened directly from SharePoint. From here you can setup a SharePoint Approval Workflow to approve or reject the PO Request.

SharePoint

Next for Incremental Sync, Ouvvi will add the Following Properties to your Project when it's opened/run and this is where we need a little magic. What we're going to do is update the value of FilterByRecievedDateTime from the Value in the Project Properties.

Properties

Go to View->Project Automation Window to open the code editor for the automation steps.

Project AUtomation

Click enable and enter 1 line of code to copy the value from the project properties into the DataSourceA FilterByRecievedDateTime property before the project runs.

    public override void Start()
    {
     DataSourceA.FilterByReceivedDateTime = Properties["Auto_LastSuccessfulRun"];
    } 

Project Automation

Then finally set the SyncOption of the Project to SyncAtoBIncrmental to enable the Incremental support with the SharePoint Provider.

Settings

Now you can schedule this to run directly in Ouvvi by adding a Time based Trigger to the Project and watch as your PO Request emails are all fed nicely into your SharePoint List.

| |