Importing emails into SharePoint lists

Hi,

Being able to start off a business process from an incoming email is a great way of integrating your business applications with your internal or external customers. Although SharePoint does have the ability to import incoming emails, it's severely limited and only suitable for some specific scenarios. Moreover, when you are on Microsoft 365, it's missing completely.

When you need something robust, you have to use a tool that is generic enough and feature-rich enough to be able to handle the requirements of your real life applications. Smart Import Pro, a component of Ultimate Forms, is just the tool for the job.

Smart Import Pro offers unique features you will know to appreciate:

• You can import from any type of email server, database server or SOAP/REST web service
• You can import into any list or document library
• It works on premises and in Microsoft 365
• It allows you to map the incoming data to your list columns in any way you see fit, including using functions and conversions (more on that below)
• It allows you not just to create new items, but also update existing items (think email-based approvals) or even sync
• It's much easier to set up, use and monitor, you don't have to be a farm administrator

Let's examine the following scenario, that's quite similar to what we are doing here at Infowise:

1. You run a hosted web site, to which you have only limited access and which is located completely outside of your firewall.
2. The website contains a "Contact Us" form to collect information from visitors.
3. Visitor can enter their first and last name, company name, email and comments.
4. Currently, the form send this info to your email account in a predefined email format.
5. This information needs to find its way into your internal SharePoint-based tracking system that's inside your firewall.
6. You don't know how to connect the two system without creating major security breaches.

Let's say this is how the email looks like:

First name: John
Last name: Smith
Company: ACME Ltd
Email: jsmith@acme.com
Comments:
I really like your product, please contact me with pricing info.

What we need to do now is to establish an automated import process, that will listen to the mailbox, get the new emails coming in and then create items in a SharePoint list based on the data in those emails. But here is the catch: in the email it all appears as one blob of text, but in the SharePoint list I want to separate each piece of data into a separate column, so that the first name, last name and so one will be put in their respective columns.

Luckily, Smart Import Pro contains functions that make this process incredibly easy.

The first function we need to use is $StripHTML. Most of modern emails are in HTML format, meaning the the text you see on your screen is actually multiple invisible tags that instruct your email client (e.g. Outlook) how to display it, for example, make it bold. This information is of no use to us inside SharePoint, so we need to strip away all HTML tags, $StripHTML([Body]) gives us the email body without HTML. Easy, right?

The second function is a bit more complicated, but much more powerful. It's called $Extract and it is capable of examining some text and taking out (extracting) just the parts you need according to some predefined pattern. Take a look at our sample email again. The lines start with the label, colon, space, then comes the value itself and then a linebreak. That's the pattern we need to set in the $Extract function. For example, $Extract(<incoming text>|?First name: ^\r). Let's take it apart and you will see that it's actually very simple:

1. The incoming text, the first parameter, is the result of our StripHTML function, so it's $StripHTML([Body]). And yes, you can nest functions
2. The second parameter is the pattern itself, which is basically <prefix>^<suffix>. Both prefix and suffix are optional. When no prefix is supplied, we assume start of text. If no suffix is supplied, we assume space, line break or end of text, whatever comes first. So, in our case:
   1. Prefix ?First name: - we want to take the text that comes after First name: . The question mark means the we can be looking for First name: at any location within the text. If we omit the questions mark, the prefix is assumed to be at the beginning of the text
   2. ^ - that's what we are extracting
   3. Suffix \r  - special character notifying linebreak. Remember, if we don't supply a suffix, any space will become the delimited. People and company names can have spaces in them, so we specifically look for a linebreak.

So the end result, $Extract($StripHTML([Body])|?First name: ^\r) will produce John. Easy enough, right?

So all we need to do is map all 5 columns and we are done! Let's take a look at how it's configured in our internal system:

As you can see, we are mapping the columns exactly the way we want them. We also added a couple of conditions to only handle emails coming from our site with a specific subject line.

It only takes a couple of minutes to configure and it takes care of a business process that would otherwise require a significant development and could've exposed your system to external threats.