• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Trigger a Power Automate Flow directly for a selected message in Outlook

April 30, 2021 by Paulie 3 Comments

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:

  1. Creation of a small Outlook Macro and assigning that Macro to a button on the toolbar.
  2. 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:

Image showing button on the Outlook Ribbon that will trigger a Power Automate Flow

If that button is clicked, you will receive a plain text email, back into your own Inbox that looks like this:

Image of email created by an Outlook Macro which will trigger a Power Automate Flow

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:

Image of a Power Automate flow that is triggered for a selected email in Outlook

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:

  1. The flow is triggered on the arrival of a new email, which is generated by the Outlook Macro.
  2. The trigger has the unique subject filter applied to ensure it only runs in response to the Macro.
  3. The mailbox is then searched for matching emails based on the folder, the sender and the subject.
  4. These results are then filtered further to match emails with a matching Internet Message ID.
  5. 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.

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. H. Rile says

    May 25, 2021 at 4:12 pm

    Not sure why… but when I press the button… nothing is happening.. the email does not get sent…

  2. Hemdeep says

    August 26, 2021 at 3:50 pm

    You need to manipulate the content from where he left

  3. Philipp says

    September 7, 2021 at 6:07 pm

    Hello Paul,

    I implemented your Script for triggering a power automate flow for a selected message in outlook https://www.tachytelic.net/2021/04/trigger-power-automate-selected-message-outlook/
    It works great, but fails on some messages, so I investigated. I found that I got this error in the flow:

    ——–
    There is an unterminated string literal at position 64 in ‘”from:[email protected] AND subject:WG: Daten prüfen in Vi’.
    clientRequestId: 7d5db9aa-0a94-46da-af9d-381a259ffd61
    serviceRequestId: 4b46bf9d-64b3-407e-829a-4728593d33c6;5f46eb9c-f11f-4612-9d96-ef81878e3790
    ——–

    And indeed, there is a ‘&’ in the subject of the selected mail at position 64. So I wrote a VBA function to clean the subject string before sending the trigger email. It removes all non-ascii chars.

    Now the sent subject string is e.g. “WG Daten pr fen in Vi Va Antwort von Hr Werner” instead of the original “WG: Daten prüfen in Vi&Va – Antwort von Hr. Werner”. Which is fine for my purposes (I’d tweak the cleanup function a little more to make it look nicer)

    Although this solved the initial error, it unfortunately created a new one:

    ——–
    Unable to process template language expressions in action ‘Parse_Email_JSON’ inputs at line ‘1’ and column ‘35373’: ‘Required property ‘content’ expects a value but got null. Path ”.’.
    ——–

    I suspect the email cannot be found anymore by the “Get Emails” step in the flow (because the subject line is changed by the char-cleanup).

    Do you think, there is a way to fix this problem? Maybe there is a better way to fix the first error (unterminated string literal)?

    Thanks for any help.

    Philipp

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 259 other subscribers.

Go to mobile version