In my previous post I talked about the simpler scenario for keeping my inbox clean and focused with one of my customer scenarios where the requirements were much simpler. For my personal email I mentioned that I have a number of different scenarios going on and I needed a more complex solution. I have refined my requirements a little since the previous post and my requirements for my personal email are now:

  • Move Flagged Emails to a TODO Folder
  • On Demand Cleanup Job
    • Run a job on demand
    • Any emails that I havent read yet, leave in the inbox for me to read when done
    • For emails I have read:
      • Check a list using the from email address and if that list specifies a folder move the mail to that folder
        • If there isnt an entry for the email address then add it to the list so I can define a rule if I want to
        • Note this will give me a nice list of all of the email addresses who have sent to me which might be handy too
      • Check a list of email domains and if the domain of the sender is in the list then move the email to the specified folder
      • Any thing that doesnt have a rule defined for it move to a folder called Needs Sorting so after the job is ran so I can refine my rules

References:

If you are reading this, you may also find these articles useful:

Video

If you want to watch a video walking through the content of this post please checkout the below video, a walk through is also below.

Move Flagged Email to TODO Folder

I discussed how I did this in the previous post (https://mikestephenson.me/2021/01/01/2021-new-year-clean-inbox-with-power-automate-part-1/)

On Demand Clean up Job

In this section ill discuss how I implemented the on demand clean up job.

Approach

My approach to tackling this problem will involve using a channel in microsoft teams to manage my email solution. I will then create 3 lists in the teams channel using Microsoft Lists / SharePoint Lists. The lists will be:

  • A list of email folders that I want to move stuff to
  • A list of all email addresses who send me emails and columns to allow me to configure a folder that their email should be sorted to
  • A list of email domains where I can configure a folder for all email from that domain to be sorted to

I will then implement a Power Automate flow which will be triggered on demand and it will check my email, workout if there is a rule in the Lists which tells the flow where to put the email and if not it will be moved to the needs sorting folder.

Technology Chosen

The tech I will use for this are:

  • Microsoft Teams / Lists to:
    • make it easy to maintain the list of email address rules
    • Keep a list of email folders and the id for the folder
  • Power Automate
    • Implement the workflow for the job
  • Power Automate Flow Buttons
    • To trigger the job from my mobile when I want to run it

Limitations

There are a few limitations in some of the tech/API’s that are a bit of a pain to be aware of:

  • Move Email Folder ID

The action to move an email needs you to select a folder or dynamically provide a folder id. This is a bit of a pain, you cant just provide a path. This is due to the way the graph api works for mail. Ill blog a bit more about how to do this in a seperate post.

  • Outlook API Get Emails

The get emails action gets upto the top 25 emails in your inbox. So that it doesnt keep getting the same 25 over and over ill move any that are unread to a temp folder in outlook and then move them back to the inbox at the end of the job.

My Flows

The below picture shows the 4 flows I have used to work together to implement this approach.

While this may seem like a lot of flows, there is a reason for this. I have encapsulated the logic for getting the routing rules into child flows to keep things simpler.

CleanInbox gets triggered and loops over each email moving it to the temp folder if its not read, and if it is read then it will call the CleanInbox-ProcessEmail flow. This child flow will do most of the work for each email and it also allows me to test it in isolation to help me build it effectively. CleanInbox-ProcessEmail will call CleanInbox-GetEmailRouting to see if there is a rule specifically for the email address and if there is, it will move it to the appropriate folder. If there is no rule for the email address it will then call CleanInbox-GetEmailDomainRouting to check if there is a rule for the domain and if there is then it will move the email to the appropriate folder and finally if there are no rules then CleanInbox-ProcessEmail will move the email to a folder called Needs Sorting so any emails that havent got rules I can manually sort and add rules if I think they are needed for the future.

Lets now take a look at some of the individual bits in the implementation.

Teams / SharePoint

In teams I created a team called “Mike – Personal Productivity” which ill now use to put any stuff which I can use to help make myself more productive.

Inside the team there is the email management channel and there are the 3 lists in here:

You can see in the above picture how I have added a tab for each list and in the picture you can see I have the email folders list. This list has a friendly name for each folder and the outlook id for that folder for the move of emails dynamically. I will add columns to the other lists so I can reference the folders by their friendly name and then at runtime my Flow will be able to get the id for moving the email.

The email domain mapping list contains a list of domains and a target folder. The target folder is a column reference to the email folders list so it allows me to reference the id for the folder simply by picking the friendly name for the target folder.

I also added an ignore columns to I can easily turn a rule off without needing to delete it.

The email address mapping list is very similar like below

It also has a column which references the email folders list so I can choose a destination folder to move email to. I also have an ignore column so i can ignore an email address too and I chose to add an Is New column as well because in my flow I decided that automatically adding a new email address with a default state of ignore so that its easy to add new rules if I want to and the email address should already be there. The is new flag defaults to Yes so I can easily track which new emails addresses have been added and that I havent reviewed yet. Every now and then Ill review the email addresses in the list and add a rule if needed and otherwise Ill ignore them.

CleanInbox-GetEmailDomainRouting

If we start by taking a look at the CleanInbox-GetEmailDomainRouting flow.

Here I am accepting a json message over HTTP to this flow and ill check the list from teams using the SharePoint Get Items action. The input to the flow is a json matching this schema:

{
  "type": "object",
  "properties": {
    "EmailId": {
      "type": "string"
    },
    "FromEmailAddress": {
      "type": "string"
    },
    "FromDomain": {
      "type": "string"
    }
  }
}

It will accept a json looking like this:

{
  "EmailId": "AAMkADIwOTJmN2E4LTllOTUtNGFmZC05NzhiLTMzMzJjOGFhNDcwYQBGAAAAAAAuGudwjWhtRpIMe7a_sJ6uBwCGSHbTe4MbQ7gmtUzO23QnAAAApxV0AAAXkyYc7PloRK5Be0BK0GE2AANNBGc3AAA=",
  "FromEmailAddress": "donotreply@ring.com",
  "FromDomain": "ring.com"
}

The flow will also response with a json with 2 properties, ignore will be a bool telling if the email domain should be ignored or not and outlook folder id will tell which folder the email should be moved to. This is shown below:

The logic for this flow is that the parent flow calling it will have read an email from outlook and will pass the id, the email address and domain. This flow will then use that SharePoint Get Items connector like below to pass the email domain and query it using the odata filter based on the title column.

When I get the results from SharePoint I will loop over them (there will be only 1 because the title will only match 1 record) and ill extract the folder id if there is a match and also set the ignore flag to true as shown below.

The net result is that I now have a helper flow which will workout if an email domain should be mapped to a folder or not based on the SharePoint list.

CleanInbox-GetEmailRouting

The CleanInbox-GetEmailRouting flow works from the same principal but is slightly more complex. The inputs and outputs to this flow are exactly the same as for CleanInbox-GetEmailDomainRouting however the logic is slightly different as you can see below.

In this case you might notice the condition step. If there is no item in the list for the email address then I will add the email address so that I can at some future point add an email address if I choose to. We will take a look at this in a moment.

We start the flow by checking the other List with the SharePoint connector. This time we use the email address and check the Email Address Mapping list.

In the next step I am using a variable which will measure the length of the array of results to see if we have a match.

If there is no match then I will add the new email address to the list so I can add a rule later if I choose

If there was a match then I check if the item is marked as ignore and if it is then I will not use it, but if its not to be ignored then I will get the folder id.

Because the column in my list is linked to the other list then I can get the value without having to do a separate lookup to the other list. This is handy!

I can now return the response indicating if there is a rule for the mapping by email address and what folder id to use.

CleanInbox-ProcessEmail

The CleanInbox-ProcessEmail flow is the one that does the main work to process each email. It will check if the email should be moved and move it.

This flow takes the simple json with the email id, the from address and the from domain.

{
  "EmailId": "AAMkADIwOTJmN2E4LTllOTUtNGFmZC05NzhiLTMzMzJjOGFhNDcwYQBGAAAAAAAuGudwjWhtRpIMe7a_sJ6uBwCGSHbTe4MbQ7gmtUzO23QnAAAApxV0AAAXkyYc7PloRK5Be0BK0GE2AANNBGc3AAA=",
  "FromEmailAddress": "donotreply@ring.com",
  "FromDomain": "ring.com"
}

You can see in the picture above I have broken the flow into scopes to make it easier to see what happens in each bit. If we work up from the bottom, the default scope at the bottom will simply move the email to the Needs Sorting folder. This happens if nothing else has sorted the email.

Next up the Move based on Domain will call our child flow we talked about earlier in the article using the HTTP action and simply passing over the input json. using the response we can workout if we should move the email by domain and we know the folder we should move it to.

One point to note is that if you have the folder id you will need to add an id:: in front of the value to dynamically set the folder as shown below rather than picking it with the folder selector.

Notice that in this scope we also return a response and terminate the flow if we move the email here. I find this helps keep the logic simpler rather than having lots of nested conditions to cover all of the different scenarios.

Next up the move email based on email address scope is exactly the same as the scope for move based on domain, the only difference is that it uses the other child flow which will check the list for email address mapping rather than domain mapping. It will again call it over http.

CleanInbox

My clean inbox flow is exactly the same as the one in the part 1 post about the simpler version of this but with the following changes:

  • At the start its triggered from a flow button using the flow button trigger
  • At the end I make it write a message to teams so I know when its finished

The actions I am performing here are to go through the emails using the until loop like in part 1 and moving everything thats unread to the temp folder (and moving it back in the 2nd until look at the end) and for each email that is read I call the ProcessEmail flow so it is compared against my rules and moved as appropriate.

Running the Job

Now I can go to the flow buttons on my mobile and trigger the flow whenever I want.

My flow will run in the background and do its clean up tasks and when complete ill get a message in teams telling me what it did.

I now know that regular emails are all sorted into the folders I like them in and anything which doesnt have a rule is in the Needs Sorting folder.

I have found this makes my inbox loads easier to manage and its very simple to get rid of that depressing feeling of looking at your email and thinking your going to spend the next hour working out what needs deleting and keeping. When its complete I also have all of the unread mail still in my inbox helping me focus on what I need to do next.

Conclusion

I really wish Outlook would just add something out of the box which supported something like an on demand clean up job. I do like outlook rules but its a major pain that they can only run from events, or you have the sweep rules but they run “at regular intervals” which is very unhelpful because you dont really have control of when they run and I find them to be not very useful. The idea of an on demand or nightly clean up job where you can specify some rules makes so much sense.

While this solution is a bit of a pain to implement it really wouldnt be that difficult for the product team to use Power Automate and Teams or something else behind the scenes to automate this for you so hopefully this can be an out of the box feature. Maybe something is there but ive had a damn good look for a way to do this and I cant find anything so ive built my own. Hopefully for people with a lot of email to manage then this will work well for now and hopefully it might inspire someone else who hates boring repetitive admin as much as myself to automate this in an even better or more effective way.

 

Buy Me A Coffee