What do email-enabled applications look like in the modern workplace? – Part 2 (smarter solution)

This blog is Part 2 of a multi-part series, What do email-enabled applications look like in the modern workplace? which examines how enterprise mail-enabled applications built on legacy platforms like Notes or SharePoint on-premises can be re-imagined in the Modern Workplace.

The goal of this series is to provide a blueprint for building a “Modern Workplace version” of a mail-enabled application on Microsoft Flow, beginning with a basic solution and gradually adding more intelligent features attainable only in the cloud.

I recommend you begin by reading Part 1 (basic solution), which provides some business use case background and covers creating a basic solution in Flow that accepts incoming email to an Outlook shared mailbox, creates a list item in a Requests triage list in SharePoint and responds to the original sender.


More intelligently handle incoming email

Our “basic solution” flow supports the following scenario:

  1. A user sends an email (a “support request”) to a shared mailbox (e.g. “support@contoso.com”)
  2. The app generates a unique RequestId for the support request and logs the request in a triage list in SharePoint, attaching the original email to the list item
  3. The app emails the sender, acknowledging receipt of the support request and providing a link to the support request in SharePoint

The flow runs every time an email enters the shared mailbox.  But imagine this (likely) scenario:

  1. A user sends a request via email
  2. The user receives an automated email response
  3. The user replies to the email, not realizing it’s an automated response

What if the user replies to the automated response? Right now, our flow would create a brand new ticket.

We need to update the flow to intelligently handle incoming email and create new request tickets ONLY when users aren’t replying to the automated email they receive. And if we determine the email IS a reply to an auto-generated ticket, we can intelligently respond to the user with an update on the status of their request!

1. Update the flow to determine if the email is a new request or a reply to an existing ticket

First, we need to detect if the incoming email is a new request or a reply. If it’s a reply, there should already be a Request ID in the subject line of the email.

Add a new “Initialize variable” action and give the new variable the name “IndexOfRequestId”; assign “Integer” as the type and -1 as the initial value.  As a best practice, you should name your shapes as you build the flow, so rename the action to “Initialize IndexOfRequestId”.

Next, we’ll check to see if there’s a request ID in the incoming email and write the index of the request ID to this variable if we find one. Add a “Set variable” action and name it “Set IndexOfRequestId”.

In the “Value” field, type (or paste) the following expression:

indexOf(triggerBody()?['Subject'], 'Request ID:')

This will find the index of the text “Request ID:” and save its value to the variable you just created (or it will remain -1 if the text isn’t found).

The two new actions should look like this, with the flyout to the right appearing when you click the value in the bottom action:

Next, add a Condition shape to check if the email subject contains a Request ID (by checking if IndexOfRequestId is greater than 0). Add the “IndexOfRequestId” variable to the left operand of the condition, then select “is greater than” for the operator and enter “0” for the right operand.

This entire new sequence essentially says “If the text Request ID is in the subject of this email, do x, and if not, do y.” It should look like this:

Finally, we want to execute the actions to create a Request list item ONLY if the email is a *new* request (i.e. NOT a response to an existing request). Drag the following 4 shapes to the “If no” side of the “Found Request ID” shape:

  1. Export original email
  2. Create request list item
  3. Attach original email to the list item
  4. Email the sender that the request was received

IMPORTANT: You’ll need to drag the shapes in reverse order (i.e. #4, then #3, then #2 and then #1) because the later actions have dependencies on the earlier ones.

The flow should now look like this:

2. Allow the sender to request a status update by replying to the automated email response

The other half of the condition represents the use case where a user replies to an auto-generated email that already contains a Request ID in the subject line. An elegant way to handle this scenario is to automatically respond with the status of the request.

To do this, our flow needs to:

  1. Parse the Request ID from the email subject
  2. Query SharePoint for the list item with the matching Request ID
  3. Send an email with the updated request status to the requester

Parse the Request ID from the email subject

To parse the Request ID from the subject line, we need to split the email subject into an array of strings. First, initialize an array variable in which to store the split subject line strings and place it after the “Set IndexOfRequestId (Integer)” action. Leave the initial value blank to create an empty array.

IMPORTANT: Note that I am renaming each Flow action manually. It’s best practice to do this early and often, as an action CANNOT be renamed once another action contains a reference to it.

If you’re following along at home, you might want to name your actions the same as mine; I reference actions by name often in the instructions that follow.

To perform the split within the left (“If yes”) side of the condition shape, use the “Set Variable” action and select the EmailSubjectParts array variable. Click into the Value field, then click Expression in the dynamic content popover.

Click into the Value field, then click Expression in the dynamic content popover. Paste (or type) the following expression:

split(triggerBody()?['Subject'], 'Request ID: ')

This will split the subject of the incoming email before and after the “Request ID: ” text, effectively placing the unique Request ID into the last element of the EmailSubjectParts array. When you’re done, click Update and the expression will appear in the Value field of the action.

Finally, set the value of the (already-initialized_ RequestId variable to the last element in the EmailSubjectParts array.

Add another “Set Variable” action beneath the “Split EmailSubjectParts” action, select the RequestId variable, then once again click into the Value field and click Expression.

Paste or type the following expression, then click Update:

last(variables('EmailSubjectParts'))

3. Query SharePoint for an existing support request

Now we can query SharePoint for a list item whose RequestId field matches the one we pulled from the email subject.

Beneath the “Set RequestId (String)” action, add a SharePoint “Get list items” action. Use the same site URL and list title (“Requests”) as you did to create the list item. Click Show Advanced Options to specify the ODATA query that will return only the list item matching the specified RequestId.

In the Filter Query field, specify the following, inserting the RequestId variable between single quotes (‘ ‘):

Theoretically, only a single value should be returned, but you can specify a Top Count of “1” just to be sure.

4. Email the sender a status update

Now that we have the list item representing the request, we can send the user the request’s status in a nicely-formatted email.

Add a “Send email from a Shared Mailbox” action beneath the “Query for existing request with this Request ID” action and specify the following values, inserting the Status Value and Link to item from the “Query for existing request with this Request ID” action.

Be sure to expand the Advanced Options and set Importance to “Normal” and Is HTML to “Yes”.

IMPORTANT: As soon as you insert values from the
“Query for existing request with this Request ID” action, the “Send email with request status” action will become nested within an “Apply to each” loop.

This is because the “Query for existing request with this Request ID” action can technically have multiple outputs, even though we are choosing to return only one.

5. Test the flow

The current flow now supports the following business scenario:

  1. A user emails a support request to a shared mailbox (e.g. “support@contoso.com”)
  2. The app determines if the user is submitting a new request OR checking the status of an existing request
  3. If the user is submitting a new request:
    1. The app generates a unique RequestId for the support request and logs the request in a triage list in SharePoint, attaching the original email to the list item
    2. The app emails the sender, acknowledging receipt of the support request and providing a link to the support request in SharePoint
  4. If the user is checking the status of an existing request by replying to an auto-generated email:
    1. The app looks up the user’s existing request from the unique ID in the email subject line
    2. The app emails the sender with the current status of the request and a link to the support request in SharePoint

The flow should now look like this (the parts highlighted in red were created during Part 2):

Click “Test” in the upper right-hand corner of the Flow canvas to test your flow (this will save the flow, too).  Choose “I’ll perform the trigger action” then click “Save & Test”:

Send an email to the shared mailbox.

After sending a “new” email to the shared mailbox, the results should mimic those of Part 1:

  1. A list item is created in the Requests list in SharePoint, with metadata populated and the original email attached.
  2. You receive an email containing a unique Request ID in the subject line and a link to the request in SharePoint:

You can watch the flow execute in real time, with a green check mark appearing next to each completed step.

Before testing the new “Reply” functionality, update Status field of the SharePoint list item created for your request. I changed the status to “Acknowledged”:

Now, reply to the email you received. You should receive another email, this time with the subject “Status update – Request ID: {unique request ID}” containing the updated status of the request.

Check the Run History for your flow and click the most recent execution. If you received an email response as above, you should see the left side of the condition executed successfully:

You can watch the flow in action below:


In the next entry in this series, we will:

  • Add cognitive services to detect the sentiment of the original email request and auto-categorize the request as “Urgent” if the email body contains key user-defined phrases
  • Add a “Mark as resolved” action to the Support Engineer user interface that marks the request resolved and solicits one-click feedback from the end user

You can download this flow (the finished product at the end of each post) below:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s