Do you remember the good ol’ days with email-enabled SharePoint lists from classic SharePoint on-premises? They were replaced with site mailboxes, which were also eventually deprecated. Many organizations still have a need to accept files from emailed sources and have had to use third-party solutions in the past.
Now that Microsoft Flow exists, you can create a solution that will take information from a shared mailbox in Office 365, and place it into a SharePoint list, complete with attachments and metadata!
This guide is a companion to my session at the Collab365 MicroJobs Microsoft Flow Virtual Summit. For the best results, please watch the video and read this guide.
You need four major components for this solution to work:
- Shared mailbox inside Office 365
- SharePoint document library inside a SharePoint Online site
- Specifically-formatted email to store metadata
- Microsoft Flow that monitors the shared mailbox, and creates SharePoint files with metadata from the email
Throughout the rest of this guide, we will dive into each of the components.
The first thing you need to do is create a shared mailbox in Exchange Online. You must have exchange administrator permissions or access to someone who does this. Follow the below steps to create a Shared mailbox:
- Navigate to the Exchange admin center in Offce 365
- Under “recipients,” click on shared
- Click the “+” to create a new shared mailbox
- Fill out the new mailbox form with the following information (1, Mailbox name, 2, Email address 3, Users who have access (The account you are using for Flow must be in here) and click save:
Now that we have our shared mailbox created with the correct permissions, we need to plan out and create our SharePoint document library. If you haven’t already, start by creating the SharePoint site itself. I’m going to use the one I already have.
Once you have your SharePoint site, start thinking about what information (metadata) you want to be attached to the documents in your document library. I created one called “Email Requests” and am going to use the below fields:
Name – Filename (and link) of each file. Default SharePoint field
Subject – Email subject. Text field
Requester name – Sender name. Text field
Company – Sender’s company. Text field
Job number – Job number/code. Text field
Notes – Description or a large block of text. Multi-line text field (important)
It’s important to use a multi-line text field for bigger blocks of text, or the Flow will fail. There is nothing special you need to do in SharePoint beyond this. You could use number or choice fields here too if you need.
You can’t reliably extract information from the body or subject line of an email if you can’t predict what’s going to be in there. For this example, we are going to require the subject line to have the characters: “Project Update:”, and have the rest of the fields in the body.
To: <address of shared mailbox>
Project Update: <subject>
Customer Name: <Name> Company: <Company>
Job Number: <Number/code> Notes: <Notes>
<files for SharePoint library>
It’s important that the senders follow the exact template you create if you want this to succeed.
We have our source (mailbox), our destination (SharePoint), and our message (email template). Now the fun begins. We need to build a flow that monitors the mailbox, extracts values from the email, create files in SharePoint, and applies the earlier values to the metadata. The process is outlined below:
The flowchart above doesn’t translate 1-to-1 in Flow, but you can see that overall, it’s pretty straightforward. Here’s what that looks like in Flow:
Let’s break down each of the sections, and we’ll talk about what each section of this diagram does.
Section 1: The Trigger
When you make your Flow, you’ll need to choose a trigger. We want to use it when a new email arrives in a shared mailbox. Fill out the fields below accordingly.
Mailbox address – This is the shared mailbox you made earlier
Folder – inbox is the default, but you can select a different folder.
Has attachment (under advanced) – Only look for messages that have attachments.
Include attachments – This must be set to yes in order for attachment data to be sent to SharePoint.
Subject filter – This is optional, but you can use this to make sure they use the correct subject format.
This trigger will make sure the Flow runs whenever an email with attachments shows up in that shared mailbox with the right subject filter, and passes all that information to the rest of the Flow.
Section 2: Set sender email and subject to variables
This section will be the easiest, so I grouped two different values together here. In section 2, we are going to initialize a variable set to the sender’s email address, and also look at the subject line, split it in half, and pull out the subject value. Lets’ start with the email address.
Set the email address to a variable
We are going to start a new step, then use the action “Initialize Variable.” Name it SenderEmail (or whatever you want), set the type to string, since it is text, and in the value, use the dynamic content option, to get the From email address from the trigger (see next image)
Once we set the variable, we are done with it until the end of the Flow. This will be true for all the variables we set along the way.
Get subject, split on the ‘:’, and set the subject data to a variable
Next, we are going to do something tricky. We’re going to use expressions inside compose actions to extract only the part of the subject that does not include the phrase, “project update.” There are 3 steps to this process:
- Use a compose that takes the input of the subject field and uses a “split” expression to break the field into two values.
- Create another compose that takes the output of the previous compose, and looks at only the second of the two values
- Initialize a variable that is set to the output of step number 2. Let’s look at each of these actions now.
Step 1: Split the subject.
Start a new action, and find “compose.” This action can be used to create all kinds of text outputs, and this solution will rely heavily on it. Inside the compose, click “add dynamic content,” and switch from “dynamic content,” to “expression”
We are going to use the split expression here. Don’t let this scare you, it’s not too bad. Start by typing “split()” and putting your cursor inside the parentheses. This expression works as follows:
Split() contains what the expression does, and all the parameters it needs will go inside the parentheses, separated by comma. In the above example, you can see everything the split command needs is in red, and the parameters we are using are in blue. Let’s talk about what those parameters are.
Parameter 1: What are you splitting? In this case, we want to split the subject of the email. Right now, it is “Project Update: My house is on fire.” We want it to just say “My house is on fire.” To get the value of email, we could either type in all that triggerBody() stuff before the red comma, but instead, we are going to click inside the parentheses, click the words “dynamic content” in the window we’re in, and choose “subject.” This will fill in all the triggerBody stuff, so you don’t need to know how to find it.
Parameter 2: What character inside this string are you going to use as a delimiter? This second value goes after the comma (again, red on the previous image). In this situation, our two text values have a ‘:’ between them, so that’s what we are going to use. It’s important that you include the apostrophes around the colon, so it is recognized as text.
Once you have set up your expression, it will look at the subject, and spit out an array (list of items) with each text value separately. We will show what all the inputs and outputs look like at the end of the guide.
Step 2: Get only the second value from the array
We have split the subject, but it’s spitting out two values. Now we’re going to use another compose action that is just the second value. We do this by choosing which item number we want from the array. Since this array (list) has two items in it, numbered from left to right, which item number do you think we want to look for? If you said item #2, you are incorrect. There is no item number two because arrays start at 0. So instead of having items 1 and 2, we have items 0 and one.
With the compose action you can use another expression to show only one item. It looks like this:
The red part is just the output from the previous action. You can see it’s represented by outputs(‘<name of the previous action, with underscores instead of spaces>’). It’s diffcult to type all of that out, so again, you can start with an expression, like split, add the dynamic content, like I showed before, then delete the split() stuff out of there, leaving you with only your outputs value.
Once you have the outputs of the previous action, add a question mark, some square brackets, and the number of the item we want to look at, which is again, the number 1, not 2.
Step 3: Initialize and set a variable to the output of the previous step.
Just like earlier, we are going to add an initialize variable action, set it to a string, and put in dynamic content, this time choosing the output of the previous action.
We now have our subject set to a variable. Time to get even more advanced.
Section 3: Convert email body content from HTML to plain text
In order for our Flow to read all the things it needs to read inside the email body, we need to convert it from HTML to text. The process looks something like this:
We will need two or three actions for this section. I use two in my example, but you really should use a variable at the end of it.
Step 1: Initialize a variable and set it to the email body.
Use the same action we’ve been using for the variables, give it a name, set it to a string, and pass in the email body.
Step 2: Use an HTML-Text action to convert the HTML in that variable to plain text.
Create a new action and look for HTML to text. In the content field, use the EmailBody variable we just created
Step 3 (optional): Initialize a variable, and set it to the output of the HTML to text action. In this example, I use the output directly at the end, instead of a variable, which is not as clean.
Section 4: Get the total length of the email body
We don’t need this value until the end, but the expression is so easy, you may as well set it up now. What we are going to do is count how many characters are in the email body that we just converted to text. You would use the variable if you set it in the previous section, but I’m using the text output directly.
We’re going to use another compose action, along with an expression that looks like this:
This will output a number, which we will then use as the value for a variable. Make sure you set the variable type to integer, because we are going to do math with this number later.
Section 5: Extract all of the metadata from the body
If you noticed section 5 is actually a few different sections, it’s because this process has to be done for each field you want to pull out of the email. The nice thing is that the process works the same for each field (except the final one), and you can keep replicating it. It flows like this:
You accomplish this with three compose actions, each using a different expression, then set it to a variable each time.
Step 1: Find the first character in the field data you need
Create a compose action, add an expression, and use the following:
The idea here is to find the number value in our block of text we made earlier where the value we need starts. We do this by finding when the field name begins (customer name in this example), then adding the number of characters in that field name (plus a space) to find where the value starts.
We do this with an Add expression here, which takes two parameters, and adds them together. In this case, we want to find the number where “customer name” starts, which should be character 0, then find the number one space beyond that. “Customer name:” has 14 characters, and we add a space to it, so that gives us 15.
15 gives us the parameter after the comma, but what about all the “indexOf” stuff? We are using the indexOf expression to find the beginning of the field. This expression takes two parameters. The first one is, “what body of text am I using,” which will be “body(‘HTML-text’)” or the variable you created if you wanted to be clean about it.
The second parameter it expects is “what phrase am I looking for?” We are going to use field names every time we do this. The first field name is ‘customer name:’ so that is what we use.
The IndexOf expression will give us an integer that we can then add to our second number (15 this time), to give us our starting value. All of this will make sense when we look at the inputs and outputs at the end.
Step 2: Find the beginning of the next field.
We only want to grab the text between each field, so now we want to find when the next field name begins. Again, we are going to use a compose action, with an expression in it, and we are again going to use the IndexOf expression. However, we don’t need to add anything, so we don’t need the add expression.
You can see here we are using IndexOf the same way we did earlier. It’s looking inside the text block (variable or output) for the phrase ‘Company:’ and outputting the number where that phrase starts. Time for the most complicated part of this whole Flow…
Step 3: Use a substring expression to extract the text between your two fields
We have our starting character number (1 space after Customer Name), and our ending character number (the beginning of Company). Now we’re going to create another compose action, use another expression, and use the “substring” expression to get all of the text between the two points.
It looks like this:
Don’t run away just yet! Let’s break this down. We are going to create another compose action, and use the substring expression. Substring is an expression that will look at a block of text, and grab everything from a designated starting point to a certain amount of numbers. It takes three parameters: 1) What body of text am I looking at? 2) What character do I start at? And 3) How many characters do I go to.
As an example, if I had a variable with this text: “Substring is tricky, but Bob is smart”, and I wanted to only get the word, “Bob,” I would say Substring(variableName,25,3). This would look at the text in the variable, start 25 characters in (right before Bob), and go three more characters, giving us the output of “Bob.”
That seems easy enough. We know that parameter one is going to be our block of text (either the html-text output or the variable you made) but we don’t know what the second and third parameters are because they will change from email to email. Instead of using absolute numbers, we are going to use dynamic values for parameters 2 and 3.
Parameter 2: We already have a number for our starting point. We are going to use the output of our “find first field” compose action. You can see this after the first red comma in the picture above, and before the second red comma, highlighted in blue.
Parameter 3: This one is a little trickier. We used the indexOf expression earlier to get the character number of our next field, but the substring expression doesn’t work that way. It expects the number of characters it needs to go before it stops. In order to figure out this number, we need to subtract our starting number from the number of the next field. We do this using the “sub” expression, which means subtract.
It works in a similar way to the add expression we used earlier. You start with a sub(), and pass in two parameters. In this case, we will have the output of step 2 as our first parameter, and the output of step 3 as our second one, which will give us the difference. It looks like Sub(second field start, First field end). The output of this will be the number of characters substring needs.
For example, if my body of text looks like this:
Field1: Value1 Field2: Value2
And I use these three actions, this will happen:
Add(IndexOf(textbody,’Field1:’),8) where field1: has 7 characters, and I added a space, getting
8. The output of this would be “8”
IndexOf(textbody,’field2:’) which will give me an output of 15, where field 2 starts
Substring(textbody, <output of step 1>, sub(<output of step 2>, <output of step 1>)) will get me 7 characters, starting at character 8.
Set the output of this action to a variable, and move to the next field.
We will test this all out really shortly and see what it looks like. Repeat this same process for every remaining field in your solution except the final one.
Just use the same 3 compose actions, with the same expressions, only change the names of the fields and number of characters to add accordingly.
Section 6: The final field
The last field in your email, in this case Notes, works slightly differently than the other ones. Since we don’t have a next field to look for, we just need to go to the end of our text block, which is a value we got way back in section 4 (the TotalLength variable I set).
Set this one to a variable as well, and you should now have all of your values ready to go!
Section 7: Getting your files into SharePoint
The hard part is over. Since we have all of our values, we just need to create our files in SharePoint and set the metadata.
Step 1: Apply to each
We are going to be getting our files from the attachments value in the email our Flow is processing. Since there is a possibility of multiple attachments, we need to use an Apply to each action inside Flow. This action functions as a loop for items, meaning it will perform whatever actions you put inside it to every item in your group of items (in this case, attachments).
Use an Apply to each Action in Flow, and in the field that says “select an output from previous steps,” choose the value from your dynamic content.
Step 2: Create the file in SharePoint
The first action we need inside our loop is the Create file SharePoint action. You can find it by searching for “SharePoint” or “create file” and clicking the appropriate action.
- In the Site Address field, use the address of your destination SharePoint site
- Place the name of your document library in the Folder Path field
- You can use “Attachments Name” for the file name if you want, but you could also get fancy here and split the file extension from the name field, and dynamically create your own naming scheme. I chose the easy solution. It’s important that the file extension is correct, whatever you do.
- Since we want our new files to be the same as the attachments, we will use “Attachments Content” in the File Content field.
Step 3: Update file properties
When you use the create file action in Flow, you don’t get to set any metadata. In order to do that, you need to use the Update file properties SharePoint action.
- Just like in the previous step, set your you are using Site Address and Library Name to the site and library
- The ID field is how you identify which file’s properties you are changing. We will want the ItemID value from when we created the file in the previous step.
- Once we know which file we’re going to change, we can use the variables we’ve created during this Flow to fill out the rest of the SharePoint columns. Notice that I added subject to the title field, but that’s optional and not necessary. You can create your fields however you want.
And that’s it! Our Flow will now take any email in that shared mailbox that meets our requirements, pull out the information we need, place the attachments in a SharePoint library, and set all the metadata for us! All that’s left now is some cleanup and error handling.
Section 8: Send an email if this fails
One of the things we should do with our Flows is put in some sort of notification if there’s an error. To do this, we’re going to use the email action.
That part is straightforward enough. But if you’ve used Flow before, you know that the email will run every time the Flow runs if we do it like this. What we need to do is Configure run after. Click the 3 dots on this action, then select that option.
On that screen, you can set which situations warrant this email action happening. I chose for it to happen if anything other than a success happens.
We will now only see the email if the previous step fails.
Section 9: Delete or move the email
The last step of our Flow is entirely for cleanup. Once you’ve gotten the files created, it’s now safe to delete or archive the original email. In this example, I’m going to use delete, but you could use a move action and move the message to a different folder in the shared mailbox if you like. It works in a similar way to the delete action.
All you need to do is place the “Message Id” value from the first step in this Flow in the Message Id field on this action. If you were using the move action, you’d also have to pick a folder from the mailbox.
We’re now 100% done with our solution. Let’s take the time to test this Flow and talk about troubleshooting. The first thing I’m going to do is compose my email, and send it with attachments
Then I’m going to wait about 5 minutes for the Flow to run. You can find your running and completed Flows on the main information page about your Flow (you can click the back button from the edit screen to get here)
You can see that my entire history of running this Flow is here right now, while I wait for this one to run. Since you just created this Flow, you won’t have a run history yet.
After waiting a couple of minutes, I refreshed my screen and saw my run had been completed. You can see what happened during the run by clicking on it.
You’ll be taken to a page that looks very similar to the edit screen we were on earlier, but now it only shows results.
You are able to click on each step to expand it and see what is happening in each. Let’s go through them one by one now. If your Flows ever fail, this screen will show you which step failed, and why.
On each step we expand, you’ll be able to see the inputs and outputs. In the case of the first step, you can see the inputs were the message that came into the mailbox, and you can click to download all of the data that came through. I am not going to do that.
Next, we can see that the sender email variable was set correctly
These three show what happens when you use the split expression and grab that value from the array
On the HTML to Text conversion, you can see that the input (the variable we set to the email body) has a bunch of HTML tags in it. The output, however, is in regular plaintext and is easy to read.
We have our block of text, now it’s time to start extracting information. First, we will get the total length of the text block:
Here are the indexes (character numbers) for the start and stop points of the first field:
Once we have those, we can use our substring expression to get the information between those points.
The output is set to our variable, and we move to the next field.
Again, you can see that the first step gets the end of the field name, the second step finds the beginning of the next field name, and the substring grabs all of the text between those two points.
Another variable is set, and we go through the rest of the fields until we get to the last one.
The last one also works correctly, and there isn’t really any difference in the inputs and outputs.
All of our variables were set correctly, so it’s time to create the SharePoint files within our “apply to all” loop.
Within the “apply to all” loop, you can use the previous and next buttons (or any of the other controls in that navigation) to scroll through each attachment and see what happened. We know from the green checkmarks that everything was successful.
For each attachment, a SharePoint file was created, and you can see the data inside the body section.
Once the file has been created, the file properties should be updated to contain all of the variables we set. That information should be available in the body section for both the inputs and outputs.
No emails were sent because everything was successful, and the file was deleted successfully as well.
It’s not as easy as it used to be to make a mail-enabled SharePoint list or library, but it’s definitely possible with Microsoft Flow. Expressions can be difficult to figure out, but if you follow this guide, you can do it!