Wouldn’t it be nice, if you could trigger a Power Automate Flow for a selected message directly from Outlook? Well, now you can! People have requested this functionality but it hasn’t been made available, so I decided to make it myself.
There are two steps to making this work:
- Creation of a small Outlook Macro and assigning that Macro to a button on the toolbar.
- Creation a Cloud Flow that is triggered on the action When a new email arrives (V3).
In practice, it is super simple to use. You simply click on a message, or messages in the main Outlook window and click a button on the ribbon. The macro will:
- Generate a JSON string of information about the message that you selected.
- Assign a particular subject to the email as the basis for the cloud flow trigger.
An example of the JSON produced by the Macro:
{
"from": "[email protected]",
"subject": "Power BI Invoice",
"internetMessageID": "<[email protected]>",
"folder": "Inbox"
}
As you can see, it contains four pieces of information:
- The sender of the selected message.
- The subject of the message.
- The Internet Message ID
- Which folder the message was in.
These values are used by the cloud flow to find the exact message that you selected when the flow runs using the $search parameter in the Get Emails action of the cloud flow.
Build the Outlook Macro
I have written the Macro for you, all you need to do is paste it in to ThisOutlookSession. You can access the VBA editor by pressing ALT-F11, or enabling the developer tab in Outlook.
The Macro code is below, don’t be put off by the amount of code, you’ll only ever need to edit one small part of it:
Option Explicit
'Duplicate this routine to create different trigger subjects.
Sub ExampleFlowStart()
'Modify this line to create a different trigger subject.
Dim triggerSubject As String: triggerSubject = "ExampleEmailTrigger12345"
'Leave everything below this line as is.
Dim myOlExp As Outlook.Explorer: Set myOlExp = Application.ActiveExplorer
Dim myOlSel As Outlook.Selection: Set myOlSel = myOlExp.Selection
Dim olItem As Outlook.MailItem
Dim x As Integer
For x = 1 To myOlSel.Count
Set olItem = myOlSel.Item(x)
If olItem.Class = 43 Then
Call triggerFlowForSelectedMessages(olItem, triggerSubject)
End If
Next x
End Sub
'Main routine that sends the email to fire the Power Automate trigger
Sub triggerFlowForSelectedMessages(olItem As Outlook.MailItem, triggerSubject As String)
Dim strFolder As String, JSON As String, sender As String
Dim messageFolder As Outlook.Folder
Dim oPA As Outlook.PropertyAccessor: Set oPA = olItem.PropertyAccessor
Const PR_INTERNET_MESSAGE_ID As String = "http://schemas.microsoft.com/mapi/proptag/0x1035001F"
'Find and convert the folder path
Set messageFolder = olItem.Parent
strFolder = Replace(Mid(messageFolder.FolderPath, (InStr(Mid(messageFolder.FolderPath, 3), "\") + 3)), "\", "/")
'Definition of JSON that will be sent to trigger the flow
JSON = "{" & vbLf & _
"'from': '{{from}}'," & vbLf & _
"'subject': '{{subject}}'," & vbLf & _
"'internetMessageID': '{{internetMessageID}}'," & vbLf & _
"'folder': '{{messageFolder}}'" & vbLf & _
"}"
'Replace JSON values
JSON = Replace(JSON, "{{from}}", GetSenderSMTPAddress(olItem))
JSON = Replace(JSON, "{{subject}}", olItem.Subject)
JSON = Replace(JSON, "{{internetMessageID}}", oPA.GetProperty(PR_INTERNET_MESSAGE_ID))
JSON = Replace(JSON, "{{messageFolder}}", messageFolder)
JSON = Replace(JSON, "'", Chr(34))
'Debug.Print (JSON)
'Send the message that Triggers the flow
Dim objMsg As MailItem
Set objMsg = Application.CreateItem(olMailItem)
With objMsg
.To = GetUserEmailAddress()
.Subject = triggerSubject
.BodyFormat = olFormatPlain
.Body = JSON
.Send
End With
End Sub
'Function to get the sender address of an email. Required for exchange accounts.
Function GetSenderSMTPAddress(mail As Outlook.MailItem) As String
Const PR_SMTP_ADDRESS As String = "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
If mail Is Nothing Then
GetSenderSMTPAddress = vbNullString
Exit Function
End If
If mail.SenderEmailType = "EX" Then
Dim sender As Outlook.AddressEntry
Set sender = mail.sender
If Not sender Is Nothing Then
'Now we have an AddressEntry representing the Sender
If sender.AddressEntryUserType = _
Outlook.OlAddressEntryUserType.olExchangeUserAddressEntry Or _
sender.AddressEntryUserType = _
Outlook.OlAddressEntryUserType.olExchangeRemoteUserAddressEntry Then
'Use the ExchangeUser object PrimarySMTPAddress
Dim exchUser As Outlook.ExchangeUser
Set exchUser = sender.GetExchangeUser()
If Not exchUser Is Nothing Then
GetSenderSMTPAddress = exchUser.PrimarySmtpAddress
Else
GetSenderSMTPAddress = vbNullString
End If
Else
GetSenderSMTPAddress = sender.PropertyAccessor.GetProperty(PR_SMTP_ADDRESS)
End If
Else
GetSenderSMTPAddress = vbNullString
End If
Else
GetSenderSMTPAddress = mail.SenderEmailAddress
End If
End Function
'Function to get the Primary email address of the current user
Function GetUserEmailAddress()
Dim outApp As Outlook.Application, outSession As Object, curr
Set outApp = CreateObject("Outlook.Application")
Set outSession = outApp.Session.CurrentUser
Set outApp = Nothing
GetUserEmailAddress = outSession.AddressEntry.GetExchangeUser().PrimarySmtpAddress
End Function
Lines 3 – 19 are the only part of the code that you need to be concerned about. In particular line 6 defines the value of the subject that is going to be used to trigger the flow. You can take this whole section of code, duplicate it and rename the subroutine to setup trigger buttons with different subjects (to produce different results). For example, you could duplicate it like this:
Sub StoreSupplierInvoices()
'Modify this line to create a different trigger subject.
Dim triggerSubject As String: triggerSubject = "TriggerStoreSupplierInvoices"
'Leave everything below this line as is.
Dim myOlExp As Outlook.Explorer: Set myOlExp = Application.ActiveExplorer
Dim myOlSel As Outlook.Selection: Set myOlSel = myOlExp.Selection
Dim olItem As Outlook.MailItem
Dim x As Integer
For x = 1 To myOlSel.Count
Set olItem = myOlSel.Item(x)
If olItem.Class = 43 Then
Call triggerFlowForSelectedMessages(olItem, triggerSubject)
End If
Next x
End Sub
The key is to use a subject line that you are unlikely to receive in your normal stream of emails. Next you need to assign the Macro to a button on your ribbon. On my ribbon, I have a button that looks like this:
If that button is clicked, you will receive a plain text email, back into your own Inbox that looks like this:
That is it for the Outlook part, when we have built the flow, it will be triggered by the new incoming email with the identifiable subject and begin to process it. During the processing it will also delete the trigger email.
Build the Cloud flow to process the Trigger
The cloud flow is really very simple to setup, I’ve also put all of the required actions into a scope that you can easily copy. Here is a screenshot, the only part you need to modify is the Subject filter in the first action:
The scope itself can be simply copied from the code below, directly into your flow:
{
"id": "793da215-6ea6-4ef0-b570-bc9f-ca19188a",
"brandColor": "#8C3900",
"connectionReferences": {
"shared_sharepointonline": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
}
},
"shared_office365": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_office365/connections/shared-office365-9474202e-bf2b-4861-96d8-aa8f94183a13"
}
}
},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "Process_Email",
"operationDefinition": {
"type": "Scope",
"actions": {
"Get_emails_(V3)": {
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_office365",
"operationId": "GetEmailsV3",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
},
"parameters": {
"folderPath": "@json(triggerOutputs()?['body/body'])['folder']",
"from": "@json(triggerOutputs()?['body/body'])['from']",
"fetchOnlyUnread": false,
"includeAttachments": true,
"searchQuery": "from:@{json(triggerOutputs()?['body/body'])['from']} AND subject:@{json(triggerOutputs()?['body/body'])['subject']}",
"top": 25,
"importance": "Any",
"fetchOnlyWithAttachment": false
},
"authentication": "@parameters('$authentication')"
},
"runAfter": {},
"description": "Find closely matching emails"
},
"Filter_array": {
"type": "Query",
"inputs": {
"from": "@outputs('Get_emails_(V3)')?['body/value']",
"where": "@equals(item()?['internetMessageId'], json(triggerOutputs()?['body/body'])['internetMessageID'])"
},
"runAfter": {
"Get_emails_(V3)": ["Succeeded"]
},
"description": "Match the email array against the Internet Message ID"
},
"Parse_Email_JSON": {
"type": "ParseJson",
"inputs": {
"content": "@first(body('Filter_array'))",
"schema": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"receivedDateTime": {
"type": "string"
},
"hasAttachments": {
"type": "boolean"
},
"internetMessageId": {
"type": "string"
},
"subject": {
"type": "string"
},
"bodyPreview": {
"type": "string"
},
"importance": {
"type": "string"
},
"conversationId": {
"type": "string"
},
"isRead": {
"type": "boolean"
},
"isHtml": {
"type": "boolean"
},
"from": {
"type": "string"
},
"toRecipients": {
"type": "string"
},
"attachments": {
"type": "array",
"items": {
"type": "object",
"properties": {
"@@odata.type": {
"type": "string"
},
"id": {
"type": "string"
},
"lastModifiedDateTime": {
"type": "string"
},
"name": {
"type": "string"
},
"contentType": {
"type": "string"
},
"size": {
"type": "integer"
},
"isInline": {
"type": "boolean"
},
"contentId": {
"type": "string"
},
"contentBytes": {
"type": "string"
}
},
"required": ["@@odata.type", "id", "lastModifiedDateTime", "name", "contentType", "size", "isInline", "contentId", "contentBytes"]
}
},
"body": {
"type": "string"
}
}
}
},
"runAfter": {
"Filter_array": ["Succeeded"]
},
"description": "Parse the Output of the Filter"
},
"Delete_email_(V2)": {
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_office365",
"operationId": "DeleteEmail_V2",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365"
},
"parameters": {
"messageId": "@triggerOutputs()?['body/id']"
},
"authentication": "@parameters('$authentication')"
},
"runAfter": {
"Parse_Email_JSON": ["Succeeded"]
},
"description": "Delete the email that triggered the flow"
}
},
"runAfter": {},
"description": "Finds the message that was selected and Parses"
}
}
To copy this into your Flow, in the Flow designer, simply copy the code above and then paste into your flow like this:
The dynamic content of Parse Email Action will now contain everything you need to manipulate the email. This is the process the flow takes:
- The flow is triggered on the arrival of a new email, which is generated by the Outlook Macro.
- The trigger has the unique subject filter applied to ensure it only runs in response to the Macro.
- The mailbox is then searched for matching emails based on the folder, the sender and the subject.
- These results are then filtered further to match emails with a matching Internet Message ID.
- Finally the result is put into a Parse JSON step to make the dynamic content available.
In my example I have a button labelled “Store Supplier Invoices”. This takes the attachments from a selected email and then puts them into a document library on SharePoint. When new items are added to the Supplier Invoice library, a new flow begins.
Let me know how you get on with the flow and if it is useful to you.
Leave a Reply