Flow idea #5: Archive Project Emails (Advanced)

Overview

Emails are used a lot in project communication, so users want to be able to archive emails to ProjectWise so that they have all project information in a single location. This article demonstrates how task of archiving project emails to ProjectWise could be made easier by automation.

We will create a flow that will periodically scan users Outlook 365 inbox for new project emails. It will store emails into folders of corresponding work area in ProjectWise. All users needing their emails to be archived would have to run a copy of such flow.

This flow will prevent archiving of duplicate emails. Very often same email is sent to multiple recipients in organization. An email will not be archived if another recipient has already archived this message.

We had to make several assumptions about how users organize emails and project data. As most of ProjectWise configurations are unique, you may need to make adaptations to make it practical in your environment. Here are the assumptions:

• Users organize project emails into subfolders for each project.
• They either manually move relevant emails to folder, use outlook rules or both.
• The above applies not only to received emails but also to sent emails.
  • Other alternatives could be: use project name in email subject, use special email address for a project.

ProjectWise Configuration

This Flow was created using ProjectWise sample datasource available with ProjectWise Design Integration server installation. Every ProjectWise configuration is unique, so this Flow may need to be adapted to your actual configuration and project requirements.

This flow handles data in several projects so we have created a new shared work area and associated cloud project called ‘Admin’. We used this project for all Flow actions to make them independent of a specific project where engineering work is performed.

• A saved search that returns all projects for current user was added to project ‘Admin’. This list of projects will be used to search for email folders in user’s mailbox.
• Each project has a subfolder called ‘Project Email’ to store the messages.
• A new environment ‘Email’ was created for storing email messages. It has several email related attributes.

The Flow

The flow consists of three major parts: Get list of projects, Find project email, Save emails to ProjectWise. We have used scopes (the brown action) to group actions for better readability.

The flow runs automatically every hour.

Getting list of projects

At the end of this part there will be an array of project folder names (later used for building path to project folder) and project number (later used for email folder name in Outlook 365).

The first action in this section is ProjectWise ‘Find folders by saved search’. This action returns list of project folders (work areas from sample PW datasource in our case).

Next is a build-in Data Operations > Select action to transform list of folders from search to list of folder names and document numbers. Folder name is taken directly from search results. Project Number in this example is extracted from project name. Project name is in format ‘BSI000 – Project name’. The part before dash is the number. The formula to extract number is (use Expression tab to enter it):

               substring(item()?['name'], 0, max(indexOf(item()?['name'], ' - '), 0))

The third action is Data Operations > Filter Array. It filters only entries that have a document non-empty number. Notice that this action was renamed to ‘ProjectList’ for easier access in following actions. The output of this action is the list of projects that will be monitored for emails.

Finding project emails

This part will look for emails for each project and save the list into array variable ‘EmailsWithProjects’. This variable is defined outside of scopes, because variables can only be declared at top level.

The following scope of ‘Find project emails’ is shown in the picture:

The flow of this part is:

• Loop through list of projects (Apply to each) retrieved in previous part into ProjectList action
• Read emails from Outlook 365 folder ‘Projects/Project name’. (Get Emails (V2))
• Handle error situation if email folder is not found without failing the whole flow run (the Compose action and run after).
• Store email data together with project number into array. (Apply to each 2, Append to array variable).

Let’s look at some details of these steps.

Get Emails V2: Reading of email tries to get as much emails as possible. At this point we do not know how much is archived already, but it assumes that the flow runs periodically multiple times a day. The filters are set to return messages from today and yesterday This is a generous overlap, but it is best what Outlook connector filtering allows.

Outlook 365 get emails actions can get maximum 25 messages. If there were more than that received since last flow run, the messages above 25 will not be fetched. Unfortunately, it is a limitation of this flow that cannot be easily overcome with current implementation of Outlook 365 connector.

The connector also constructs email folder name using project number from ProjectList. The formula for folder path is like this: Projects/@{item()?['number']}

Get Emails action may fail if there are no folder with project name. To handle this situation without failing the flow we use the following run after settings (to access run after settings for an action go to: … > Configure run after):

This sequence ensures that in case of failure of Get Emails, a compose action will be executed. This action will always succeed so the flow will continue to run without error. When Get Emails does not fail if will to ‘Apply to each 2’ loop for each email.

The Apply to Each 2 loop appends full email information along with project name into array for processing in next section.

Saving emails to ProjectWise

It is the last and most complicated section.

• It loops the EmailsWithProjects array (Apply to each 3)
• Exports each email (Export email)
• Extracts Message-ID (Extract email ID). Message-ID allows to identify the same message even if it was received by several recipients. Unfortunately, it is not available as an email attribute and needs to be extracted from message headers using string manipulations.
• Checks if message was already archived (Find document by name or GUID)
• Creates a new document if it did not or runs Compose 5 action if it does by using error handling technique with run after.

Extracting Message ID

This scope uses two compose actions to extract message ID from email. It is an email header that is in format: Message-ID: <unique id>. We need the ‘unique id’ part of it.

Compose 2 finds the Message-ID header and takes 260 symbols that will contain the unique ID. The expression is (it is a single line):

substring(body('Export_email'), indexOf(body('Export_email'), concat(json('{\"NL\":\"\\n\"}')?['NL'], 'Message-ID:')), 260)

Compose 4 tackles the unique id extraction:

substring(outputs('Compose_2'), add(indexOf(outputs('Compose_2'), '<'),1), sub(sub(indexOf(outputs('Compose_2'), '>'), indexOf(outputs('Compose_2'), '<')), 1))

Creating document with email attached

The email document name and file name values are the unique id extracted in previous step (output of Compose 4).

Other fields are specified like this:

• Document name: "@{outputs('Compose_4')}",
• File name: "@{outputs('Compose_4')}.eml",
• File content: "@{base64(body('Export_email'))}"
• Attributes:
  • Subject: "@{items('Apply_to_each_3')?['Email']?['Subject']}",
  • Form: "@{items('Apply_to_each_3')?['Email']?['From']}",
  • Too: "@{items('Apply_to_each_3')?['Email']?['To']}",
  • Cc: "@{items('Apply_to_each_3')?['Email']?['Cc']}",
  • Received: "@{items('Apply_to_each_3')?['Email']?['DateTimeReceived']}"

The flow is ready to run. It needs to be deployed for every user whose inbox must be monitored.