Many times we need to keep tracking emails related with a particular subject, received from a specific address or sent to a defined mailbox, etc. From day-to-day operations to external contacts, there are plenty of real life scenarios when it happens and there is a big probability be doing it currently.
The majority of the time our options are either, go email by email and copy the entries into a spreadsheet or pay for one of the thousand software off-the-shelf available in the market nowadays. If the latter can be expensive, the former is not only time-consuming but also it can lead to many errors.
The good news are there is a third option that is a really cheap and easy way to automate and improve this process. We are going to use Microsoft Flow, Outlook, SharePoint and Power BI from your Office 365 subscription.
In this scenario we are tracking emails received in a Shared Mailbox with a particular Subject. In order to automate the process we are going to create a flow in Microsoft Flow that is recording and entry for each email received in a SharePoint List. Then we connect Power BI to the SharePoint List and import the data to create a Report that is going to be our Email Tracker. After published to the Power BI Service we are going to embed the report into a SharePoint Page. Parallel to it, an email with dashboard is sent daily to the subscribers.
Step 1 – Define Email Rules
In order to be possible to automate the process is critical to have set of unique rules that are what define which email do we want to track. It can be based on a subject that contains a specific text, emails received from a sender or a group of senders, emails sent to an address, etc.
In this scenario we defined to track emails received in the Inbox of mailbox “email@example.com” that contains the text “REF:” in the Subject.
Step 2 –SharePoint List
For this example we are going to create a List on a SharePoint Team Site.
To simplify we define four main columns: Title, ProjectID, Date and Comment.
See more about how to create a list in SharePoint here
! Tip: alternatively, you can use Excel to collect the entries.
Step 3 – Create the Flow
Once clearly defined the email rules, we are going to create automated workflow with Microsoft Flow.
The first thing we must do is to create what will trigger the workflow.
In our case, the Trigger is when a new email arrives in the Inbox folder of firstname.lastname@example.org that contain “REF:” in the Subject.
After the trigger, we need define the running actions. The first step is to get the email.
One extra step is to transform the email body content from HTML to Text. This is necessary in order to be able to collect details that are included in the email and that will be important for our tracker.
Now we instruct to create an item in the SharePoint List created in Step 2. Parallel the email is marked as read.
! Tip: You should always perform some testing before move on to the next steps. It can avoid future errors due to rules not considered at the beginning or the Column date format on the List. There are three option to test the flow.
Select accordingly and Confirm the entries are recorded in the list as expected.
Step 4 – Power BI Report
At this point we have our data source created. Simply connect Power BI Desktop (or Data-flow) to the SharePoint List. Now is the normal process of report creation in Power BI: Edit -> Model -> Visualize -> Publish.
Step 5 – Refresh data, Set Alerts, Subscribe email
As we are looking for a fully automate process we are going to schedule data to refresh once a day together with an email push to subscribers. You can also set any alert based in a KPI.
Step 6 – Embed on Report on SharePoint Team Page
A good method to share your tracker with your team is to embed it on a Team Site on SharePoint.
In summary, we have easily automated a way to track the emails received without any manual intervention.
There are plenty of Microsoft Flow templates, with Low or No-Code required, that can be used to streamline daily repetitive tasks.