Applies To: SharePoint Online
Description
Crow Canyon Email Sync App is used to sync an Exchange Mailbox to an announcement list in O365. We can map the title, body, sender and importance etc. of the email to columns in the announcement list. Please refer Email Sync App manual for more details.
In addition to these standard email attributes, this App can parse the email body to extract information with specific format from the content and put it in corresponding SharePoint list columns.
This capability can be used wherever mails are generated by other applications. For example, a web form may take user input and then generate a mail with structured content and send to Email Sync mailbox. Or monitoring systems can generate an email with details of problem to generate a Ticket with required data.
Email Sync supports some common data formats like “name : value” pairs in the mail body. Also, regular expressions can be specified to parse any kind of custom formats.
Summary of steps
- Configure the SharePoint list schema
- Configure Email Body Parsing
- Integration of parsing in our Crow Canyon Service Request applications
Detailed Steps:
1. Create the SharePoint list and columns
In order to configure Email sync in the application, we can create an Announcement type list in the SharePoint site. This gives us standard email columns to map. After this, create more columns based on information to be extracted from the mail body.
Supported column types and formats are listed below
| Column Type | Supported Format |
| Single line of text | Free form text (up to 255 characters) |
| Multiple lines of text | Only plain text format is supported |
| Choice | Valid choice options configured in SharePoint column settings |
| Number | Valid Number (Decimal values like 8.35 also supported) |
| Currency | Number (currency sign should not be present in mail body) |
| Date Only | MM-DD-YYYY |
| Date and Time | MM-DD-YYYY HH:MM |
| Lookup | Display Name/Lookup ID |
| Yes/No | True/False (Yes/No as values – Not supported) |
| Person or Group | Display Name/User Id |
| Hyperlink | URL, Display Name |
2. Configure email body parsing – Predefined formats
Email Sync supports two types of pre-defined parsing formats for the mail body as described below.
i. Pre-defined format “<List Column Name>:<Value>”
In this format, email body has content starting with name of the SharePoint list column followed by a colon and then the value of that column. For example:
Category:Network Issue Type:VPN
Here, “Category” and “Issue Type” are the display names of the columns in the SharePoint list for which information is present in the mail body. “Network” and “VPN” are the values that need to be put in the Category and Issue Type column in the list item respectively.
Note:
- The “Column” name should match with the display name of the column present in the announcement list
- Each line will be parsed separately and if it follows the format and column exists in the list then value will be put in the list item for that column. Column value cannot span more than one line in this format
ii. Pre-defined format “[<List Column Name>:<Value>]”
This is same as above except the data is within the brackets. So parser will look for starting of the bracket to begin reading the column name. And it will read the value up to the ending bracket. This format is useful if information can span multiple lines. Example:
[Category:Network] [Issue Type:VPN] [Additional Info: This is a contoso Request. Requested by Team lead on behalf of his team members]
In this case value for column “Additional Info” is spanning two lines as the ending bracket is not in the same line.
3. Configure email body parsing – Custom format
In this option, regular expression can be specified as per the content structure of the mail body. You can use this option if data is not in one of the two predefined formats. Following configurations are required for custom format option:
- Regular Expression – Specify a regular expression to parse the content based on specific pattern. This regular expression need to have two “Groups”, one to match the SharePoint list column name and the second to match the column value
- RegEx Multiline Mode:
- Single: Considers pattern for whole mail body as a single line. This means, value for a column could span multiple lines. In regular expression terminology, this means the “Dot matches newline”
- Multiline: Considers pattern line by line in the mail body. This means each line is parsed separately for the pattern so value of column cannot span multiple lines. Note that one line could have multiple matches depending on how the regular expression is written
Sample text in Mail body to extract value :
##Category:Network## ##Issue Type:VPN## ##Additional Information:Contoso Text##
For the above text parsing, we can use simple regular expression as given below:
##(.+?):(.+?)##
Note:
- The “Column” name, that is the first part before the colon in content should match with the display name of the column in the announcement list
- This is a sample regular expression based on the particular format shown above. For your specific content format you can specify the required regular expression. You can contact Crow Canyon for professional services to generate the formatted content and also parse it as per your business requirement
Regular Expression for individual columns
Above settings will extract information for all columns that have data in the body as per the general format. If however, any particular column has data in some specific format different from the general format, then regular expression specific to that column can be specified.
For example, consider a column in SharePoint list with name “Additional Information” for which there is information in the email body. This information is prefixed with text “More Details” instead of the column name in the list. So we can extract this using a custom regular expression specific to this column.
Sample text in Mail body:
##Category:Network## ##Issue Type:VPN## [More Details:Some text for input]
General regular expression for columns: ##(.+?):(.+?)## Regular expression for "Additional Information" column: \[More Details:(.+?)\]
The multiline mode settings have same meaning as specified above in this article.
3. Integration of parsing in our Crow Canyon Service Request applications
In Crow Canyon Service Request applications, we have standard email sync configured to map Email data such as Subject, Body, From address, Recipients, Attachments etc. to the corresponding list columns in Email Tickets list. Application then creates a Ticket and copies over the data from these standard email columns to the corresponding Ticket list item columns.
Using functionality described above, we can configure the email body parsing to extract information for more columns for the Ticket list.
Go to site contents –> Configurations list –> Crow Canyon NITRO Forms –> Create a multiple lines of text column of Plain text mode with name “TicketFromEmailFldMapping” as shown below:
Add this column to “Email Handling” tab in the form and rename the column as needed and publish the NITRO forms for the list.
This column is used by Crow Canyon Product app to read the information from Announcement list (Email Ticket) and copy it over to the Tickets item.
Syntax
Internal Name of “Tickets” list column=Internal Name of “Email Tickets” list column
We can specify multiple mappings with a line break as shown below:
Category1=Category
IssueType= Issue_x0020_Type
Note: “IssueType” is internal name of “Issue Type” column in the Tickets list whereas “Issue_x0020_Type” is internal name of column created in “Email Tickets” list. In order to get the internal name of the list column, please go to corresponding list settings –> click on list column and get the internal name from the URL after the “Field=” as shown below:
With the above configurations, when a ticket is created from an Email Ticket, Crow Canyon Product app reads the configuration and maps the data from Email Ticket item to corresponding columns in the Ticket item.
