Power Platform

The standard actions in Power Automate make it simple to perform CRUD operations against a SharePoint list. But they are designed to be easy to use and do not focus on performance. This post will explain how to delete many items from a SharePoint list using the $batch API.

The standard Power Automate method

A flow to delete items from a SharePoint list is very simple to create and might look something like this:

This flow will work fine, and on a small list it would be perfect, but it has a number of problems:

• If the list contains more than 5,000 items, the Get Items action will have to be placed in a loop.
• It will consume a lot of API actions and may cause you to exceed your daily limit.
• It is very slow.

This flow took four minutes to complete on a small list containing only 500 items.

A few people from my YouTube Channel and others that have commented on the method I used to get more than 5,000 items from a SharePoint list have been looking for a faster and more efficient method.

The SharePoint Batch Method

It is possible to make batch requests of actions for SharePoint to execute using the OData $batch query option. There are many applications for this method.

Because of the increased complexity of this flow, I suggest that you would only use it if you want to speed up the process of deleting items or reduce the number of API actions that you are using.

This flow took 47 seconds to complete on the same list containing 500 items.

To build this flow I used the excellent documentation about the batch method from this website. The batch is a request to the SharePoint API that is formatted in a particular way. Here is an example request which would delete two items:

--batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda
Content-Type: multipart/mixed; boundary="changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a"
Content-Length: 166999
Content-Transfer-Encoding: binary

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE  https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2603) HTTP/1.1
Content-Type: application/json;odata=verbose
Accept: application/json;odata=nometa
IF-MATCH: *

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE  https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2604) HTTP/1.1
Content-Type: application/json;odata=verbose
Accept: application/json;odata=nometa
IF-MATCH: *

--changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a--

--batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda--

The API is quite fussy and if the request is not delivered in exactly the expected format it will fail.

Flow Detail

I have created a flow which, hopefully removes most of the complication associated with this method, which you can copy and paste into your environment. Here is a screenshot of the flow:

The Flow follows this process:

• Creates a variable used to hold the number of items found.
• A compose action defines the settings for the remainder of the flow.
  • The site address
  • The list name
• Another compose action creates a template for the change sets.
• A Do Until loop is started and continues until there are no items left.
  • Get Items collects the list items in batches of 1,000 items (this is the limit for a batch)
  • A select action creates the change set for each item.
  • A compose action joins the change set.
  • Finally a SharePoint HTTP action performs the request.

Flow Code and Implementation

To implement this flow, do the following:

• Create a new flow
• Add an Initialize variable action and name the variable itemCount.
• Copy and paste the Scope code below into your flow.
• Modify the settings action to create the correct values for your environment.
• Optional Step. Add an OData File to the Get Items action to narrow the selection of items to be deleted. Check out this excellent OData Cheat Sheet by Tom Riha for help with that.

Here is the code for the scope, which performs does the deletions.

{
	"id": "519d6f92-4bd5-4ffb-9f91-0086-c5119f9e",
	"brandColor": "#8C3900",
	"connectionReferences": {
		"shared_sharepointonline_1": {
			"connection": {
				"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
			}
		}
	},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Delete_SharePoint_Items",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"Do_until": {
				"type": "Until",
				"expression": "@equals(variables('itemCount'), 0)",
				"limit": {
					"count": 60,
					"timeout": "PT1H"
				},
				"actions": {
					"Get_items": {
						"type": "ApiConnection",
						"inputs": {
							"host": {
								"connection": {
									"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
								}
							},
							"method": "get",
							"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/tables/@{encodeURIComponent(encodeURIComponent(outputs('settings')['listName']))}/items",
							"queries": {
								"$top": 1000
							},
							"authentication": {
								"type": "Raw",
								"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
							}
						},
						"runAfter": {},
						"trackedProperties": {
							"ItemCount": "@{length(body('Get_items')['value'])}"
						},
						"metadata": {
							"flowSystemMetadata": {
								"swaggerOperationId": "GetItems"
							}
						}
					},
					"Select": {
						"type": "Select",
						"inputs": {
							"from": "@body('Get_items')['value']",
							"select": "@replace(outputs('Template'), '|ID|', string(item()['Id']))"
						},
						"runAfter": {
							"Set_variable": ["Succeeded"]
						},
						"description": "replace(outputs('Template'), '|ID|', string(item()['Id']))"
					},
					"BatchDelete": {
						"type": "Compose",
						"inputs": "@join(body('Select'), decodeUriComponent('%0A'))",
						"runAfter": {
							"Select": ["Succeeded"]
						},
						"description": "join(body('Select'), decodeUriComponent('%0A'))"
					},
					"Send_an_HTTP_request_to_SharePoint": {
						"type": "ApiConnection",
						"inputs": {
							"host": {
								"connection": {
									"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
								}
							},
							"method": "post",
							"body": {
								"method": "POST",
								"uri": "/_api/$batch",
								"headers": {
									"X-RequestDigest": "digest",
									"Content-Type": "multipart/mixed; [email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
								},
								"body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('BatchDelete'))}\nContent-Transfer-Encoding: binary\n\[email protected]{outputs('BatchDelete')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
							},
							"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
							"authentication": {
								"type": "Raw",
								"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
							}
						},
						"runAfter": {
							"BatchDelete": ["Succeeded"]
						},
						"limit": {
							"timeout": "P1D"
						},
						"metadata": {
							"flowSystemMetadata": {
								"swaggerOperationId": "HttpRequest"
							}
						}
					},
					"Results": {
						"type": "Compose",
						"inputs": "@base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])",
						"runAfter": {
							"Send_an_HTTP_request_to_SharePoint": ["Succeeded"]
						},
						"description": "base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])"
					},
					"Set_variable": {
						"type": "SetVariable",
						"inputs": {
							"name": "itemCount",
							"value": "@length(body('Get_items')['value'])"
						},
						"runAfter": {
							"Get_items": ["Succeeded"]
						}
					}
				},
				"runAfter": {
					"Template": ["Succeeded"]
				}
			},
			"Template": {
				"type": "Compose",
				"inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nDELETE  @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items(|ID|) HTTP/1.1\nContent-Type: application/json;odata=verbose\nAccept: application/json;odata=nometa\nIF-MATCH: *\n",
				"runAfter": {
					"settings": ["Succeeded"]
				}
			},
			"settings": {
				"type": "Compose",
				"inputs": {
					"siteAddress": "",
					"listName": ""
				},
				"runAfter": {},
				"description": "list to batch delete items from",
				"trackedProperties": {
					"batchGUID": "@{guid()}",
					"changeSetGUID": "@{guid()}"
				}
			}
		},
		"runAfter": {
			"Initialize_variable": ["Succeeded"]
		},
		"description": "Delete items from SharePoint using the $batch API method"
	}
}

The first step in the scope is the settings action. Modify this action to the correct values for your environment:

{
  "siteAddress": "https://SharePointSiteName.sharepoint.com/sites/ListName/",
  "listName": "someList"
}

Conclusion

If you need to delete many items from a list in an efficient manner this flow should improve your performance and reduce the number of API actions that your flow consumes. I hope you are able to implement it into your environment easily. Please drop me a question in the comments if you need further help.

I recently did a post on how to sum an array with Power Automate which used an Office Script to calculate the sum of an array. The reason I used an Office Script is because Power Automate does not have a built in action to do this simple operation. But I have now discovered a native method of summing an array in an instant.

The Standard Method

The standard method to sum an array in Power Automate generally follows this pattern:

• Define a variable to hold the total.
• Get an array of items.
• Loop through the array of items.
• For each iteration of the loop, increment the total variable by the current array item in the loop.

This method is fine and works perfectly well. The problem is that the performance is bad. Here is an example of a flow which uses the standard method to sum an “Invoice Amount” from a list with 500 rows:

In the example above, the flow took three minutes to execute. For a simple sum operation I think this is too long.

Instantly Sum an Array – A New Method

I discovered a new, native method to sum an array which I don’t think is widely known. Using the same example list I was able to sum the entire list instantly. The pattern of the flow is:

• Get Items (SharePoint list etc)
• Select action to make a simple array of items.
• Compose action to reformat the array.
• Use the xpath function to sum the array in a single step.

Here is a screenshot of the flow:

With this method I was able to execute the entire flow in 1 second.

Step by Step Explanation

Let’s go through the above example in depth and you will get a better understanding of how it works.

The Get Items action collects the items from the SharePoint list, but the data source is irrelevant. It could be any action that returns an array of results.

The Select action, which is changed to “Text” mode produces a simple array of numbers from the amount column. Example output:

[  3764.58,  2223.05,  4320.49,  544.46,  3464.76 ]

The JSON step (which is a compose action) creates an object to store the number array. Ready for the next step, which requires the array to be formatted in a way that can be converted to XML. The code, which you can copy and paste to your flow is:

{
  "root": {
    "Numbers": @{body('Select')}
  }
}

The result of this compose action is a JSON that has the following format:

{
	"root": {
		"Numbers": [
			3764.58,
			2223.05,
			4320.49,
			544.46,
			3464.76
		]
	}
}

This JSON is then passed into the XML function which transforms it to this:

<root>
  <Numbers>3764.58</Numbers>
  <Numbers>2223.05</Numbers>
  <Numbers>4320.49</Numbers>
  <Numbers>544.46</Numbers>
  <Numbers>3464.76</Numbers>
</root>

Now that the data is in XML format, the powerful xpath expression can be used. xpath has native summing functionality so it can sum the entire array in one action with the following code:

xpath(xml(outputs('JSON')), 'sum(/root/Numbers)')

The output of the action is the sum of the array.

Conclusion

Summing an array using apply to each method is fine if your array is small. But it’s really flawed:

• Even a modest sized array takes long time to process.
• It can easily consume large chunks of your daily request limits.
• It’s very inefficient.

I personally like to avoid apply to each actions whenever possible. The xpath method explained in this post is even faster than the Office Script method I previously posted, I tested it it with 100,000 items and it finished almost instantly.

This post explains how to extract text from Microsoft Word docx files using only built in actions in Power Automate. 3rd party actions exist, which are more probably more sophisticated and can certainly make this process easier.

docx files are actually zip files

The first thing to that is important to understand, is that a word docx file is actually a zip file that contains a number of folders and files. The root of the zip folder contains these files:

The word folder in the root of the zip file contains more files and folders:

Within the word folder, there is a file called document.xml (sometimes documentN.xml) which contains the actual document content, and this is the file which we will parse with Power Automate. My example word document looks like this:

The content of document.xml contains:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<w:document xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml" xmlns:w15="http://schemas.microsoft.com/office/word/2012/wordml" xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup" xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml" xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape" mc:Ignorable="w14 w15 wp14">
  <w:body>
    <w:p xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordml" w:rsidP="65D7FFCD" w14:paraId="2C078E63" wp14:textId="568EF955">
      <w:pPr>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
      <w:bookmarkStart w:name="_GoBack" w:id="0"/>
      <w:bookmarkEnd w:id="0"/>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t xml:space="preserve">How to extract </w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:b w:val="1"/>
          <w:bCs w:val="1"/>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t xml:space="preserve">text </w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="648E6FBB">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>from a Microsoft Word docx file</w:t>
      </w:r>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="4ECA4038">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>.</w:t>
      </w:r>
    </w:p>
    <w:p w:rsidR="65D7FFCD" w:rsidP="65D7FFCD" w:rsidRDefault="65D7FFCD" w14:paraId="77484B81" w14:textId="049555E8">
      <w:pPr>
        <w:pStyle w:val="Normal"/>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
    </w:p>
    <w:p w:rsidR="4ECA4038" w:rsidP="65D7FFCD" w:rsidRDefault="4ECA4038" w14:paraId="10A0E5FC" w14:textId="67D59CCA">
      <w:pPr>
        <w:pStyle w:val="Normal"/>
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
      </w:pPr>
      <w:r w:rsidRPr="65D7FFCD" w:rsidR="4ECA4038">
        <w:rPr>
          <w:sz w:val="24"/>
          <w:szCs w:val="24"/>
        </w:rPr>
        <w:t>This document explains how to extract text from a Microsoft Word document using standard Power Automate actions. The result isn’t perfect, but it should be good enough for basic usage.</w:t>
      </w:r>
    </w:p>
    <w:sectPr>
      <w:pgSz w:w="12240" w:h="15840" w:orient="portrait"/>
      <w:pgMar w:top="1440" w:right="1440" w:bottom="1440" w:left="1440" w:header="720" w:footer="720" w:gutter="0"/>
      <w:cols w:space="720"/>
      <w:docGrid w:linePitch="360"/>
    </w:sectPr>
  </w:body>
</w:document>

As you can see from the above, the text data is on lines 18,27,34, 41 and 66 of the XML file.

Step 1 – Extract the contents of the Word document

To be able to access the content of document.xml the docx file needs to be extracted first. Use the flow action Extract archive to folder to extract the docx file to a temporary folder. Make sure you set the overwrite option to Yes.

Note: You will not be able to select the word document from the file browser within the action because it filters the available files to show only files with a .zip extension. So you can either:

• Rename the docx file to .zip
• Put in the file path manually or use dynamic content from a previous step

In my flow, the action looks like this:

Step 2 – Filter the output of the extraction

The output of the Extract archive to folder action is an array of objects which contains information about every file extracted from the archive. This output needs to be filtered so that we can get the file Id of the document.xml. So add a filter array action and use the output of Extract archive to folder as the input for the filter. Click the edit in advanced mode link and use this filter expression:

@and(startsWith(item()['Name'], 'document'),endsWith(item()['Name'], 'xml'))

This will filter the array and narrow it down to just the file containing the document contents. Here is how my filter array looks:

Step 3 – Get the file content of document.xml

Add a Get file content action and use this expression for the file:

first(body('Filter_array'))['Id']

It should look like this:

Step 4 – Grab the content of the text elements

Finally, add a compose action and use the following expresison:

xpath(xml(outputs('Get_file_content')?['body']), '//*[name()=''w:t'']/text()')

Here is how it looks in my flow:

The xpath expression will grab each element named w:t and return an array of strings of the content found in those elements. Click here If you’d like to learn more about the structure of a word docx file. The output from my sample document produced the following array:

[
  "How to extract ",
  "text ",
  "from a Microsoft Word docx file",
  ".",
  "This document explains how to extract text from a Microsoft Word document using standard Power Automate actions. The result isn’t perfect, but it should be good enough for basic usage."
]

At this point you can either iterate through the results, or use a simple join expression to create a single string from the results. Here is a screenshot of the entire flow:

As you can see from the above, it is possible to Extract Text from a Word docx file with Power Automate quite easily, and a more sophisticated xpath expression could target specific regions of text required.

If you want to publish data stored in your Office 365 environment to your WordPress site, wpDataTables combined with Power Automate makes it very easy to do. The data source in Office 365 can be anything accessible to Power Automate (e.g. SharePoint list, Dataverse, Excel, SQL Server).

Check out the example below, which is being generated from a SharePoint list and nicely demonstrates some of the default functionality of wpDataTables.

Employees

wpDataTables can get table data from many different data sources. One possible source is a URL that contains a JSON array. Power Automate makes it simple to publish JSON data to drive the table, using the action When a HTTP request is received.

There are two steps required to generating a table like the example above:

• Generate the source JSON data for the table.
• Create a page or a post in WordPress that contains a wpDataTable.

Generating the JSON for the Table

There are two ways to generate the source data for the table, both methods have pros and cons.

The First Method uses the trigger action When a HTTP request is received.

• Pros
  • Provides always up to date data.
  • Very easy to configure the flow to provide the data.
• Cons
  • Requires Power Automate Premium
  • Request limits in Power Platform could prevent execution on a busy site.

The Second Method uses a recurrence flow to copy data to the WordPress server using the SSH Connector.

• Pros
  • Does not require Power Automate Premium
  • Does not expose your HTTP action to the client.
  • Number of page views will not impact your Power Automate Request limits.
  • Faster performance for the client.
• Cons
  • Data is refreshed on a schedule, so isn’t live data.
  • Requires you to have SSH access to your WordPress host.

Generate data using the HTTP Method

Here is the flow that generates the HTTP Response, it consists of only a few steps:

As you can see, it consists of only a few actions.

• The HTTP Trigger, which must have the method set to GET.
• A Get Items action, to retrieve the data from SharePoint
• A Select action to reshape the data and remove any columns that are not required.
• Finally a response action to return the results to the browser that requested the record set. It is important to set the Content-Type to application/json.

The URL for the HTTP Trigger is generated after the flow is saved. Click this link to see the JSON data generated for the example table in this post. Once the URL has been generated, it can simply be added as the data source in the wpDataTables table.

Generate Data Using the SSH Method

The core of this flow is the same as the HTTP method, but the trigger and method of delivery are different. Here is the flow:

As you can see from the image above, the flow is set to run every 30 minutes, which means it will execute 48 times per day. The HTTP version of the same flow would execute every time someone visits the page.

The create file action at the end of the flow connects to the the WordPress server and saves the output of the select action to a static file called employees.json. There are two advantages to storing a static file like this:

• No need for the browser client to lookup another host.
• The data is static so the response is near instant.

So if your list was a restaurant menu for example, then a lag in the data would be fine. But if it was displaying stock levels of fast moving products, then you’d use the live method.

Create the Data Table

Creating the data table is super easy and you can easily follow the video demo from the wpDataTables website. wpDataTables has loads of different display options and makes it very easy to expose data from your Office 365 environment when combined with Power Automate.

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]eurprd09.prod.outlook.com>",
	"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:

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.