Parse and save email receipts, then add them to an Excel table for tracking.

ForTechSupport · ‎02-06-2021

Power Automate: Save and track email receipts

Overview:

In this tutorial, you will learn how to create a flow in Power Automate that will parse receipts received by email for the total amount paid, then save an html copy of the receipt, add the purchase to a ledger with a link to the saved copy, and finally delete the email before sending a push notification to your mobile device letting you know that it was successful. For this tutorial, I will be using the purchase of an Amazon Kindle book as an example, but this can be used as a template for receipts (or bills) from other vendors too.

Instructions:

Start by creating a folder in OneDrive where you want to save the receipts to. I will be saving mine in a ‘Receipts’ subfolder within a folder called ‘Finances’. You will notice the timestamps in my recent purchases match the timestamps in the filenames. That will be covered later.

Create an Excel file called ‘Budget.xlsx’ and save it to OneDrive in the ‘Finances’ folder. Then create a table called ‘Ledger’ and use the following columns in order from left to right (A-I).

Date, Amount, Company, Category, Document, DocURL, DocType, Created, Notes

Format the Date and Created columns to M/D/YYYY

Format the Amount column to $ with 2 decimal places.

Format the Document column as a hyperlink using =HYPERLINK(F2,G2) and save the file again.

Next, we will create an Outlook rule to move the new receipts from the Inbox into a different folder. For this example, I am using Inbox\Finances\Receipts for my folder.

Create an Outlook rule that moves emails with the following criteria into the ‘Receipts’ folder.

From: digital-no-reply@amazon.com
In the Subject: Amazon.com order of

Now you can start building the flow by going to https://flow.microsoft.com and signing in using your Microsoft account. Once you are signed in, click the Create button on the left side.

Then click Automated cloud flow

Name the flow 'Receipts: Amazon Kindle', search the available triggers for 'email arrives', select 'When a new email arrives (V3)' then click the Create button.

Change the Outlook Folder from Inbox to Receipts by selecting the folder icon and browsing to it. Then click 'Show Advanced Options'.

In the From field, enter ‘digital-no-reply@amazon.com’, select Yes to Include Attachments, and enter ‘Amazon.com order of’ for the subject filter.

For the next step, we will search the email for the information to parse. In this case, we will be looking for the Grand Total of the purchase. Here is an example of a Kindle receipt email.

Click the New Step button, search for and select the action called ‘Compose’ and select the Body from the email in the flow trigger.

Click the Save button, then click the back arrow near the flow name.

Make sure the flow is turned on. If it says ‘Turn Off’ at the top, the flow is on already.

Now we want to find an old receipt that we can use to find the information we need to parse. If you already have one available, you can put it in your Inbox folder, then run the Outlook rule we created earlier so that it will move the email to the ‘Receipts’ folder and trigger the flow to run. Once that’s done, you can return to your flow and you may need to click the refresh arrow until the flow run appears. This may take a few seconds or a few minutes depending on which Power Automate plan you have. Once you see it, click the timestamp to check the steps it took.

Click to expand the Compose action and copy everything in the Outputs section.

Open a text editor and paste the Outputs inside and search for ‘Grand Total’. It only appears once in this email, so it should be easy to find. We will need the text directly before and after the information we are parsing (highlighted in the example below).

Now that we know that information, we will add the additional actions to our flow. The next 3 will all use the same type of action called ‘Initialize Variable’ Edit the flow, click New Step, and select that action.

Name the first variable 'AmountPaid1', change the Type to 'String', and select the Body of the email as the Value.

Name the second variable 'AmountPaid2' and change the Type to 'String'. Click Value, switch from Dynamic content to Expression, enter the following, and click OK. The name of the previous variable and part of the text from our email code is in this.

last(split(variables('AmountPaid1'), 'Grand Total:</strong></td><td class="total" style="font-size:14px; font-weight:bold; text-align:right; line-height:18px; padding:0 0 10px 10px; vertical-align:top; font-family:Arial,sans-serif"><strong>$'))

Name the third variable 'AmountPaid3' and change the Type to 'String'. Click Value, switch from Dynamic content to Expression, enter the following, and click OK. The name of the previous variable and the other part of the text from our email code is in this.

first(split(variables('AmountPaid2'), '</strong>'))

For these next two steps, we will format the timestamp of the email for use in the filename and the ledger. Select the action called ‘Convert time zone’ and rename the first one ‘Timestamp: Filename’. Choose the Received Time of the email for the Base time, UTC for the Source time zone, your local time zone for the Destination time zone, and a custom value of

yyyyMMdd-hhmmss

For the next one, use all the same information, except instead of a custom value in the format string, use the Short date pattern.

Now that we have collected everything we need, we will save an html copy of the email receipt to OneDrive. Select ‘Create file’ for the next action, select the path in OneDrive where you are saving the receipts to. (ie /Finances/Receipts) and select the Body of the email from the flow trigger for the ‘File Content’ field. For the File Name field, enter AmazonKindle.html, then move the cursor to be at the end of the word Kindle and before the .html file extension. Select ‘Converted time’ (from the action Timestamp: Filename).

Select ‘Add row into a table’ for the next action, then choose the location of the ‘Budget.xlsx’ file we created earlier, select the ‘Ledger’ table, choose the ‘Converted time’ (from the action 'Timestamp: Ledger') for the Date and Created fields, the AmountPaid3 variable for the Amount field, enter ‘Amazon Kindle’ for the Company, ‘Purchases’ for the Category, and ‘Receipt’ for the DocType. Finally add the URL of the document to the DocURL field in the following format where [FILEPATH] should be the content from the action 'Create file', your domain instead of contoso, and your email address instead of adam_contoso_com Make sure to use underscores instead of @ or . symbols:

https://contoso-my.sharepoint.com/personal/adam_contoso_com/_layouts/15/onedrive.aspx?id=/personal/a...

This next step is optional, but since I like to keep my mailbox clean, I have chosen to add an action to delete the email from my mailbox after a copy has been saved. Select ‘Delete email’ from the list of actions and select ‘Message Id’ from the email in the flow trigger.

I use the Power Automate mobile app also, so for the final action on this flow, I added a push notification that gets sent to the app installed on my mobile device. Enter 'You have a new receipt from Amazon Kindle' in the Text field, 'Link to document' in the Link label field and select DocURL from (from 'Add a row into a table') for the Link field.

Save the flow and back out of it. Refresh the webpage, then go back into edit it again. Expand the ‘Create file’ action and make sure the .html file extension still appears there. Sometimes the first time you save a flow with that, it may remove the extension, so if it is missing, add it back, save the flow again, then re-check. It should remain there the second time.

----------------------------------------------------------------------------------------------------------------------------------------

And finally make sure the flow is turned on, then go purchase a Kindle book to test it.

----------------------------------------------------------------------------------------------------------------------------------------

watch?v=fW44xULNVhY