How to recreate SharePoint On-Premise Email Enabled List in SharePoint Online with Microsoft Flow

Amongst the various SharePoint on-premise to SharePoint Online migration activities that I do in my organization for different customer projects, off late I came across migration of email enabled lists. While many a times we discover that such migrations are just for data since the end-users stopped using this feature, in this case, the migration wasn’t just data, but rather replicating the entire functionality for quite an active list.

For those who haven’t used this type of list so far, below is a screenshot of settings of one such list on SharePoint on-premise.

To summarize – there’s an E-mail address specified for each list of this type, and based on the settings for how the email themselves and attachments contained in emails received at the email ID are required to be saved, each list will have a different setting.

Note: Here Email Enabled List may be a misleading term, since it could also implicitly mean Discussion lists and document libraries.

What was the Challenge?

I know this functionality shouldn’t be raising eyebrows indicating astonishment, but unlike most cases where we promote customers/end-users to adopt new features of SharePoint Online, in this specific project, it was a little difficult to switch users to adopting a new functionality altogether. Having said that, I mean, the customer environment wasn’t setup for Yammer, for various other reasons we had to use classic Team Site and not O365 Groups (even as late as mid 2019) and a few more to add to the woes which included that we also didn’t want to provide any other solution which would separate the rest of the migrated site content in one location and give an isolated solution for this case.

When I embarked on my journey to look out for something out of the box that may be other users have claimed to have used, or has been reviewed over the internet as a good workaround, I was pretty sure to find one, however, disappointingly at the end, nothing fit my purpose.

In fact, to put it correctly, there are flows that extract email attachments (which is fairly an Out of the Box action) and save them as a document library item, but what do you do in cases where emails do not have attachments – for sure, since the basic existence of a document library is a tangible file.

Another complexity was right at the outset, Microsoft Flow categorizes mails with and without attachments separately. The following screenshot is a clear representation of indication of the need of deciding further course of action based on presence of attachments and available Flow actions.

What is the specific Case Study?

To devise my solution, I knew I had to keep user experience on the new SharePoint Online environment close to what end-users are used to seeing now.

To delve further into how data is stored in this particular case, I observed that emails were grouped in folders of Sender name (and email) like in the screenshot below.

On inspecting content of each folder, it was clear to me that as the settings of this library indicated, apart from the email, attachments were also stored as an individual document library item.

However, there was no distinction based on emails with or without attachments – all emails still got saved as document library items.

How to architect this Solution?

Before implementing the solution, it’s imperative to first visualize how the output would look like. There may be technical challenges later, that cannot be overlooked, but it’s important to have a vision of end results.

In the existing on-premise implementation, I observed one issue with the way data was stored in Email Enabled lists. If you revisit the screenshot with content inside the folder, you will notice that it was pretty inconvenient to relate attachments to the source (or originating emails). When recreating a solution on SharePoint Online, I could try to work around it to keep emails and attachments connected.

Coming back to the current scenario with Microsoft Flow, I realized that if my emails didn’t have attachments, I couldn’t use a document library in the simplest solution approach.

To me, using an Out of the Box SharePoint list seemed to be a good start, for one main reason – despite being a text entry for each line item, each list item was capable enough of containing attachments.

Another logical step that I took was to not get discouraged by separate handling of emails with or without attachments, rather handle them separately in different Flows. This however doesn’t mean I have different destinations – the intention is to keep the same destination SharePoint Online list, but have two flows to parse different (with and without attachment) types of emails.

How to create the Solution?

My solutions are primarily demonstrated as screenshots of my Flow with explanations interspersed as needed.

I start with the first Flow to handle emails without attachments. The Inbox in the first field Folder points to the Inbox of the mail account that runs the Flow. This could also point to any folder within the Inbox, however I’m pointing directly to the Inbox, since I’m presuming this email account is meant only for handling emails for the associated enabled list.

As I proceeded further to get properties of this email to seed in as my destination list item, I discover that from the above When a new email arrives action, I could extract the following data to seed in my list to help me create the list structure:

• To
• CC
• From (Sender)
• Subject
• Mail Body
• Received Time

Interestingly, I came across a scenario where the To and CC recipients might contain email addresses like distribution list or IDs outside the SharePoint Online tenant, thus while the From (Sender) is considered from within the organization SharePoint user or Group which can map as a single organizational SharePoint Person or Group ID, it was wiser to use simple Single Line of Text field instead of Person or Group field for To or CC.

Subject is better off as a Single Line of Text and Mail Body as Multiple Lines of Text. Received Date remains as a Date and Time field.

Hence, I could create my destination list to contain emails setup like the one shown in the following image.

List Structure

Select the next step to be Data Operations (Compose) to extract the To and CC data.

In the step that I’ve renamed to CC Recipients, the Inputs is pointed to CC from Dynamic content.

Similarly, create another action for the To field and rename the step to To Recipients.

For this Flow, the final step is fill in data for each list item for each received email.

Use the Create Item step and first point to the list to show up all item fields to fill in.

The values of To and CC to fill in the list item are taken from Output of the Dynamic Content for the earlier created steps – To Recipients and CC Recipients.

At the end, my first Flow looks no more complicated than the one shown below.

Since at the start of the solution I mentioned that I needed to divide my solution into two Flows to handle mails with and without attachments separately, below is how I modify a copy of my first Flow to create the second Flow.

To begin with, in contrast to my first Flow, the initial action in this one starts only when emails contain attachments.

The rest of my second Flow stays the same as my first Flow. The only difference being, after the Create Item step, I extend this flow to Add Attachment to the list item created.

As I start building this action, you can see that I’m able to point to the list item ID that I created for my basic Flow.

To this list item, I can add attachment details as indicated in the below screen shot from the When a new email arrives step.

As you fill in details for this action, you’ll realize the Flow is intelligent enough to realize that there could be multiple attachments, hence the action by itself gets transformed to Apply to Each to evaluate in case of multiple attachments in the email.

To summarize, the only changes in my second Flow as compared to the first look like one in the below screen shot.

I don’t need any additional trigger for any of my Flows – both of them track the emails received in the Inbox.

When emails arrive, both my Flows create list items like the one in the image below.

On inspecting each list item, I see that all values from my email (including attachments) got well populated in my list item.

The final step remains to organize the emails in the list so that they look ordered, and as I promised, close to the one on the source. To achieve the arrangement I desire, I’ve edited the default view of the list to group items by Sender and order by Email Date.

Below is a view of the same with one expanded group.

For users still fascinated with using Document Libraries, I did discover another solution, however I’ll keep it for my next blog to form a series. Until them do text in your comments or write to us.