How to email a weekly sales report from Excel with Power Automate

Open your orders workbook (Orders Log in the video). Select all the columns, choose Format as Table and pick any style. Note the table name shown in the ribbon. In the video it is Orders.

Power Automate can only read rows from an Excel table, not a plain range.

In Power Automate select Create, then Scheduled cloud flow. Name it Weekly Sales Report. Set it to repeat every 1 Week on Monday and select Create.

Open the Recurrence trigger to set your Time zone and the exact time. In the video I set the hours to 9 and the minutes to 30, so the preview reads "Runs at 9:30 on Monday every week".

Add List rows present in a table from the Excel Online (Business) connector. Set Location to OneDrive for Business, Document Library to OneDrive, browse to the Orders Log file and pick the Orders table.

Under Advanced parameters, add DateTime Format and choose ISO 8601. This matters: without it, Excel dates often arrive in Power Automate as serial numbers that you cannot filter by date.

Add a Compose action and rename it Start Date. In the input, open the expression editor and enter an expression that takes today's date and subtracts seven days. Because the flow runs on Monday, the result is the previous Monday.

Add a second Compose action named End Date with an expression that subtracts one day, which gives the previous Sunday.

The exact expressions are in the downloadable guide linked under the video. They follow this pattern:

• Start Date: formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')
• End Date: formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')

Add a Filter array action and rename it Filter after last Monday. In From, select body/value from List rows present in a table.

For the condition, pick the Order Date column on the left, choose is greater than or equal to, and select the Outputs of Start Date on the right. This drops everything before the previous Monday.

A Filter array holds one condition in the basic editor, so add a second Filter array named Filter before this Monday. Set From to the Body of the first filter.

On the left you cannot pick Order Date from the Excel action any more, because this filter reads the first filter's output. Use an expression that reads the order date of the current item instead, for example formatDateTime(item()?['Order Date'],'yyyy-MM-dd'). Choose is less than or equal to and select the Outputs of End Date on the right.

Together the two filters return only orders from the previous Monday through Sunday.

Add Initialize variable named Total Orders, type Integer, value 0. Add a second one named Weekly Sales, type Float, value 0.

Add Apply to each and rename it Calculate weekly totals. Set its input to the Body of Filter before this Monday.

Inside the loop:

• Add Increment variable, rename it Count orders, pick Total Orders and set the value to 1.
• Add another Increment variable, rename it Add order total, pick Weekly Sales, and for the value use an expression that reads the current row's order total, such as float(items('Calculate_weekly_totals')?['Order Total']).

The float() function makes Power Automate treat the Excel value as a number before adding it. If no orders match, the loop is skipped and both variables stay at zero.

Below the loop, add a Select action (Data Operation) and rename it Prepare report table. Set From to the Body of Filter before this Monday.

In the map, add one row per column you want in the email, each with an expression as the value:

• Order ID: item()?['Order ID']
• Customer: item()?['Customer']
• Product: item()?['Product']
• Quantity: item()?['Quantity']
• Total: item()?['Order Total']
• Payment: item()?['Payment Status']

Expressions are needed here because the data comes from the filter, not straight from the Excel action. You can rename, add or remove columns.

Add Create HTML table (Data Operation). Set From to the Output of Prepare report table. Under Advanced parameters, leave Columns set to Automatic.

Add Send an email (V2) from Office 365 Outlook and enter the recipient.

Paste the subject from the email template in the downloadable guide and replace its two placeholders with the Outputs of Start Date and End Date.

For the body, switch the editor to code view and paste the HTML from the guide, replacing what is there. Then replace each placeholder with dynamic content (type / and choose Insert dynamic content):

• Start date and end date: the two Compose outputs
• Total orders: the Total Orders variable
• Weekly sales: the Weekly Sales variable
• Report table: the Output of Create HTML table

The styled HTML is what makes the report look clean. The default HTML table on its own looks plain and unformatted in the inbox.

Save, select Test, choose Manually and run the flow. Check the recipient's inbox. The email shows the reporting period, total orders, total sales and the full order table.

The flow sends one email no matter how many rows the file has. If no orders are found, it still sends the email with zero orders, zero sales and an empty table.