Simple Emails Tracker with MS Flow and Power BI

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.

Flow diagram

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 “info@bordalos.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 info@bordalos.com 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.

Stop wasting money and time

How much time do you spend doing repetitive and tedious tasks to get your data and create your reports?

Multiply it by the cost per hour and you will find how much money you are spending on it.
Now eliminate this wasted time and instead, use it to grow your business or add value to your organization.
How much value are you adding?
Sum both amounts and you’ll find how much money your losing by missing out on the process automation.

Don’t be fooled by small tasks. Say for example that weekly report that takes you 2 hours every Monday to run. You spend roughly 8 hours per month or 112 hours year.
If the cost per hour is €20 you spend €2240 doing this task. Does it worth €2k? And how about the business you can do with this 100+ hours? I am sure you got me.

At this point you’re asking: How can I have the task done not loosing all that amount of time and money?
My answer is: Eliminate, Simplify, Automate, Delegate.

“Never automate something that can be eliminated, and never delegate something that can be automated or streamlined.” Tim Ferriss

Eliminate – You would be surprised how many times I heard “I am doing it because…
this is the way it’s always been done ”
So, do you really need to do that report? Does anyone look at it in the end?
If not, stop doing it.

Simplify – Over time we all tend to overcomplicate. Is just one more detail, one more chart, one more process… and we end up with something that you’ll need a guide to understand what it is. Often less is more. Focus in what is really important and remove all the unnecessary noise.

Automate – Our most precious attribute is we are thinking beings. Thinking is what distinct us from other animals and what make us continue to evolve. No one should have to do tedious and repetitive tasks that can be done by a machine, a system or a process.
Leave the dirty work for the machines and concentrate in doing what you are good at…Thinking.

Delegate – The breakpoint is whenever you can earn more than what costs you to delegate, you should look for someone to do it for you.
Delegate it’s hard mostly because we are afraid of losing control of something we see as ours. But it is a necessary pain if you want to grow your business or get more time for what you really enjoy.
Find someone with the right skills to do that task for you and free yourself. At the end you are still profiting in money and time.