Automate Monthly Excel Report Emails

Build a scheduled Power Automate pipeline that refreshes Excel files and emails them.

Introduction

30 Second Summary

Every month, the same routine hits. You open dozens of Excel files, wait for each one to refresh its data, apply filters, and email them out one by one. It eats hours of your time for work that should happen automatically.

In this project, you will build an automated monthly reporting pipeline using Power Automate that refreshes Power Query data in your Excel files, applies your filter and sort settings, and emails each file to the right person.

What You'll Build

A fully automated monthly reporting pipeline that combines a scheduled cloud flow with a desktop flow to refresh, process, and distribute Excel reports stored in SharePoint.

By the end of this project, you'll have:

A scheduled cloud flow that triggers on the 1st of every month to kick off your reporting pipeline.
A desktop flow that opens Excel files from SharePoint, refreshes Power Query data, applies filtering and sorting, and saves the results.
An automated email delivery system that sends each refreshed file as an attachment to its designated recipient.
Secret Mission: Add error handling and a Teams notification summary that reports which files succeeded, which failed, and total processing time.

Are there any prerequisites?

You need a Microsoft 365 account with Power Automate Premium capabilities (which includes RPA attended mode and premium connectors). You also need Excel files with Power Query connections stored in SharePoint and a Windows PC with Excel desktop installed.

Before We Start

Before We Start...

Set Up Power Automate Desktop and Register Your Machine

Before you can automate anything, your Windows PC needs a way to talk to the Power Automate cloud service.

In this step, you will install the Power Automate Desktop app, register your machine so cloud flows can trigger desktop actions, and confirm that your SharePoint-hosted Excel files refresh correctly.

In this step, get ready to:

Install Power Automate Desktop and sign in with your work account.
Register your machine with the Power Automate cloud service.
Verify that a SharePoint Excel file with Power Query connections refreshes successfully.

Install Power Automate Desktop

Power Automate Desktop is the app that runs desktop flows on your machine. Desktop flows let Power Automate control applications installed on your PC, like Excel.

Press the Windows key to open your search bar.
Type Microsoft Store and press Enter to open the Microsoft Store app.
In the Microsoft Store search bar, type Power Automate and press Enter.
Select Power Automate from the results (the app published by Microsoft Corporation).
Click Get or Install and wait for the installation to complete.
Once installed, click Open to launch Power Automate Desktop.
Sign in with your work Microsoft 365 account when prompted.

Alternative download link

If you prefer, you can download Power Automate Desktop directly from https://aka.ms/pad instead of using the Microsoft Store.

After signing in, you should see the Power Automate Desktop console with your account name in the top-right corner.

Why desktop flows instead of cloud-only?

Microsoft documents that Office Scripts running in Power Automate cloud flows cannot refresh Power Query data connections. The refreshAllDataConnections method only works when PowerBI is the data source.

Desktop flows solve this by opening Excel on your actual machine, where Refresh All works exactly as it does when you click it manually. This is the only reliable automated method for refreshing Power Query in Excel files on SharePoint.

Installation not working?

Make sure you are signed into the Microsoft Store with a Microsoft account. If the Get button is greyed out, try restarting the Store app.

Power Automate Desktop is not available on Windows 10 Home or Windows 11 Home editions. Confirm you are running a Pro or Enterprise edition.

help me install Power Automate Desktop on my Windows PC

Register your machine with Power Automate cloud

Registering your machine creates a secure connection between your PC and the Power Automate cloud service. Once registered, cloud flows can trigger desktop flows on this machine.

In Power Automate Desktop, click the Settings gear icon (top-right area of the console).
Look at the Machine settings section. Your machine should already appear as registered because signing in with direct connectivity registers it automatically.
Confirm you can see your machine name and machine environment listed.

What does registration do?

Registration generates a secure key pair so your machine and the Power Automate cloud can communicate safely. After registration, the machine appears as a manageable resource in the Power Automate web portal.

Now verify the registration from the cloud side.

Open your browser and go to make.powerautomate.com.
Sign in with the same work account you used in Power Automate Desktop.
In the left navigation menu, expand Monitor.
Click Machines.
Confirm your machine appears in the list with a status of Connected.

You should see your machine name listed with a green Connected status indicator.

Machine not showing as Connected?

Make sure Power Automate Desktop is still running and signed in on your PC. The machine status only shows as Connected while the desktop app is active.

If your machine does not appear at all, go back to Power Automate Desktop, click Settings, and confirm the correct environment is selected under Machine environment.

Corporate firewalls or proxies can block the registration. Your network must allow outbound connections to *.servicebus.windows.net and *.dynamics.com.

help me troubleshoot my Power Automate Desktop machine registration

Verify your SharePoint files can be refreshed

Before automating the refresh process, you need to confirm that Excel can open your SharePoint files and pull fresh data through Power Query manually.

Open Excel on your desktop.
Click File in the top menu.
Click Open.
Select your SharePoint site from the list of locations on the left.
Navigate to one of your Power Query Excel files and open it.
Once the file is open, click the Data tab in the Excel ribbon.
Click Refresh All in the ribbon.
Wait for the refresh to complete. You can check the status bar at the bottom of Excel for a "Retrieving Data" message that disappears when done.
Confirm your data has updated successfully (look for refreshed dates, new rows, or updated values).
Close the file (you can save or discard changes).

What does Refresh All do?

Refresh All tells Excel to re-run every Power Query connection in the workbook and pull the latest data from each external source. This is the same action your desktop flow will automate in a later step.

Refresh failing or hanging?

If you see a credentials prompt, enter your work account credentials. SharePoint data sources require authentication the first time you refresh from a new machine.

If the refresh hangs for more than a few minutes, the data source your Power Query connects to may be unreachable. Check that you have network access to the source (database, API, or file location).

help me troubleshoot a Power Query refresh failure in Excel

Your environment is ready. Power Automate Desktop is installed, your machine is connected to the cloud, and your Excel files refresh without issues. Next up, you will create a configuration list in SharePoint that tells your automation which files to process and who should receive them.

Create a Configuration List in SharePoint

Your machine is registered and your Excel files refresh successfully. Now you need a way to tell the automation which files to process and who should receive each one.

Instead of hardcoding file paths and email addresses into the flow itself, you will create a SharePoint list that acts as a configuration table. Each row maps one Excel file to its recipient and specifies the filter and sort rules to apply after the refresh.

In this step, get ready to:

Create a SharePoint list with columns for file paths, recipients, and filter/sort rules.
Populate the list with 2-3 test entries using your own email address.

Create the Monthly Report Config list

A single SharePoint list will hold all the information your cloud flow needs to process each report. Every row represents one file and its delivery rules.

Navigate to your SharePoint site (the same site where your Excel files are stored).
In the command bar at the top of the site, click + New.
Select List from the dropdown menu.
On the "Create a list" page, select Blank list.
In the Name field, enter Monthly Report Config.
Click Create.

SharePoint creates the list and opens it. You will see an empty list with only the default Title column.

Why use a SharePoint list as configuration?

When you store file-to-recipient mappings in a list, adding a new report to the automation is as simple as adding a row. You never need to edit the flow logic itself.

This is a common production pattern for data-driven Power Automate flows. It separates the "what to process" from the "how to process it."

Add columns for file paths, recipients, and filter/sort rules

Your list needs six custom columns beyond the default Title column. Each column stores one piece of configuration that the desktop flow and cloud flow will read at runtime.

In the list view, click + Add column in the column header row.
Select Single line of text as the column type.
In the Name field, enter FileName.
Click Save.

Repeat this process to add the remaining columns. Use the settings below for each:

Column name: FilePath / Type: Single line of text. This stores the full SharePoint URL or relative path to the file.
Column name: RecipientEmail / Type: Single line of text. This stores the email address of the person who should receive the report.
Column name: FilterColumn / Type: Single line of text. This is the name of the column in the Excel file to filter on.
Column name: FilterValue / Type: Single line of text. This is the value to filter by in that column.
Column name: SortColumn / Type: Single line of text. This is the column in the Excel file to sort by.

For the final column, you will use a different type:

Click + Add column once more.
Select Choice as the column type.
In the Name field, enter SortOrder.
In the choices area, add two options: Ascending and Descending.
Click Save.

Your list should now show all seven columns in the header row: Title, FileName, FilePath, RecipientEmail, FilterColumn, FilterValue, SortColumn, and SortOrder.

Don't see all your columns?

If a column doesn't appear in the list view after saving, click the column header area and look for + Add column at the far right. SharePoint sometimes hides newly added columns from the default view.

You can also click the gear icon in the top right, select List settings, and confirm all columns are listed under the Columns section.

help me troubleshoot my SharePoint list columns not appearing in the default view

Add test entries to the configuration list

With the structure in place, you will add 2-3 rows so you can test the full automation pipeline later. Use your own email address as the recipient for all test entries so you can verify delivery without emailing colleagues.

In the list command bar, click + New item (or click New in the command bar).
Fill in each field for your first test file:
Click Save.
Repeat the process to add 1-2 more entries for different Excel files on your SharePoint site.

What should I put in FilePath?

Open one of your Excel files directly in SharePoint (click the file name from your document library). Copy the URL from your browser's address bar. It will look something like https://yourcompany.sharepoint.com/sites/YourSite/Shared Documents/Sales_Report_Jan.xlsx.

Paste this full URL into the FilePath column. The desktop flow will use this path to locate and open the file.

Use grid view for faster data entry

Click Edit in grid view in the command bar to type directly into cells like a spreadsheet. This is faster than opening the form for each row.

Verify that your list shows 2-3 rows of data with all fields populated. Each row should have a valid SharePoint file path, your email address, and a filter/sort configuration that matches real columns in the corresponding Excel file.

Not sure which columns exist in your Excel file?

Open the Excel file from SharePoint and check the column headers in the worksheet you want to filter and sort. The FilterColumn and SortColumn values must match the exact column header text in your Excel file (case-sensitive).

help me figure out which column names from my Excel file to use in my SharePoint configuration list

Your configuration layer is ready. Next up, you will build the desktop flow that reads these settings and uses them to open, refresh, and manipulate each Excel file automatically.

Build the Desktop Flow for Excel Refresh

Your configuration list is ready with file paths, recipients, and filter/sort rules. Now you need the engine that actually opens each Excel file, refreshes its data, and saves it.

Microsoft's cloud-only approach cannot refresh Power Query data in Excel files. This is a documented limitation. The solution is a desktop flow that controls Excel on your local machine, triggering the refresh just like you would manually.

In this step, get ready to:

Create a new desktop flow with input variables for dynamic file handling.
Add Excel launch, refresh, and wait actions to handle Power Query connections.
Apply filtering and sorting, then save and test the flow.

Create a new desktop flow and add input variables

Your desktop flow needs to accept information from the cloud flow about which file to process and how to filter it. Input variables make this possible.

Switch back to Power Automate Desktop on your taskbar.
Click + New flow in the console.
Enter Refresh Excel Report as the flow name.
Click Create. The flow designer opens in a new window.

You should see the flow designer with an empty workspace, an Actions pane on the left, and a Variables pane on the right.

Now add the five input variables that the cloud flow will pass in later.

In the Variables pane on the right side of the designer, click the + (plus) button.
Select Input.
In the New input variable dialog, set Variable name to FilePath.
Set Data type to Text.
Set External name to FilePath.
Click Update to save the variable.
Repeat this process four more times to create these input variables (all with Data type Text): FilterColumn, FilterValue, SortColumn, SortOrder.

What are input variables?

Input variables let you pass data from a cloud flow into a desktop flow at runtime. When the cloud flow triggers this desktop flow, it fills in values like the file path and filter criteria from your SharePoint configuration list.

The External name is what appears in the cloud flow designer when you connect the two flows later. Keep it matching the Variable name for clarity.

After creating all five, you should see FilePath, FilterColumn, FilterValue, SortColumn, and SortOrder listed under the Input variables section of the Variables pane.

Don't see the Input option?

Make sure you are clicking the + button in the Variables pane (right side), not in the Actions pane. The Variables pane shows a dropdown with Input, Output, and Flow variable options.

Also confirm you are not using a reserved keyword as a variable name. Words like input, output, and set are reserved in Power Automate Desktop.

Help me create input variables in Power Automate Desktop

Add the Excel launch and refresh actions

With your input variables defined, you can now build the action sequence that opens an Excel file and refreshes all its Power Query connections.

In the Actions pane on the left, search for Launch Excel.
Double-click the Launch Excel action to add it to your workspace.
In the action dialog, set Launch Excel to and open the following document.
In the Document path field, click the variable icon {x} on the right side and select your FilePath input variable.
Click Save on the action dialog.

Next, add a wait to let the file fully load before triggering the refresh.

In the Actions pane, search for Wait.
Double-click the Wait action to add it below the Launch Excel action.
Set the Duration to 5 seconds.
Click Save.

Now send the keyboard shortcut that triggers a full data refresh in Excel.

In the Actions pane, search for Send keys.
Double-click the Send keys action to add it to your workspace.
In the Send keys to parameter, leave it set to Foreground window (this targets the Excel window you just opened).
In the Text to send field, click Insert special key and build the key combination: Ctrl+Alt+F5. This is the Refresh All shortcut in Excel.
Click Save.

Why use Ctrl+Alt+F5?

This keyboard shortcut triggers Refresh All in Excel, which refreshes every Power Query connection in the workbook. The Send keys action simulates this keystroke as if you pressed it yourself.

You are using keyboard simulation because Power Automate Desktop's built-in Excel actions do not have a dedicated "Refresh Power Query" action. The keyboard shortcut is the most reliable way to trigger a full data refresh.

Finally, add another Wait to give your Power Query connections time to finish refreshing.

Search for Wait in the Actions pane again.
Double-click to add a second Wait action after the Send keys action.
Set the Duration to 30 seconds. If your Power Query connections pull from large data sources, increase this to 60 seconds.
Click Save.

Tune your wait time

The wait duration after the refresh depends on how long your slowest Power Query connection takes. Start with 30 seconds for testing. If your test file's data does not fully refresh, increase to 60 seconds. You can always tune this later once you see real performance.

Your workspace should now show four actions in sequence: Launch Excel, Wait (5s), Send keys, Wait (30s).

Send keys action not appearing?

Search for Send keys (two words) in the Actions search bar. It lives under the Mouse and keyboard category in the Actions pane. Make sure you pick the Send keys action, not Press/release key.

Help me find and configure the Send keys action

Apply filtering and sorting, then save and test

After the data refreshes, you need to apply filtering and sorting to the worksheet before saving. You will use Power Automate Desktop's UI automation or recorded actions to manipulate the Excel Data tab.

In the Actions pane, search for Send keys and add another Send keys action after your second Wait.
Use the Insert special key button to build the key combinations that apply your filter. For example, use Ctrl+Shift+L to toggle AutoFilter, then additional keystrokes to select your filter column and value.
Alternatively, use the Recorder in the flow designer toolbar to record yourself applying the filter and sort manually in Excel. Power Automate Desktop captures the UI actions for you.

Which approach for filtering?

For simple filtering, the Recorder approach is the fastest. Open your test Excel file, click Record in the flow designer toolbar, then perform your filter and sort actions in Excel. Power Automate Desktop captures each click and keystroke as UI automation actions.

For dynamic filtering based on your input variables (where the filter column and value change per file), you will need to combine Send keys actions with your FilterColumn, FilterValue, SortColumn, and SortOrder input variables. Pass these variables into the Text to send field where needed.

Now add the Save and Close actions to finish the flow.

In the Actions pane, search for Save Excel.
Double-click the Save Excel action to add it after your filter/sort actions.
Leave the default settings (saves to the current file path) and click Save.
Search for Close Excel in the Actions pane.
Double-click Close Excel to add it as the final action.
In the Before closing Excel parameter, select Save document.
Click Save.

What does the full action sequence look like?

Your completed desktop flow should have these actions in order:

Launch Excel (open document using FilePath variable).
Wait 5 seconds (let file load).
Send keys Ctrl+Alt+F5 (refresh all Power Query connections).
Wait 30-60 seconds (let refresh complete).
Filter and sort actions (using your input variables or recorded UI actions).
Save Excel.
Close Excel with Save document option.

Time to test your desktop flow with one of your real files.

Click the Run button (or press F5) in the flow designer toolbar.
The Flow inputs dialog appears, prompting you for values for each input variable.
Enter the SharePoint path of one of your test files in the FilePath field.
Fill in FilterColumn, FilterValue, SortColumn, and SortOrder with matching values from your SharePoint configuration list.
Click OK to start the test run.

Watch the flow execute. Excel should open, the data should refresh (you will see the query progress bar), and then the file should save and close automatically.

After the flow completes, open your test file manually from SharePoint to confirm the data refreshed and your filter/sort applied correctly.
Click Save in the flow designer toolbar (top left) to save your desktop flow.

Excel opens but data does not refresh?

The most common cause is that the Wait duration after the Send keys action is too short. Increase the second Wait from 30 to 60 seconds and try again.

Also confirm that the Excel file's Power Query connections work manually. Open the file yourself, go to the Data tab, and click Refresh All. If it fails manually, the desktop flow cannot fix the underlying connection issue.

If Excel opens but the Send keys action does not reach it, ensure no other window is stealing focus. Close unnecessary applications before running the test.

Help me debug my desktop flow refresh

Your desktop flow is built and tested. Next, you will create the scheduled cloud flow that triggers this desktop flow automatically on the 1st of every month and passes in the file details from your SharePoint configuration list.

Build the Scheduled Cloud Flow

Your desktop flow handles the heavy lifting of opening and refreshing Excel files. Now you need something to trigger it automatically every month and feed it the right file information from your configuration list.

A scheduled cloud flow runs on a recurring schedule without any manual intervention. In this step, you will create one that reads your SharePoint config list, loops through each entry, and calls your desktop flow for every file.

In this step, get ready to:

Create a scheduled cloud flow with a monthly recurrence trigger.
Connect the flow to your SharePoint configuration list.
Call your desktop flow inside a loop with mapped input variables.

Create the scheduled cloud flow

You need a flow that fires on the 1st of every month without you lifting a finger. The Recurrence trigger in Power Automate handles this.

Return to make.powerautomate.com in your browser.
In the left navigation pane, select Create.
Under Start from blank, select Scheduled cloud flow.
In the Flow name field, enter Monthly Report Distribution.
In the Starting field, select the 1st of next month at your preferred time (for example, 7:00 AM).
In the Repeat every field, enter 1 and select Month from the dropdown.
Select Create.

The flow designer opens with a Recurrence trigger already in place.

What is a Recurrence trigger?

A Recurrence trigger is the clock that starts your flow. When you set the frequency to Month, the flow runs on the same date each month at the time you specified.

The start time format follows the pattern YYYY-MM-DDTHH:MM:SSZ. Power Automate fills this in for you based on the date and time you pick in the calendar.

Why schedule for early morning?

Scheduling for 7:00 AM (before most people start work) avoids file-locking conflicts. If someone has a file open in SharePoint when the desktop flow tries to refresh it, the flow may fail. Running early minimizes that risk.

Don't see Scheduled cloud flow?

Make sure you are selecting from the Start from blank section, not from templates. If you still do not see it, confirm you are in the correct environment by checking the environment switcher in the top-right corner of make.powerautomate.com.

Help me find the Scheduled cloud flow option in Power Automate

Add the SharePoint Get items action and loop

Your flow needs to know which files to process and who to send them to. The SharePoint Get items action pulls every row from your configuration list so the flow can loop through them one by one.

In the flow designer, select the (+) button below the Recurrence trigger to add a new action.
In the Add an action search field, type Get items.
Select the Get items action under the SharePoint connector.
In the Site Address field, select your SharePoint site from the dropdown.
In the List Name field, select Monthly Report Config.
Select the (+) button below the Get items action to add another action.
Search for Apply to each and select the Apply to each control action.
In the Select An Output From Previous Steps field, select the value dynamic content from the Get items action.

What does this do?

The Get items action retrieves all rows from your Monthly Report Config list. Each row contains a file path, recipient email, and filter/sort settings.

The Apply to each loop iterates over those rows. Every action you place inside this loop runs once per configuration entry. This is how the flow processes all your files without duplicating logic.

Can't find your SharePoint list?

Ensure you select the correct SharePoint site first. The List Name dropdown only populates after a site is selected. If your list does not appear, verify you named it exactly Monthly Report Config in Step 2.

My SharePoint list isn't showing in the Get items action

Connect the desktop flow inside the loop

This is where your cloud flow and desktop flow meet. You will add the action that triggers your desktop flow on your registered machine, passing in the file-specific details from each SharePoint list row.

Inside the Apply to each loop, select the (+) button to add an action.
In the search field, type Run a flow built with Power Automate for desktop.
Select the Run a flow built with Power Automate for desktop action under Actions.

First time using this action?

If this is your first time adding this action, Power Automate will prompt you to create a desktop flow connection. Select Directly to machine in the Connect field, choose your registered machine, enter the Windows credentials you use to sign in to that machine, and select Create.

Once the connection is established, set the Run Mode dropdown to Attended.
In the Desktop flow dropdown, select Refresh Excel Report.

After you select your desktop flow, the input variable fields appear. Map each one to the corresponding column from your SharePoint list item using dynamic content.

Click inside the FilePath field and select the FilePath dynamic content from the Get items action.
Click inside the FilterColumn field and select the FilterColumn dynamic content.
Click inside the FilterValue field and select the FilterValue dynamic content.
Click inside the SortColumn field and select the SortColumn dynamic content.
Click inside the SortOrder field and select the SortOrder dynamic content.
Select Save in the command bar at the top of the screen.

What does this flow look like now?

Your cloud flow has three layers. The Recurrence trigger fires on the 1st of every month. The Get items action pulls all rows from your config list. The Apply to each loop processes each row by calling your desktop flow with the correct file path and filter/sort settings.

In Attended mode, the desktop flow runs while you are signed in to your PC. Your machine must be on and unlocked at the scheduled time for the flow to execute successfully.

Keep your PC awake at trigger time

Since you chose Attended mode, your Windows PC must be powered on and signed in when the flow triggers. Adjust your power settings to prevent sleep during the scheduled window, or consider switching to unattended mode later once you confirm everything works.

Desktop flow not appearing in the dropdown?

Confirm your machine shows as Connected under Monitor > Machines at make.powerautomate.com. Also verify that your cloud flow and desktop flow are in the same Dataverse environment. You can check your current environment in the top-right corner of the portal.

Help me connect my desktop flow to my cloud flow in Power Automate

Your cloud flow is built and saved. It knows when to run, which files to process, and how to call your desktop flow for each one. Next up, you will add the final piece: emailing each refreshed file to the right person.

Add Email Delivery to the Cloud Flow

Your cloud flow can now trigger the desktop flow to refresh each Excel file automatically. The final piece is getting those freshly updated reports into the hands of the people who need them.

In this step, you will add email delivery inside the loop so that each refreshed file is sent as an attachment to the correct recipient. By the end, you will have a fully hands-off pipeline that refreshes and distributes reports every month.

In this step, get ready to:

Add a SharePoint action to retrieve refreshed file content.
Configure automated email delivery with attachments.
Test the full end-to-end pipeline.

Retrieve the refreshed file content from SharePoint

After the desktop flow saves the refreshed workbook back to SharePoint, the cloud flow needs to grab that file's binary content so it can attach it to an email.

Return to your cloud flow at make.powerautomate.com.
Inside the Apply to each loop, click New step below the desktop flow action.
Search for Get file content and select the Get file content action under the SharePoint connector.
In the Site Address field, select the same SharePoint site where your Excel files are stored.
In the File Identifier field, click inside it and select FilePath from the dynamic content panel (under the Get items action). This tells the action which file to retrieve.

What does this action do?

The Get file content action downloads the binary contents of a file from SharePoint. You are pointing it at the FilePath value from your configuration list, so each iteration of the loop grabs a different report file.

This binary content is what you will attach to the outgoing email in the next action.

Don't see FilePath in dynamic content?

Make sure you click inside the File Identifier field first. The dynamic content panel only shows values from previous actions. Look under the Get items section for the FilePath column.

If you still do not see it, confirm that your SharePoint list has a column named exactly FilePath with data in it.

Help me find my SharePoint list columns in dynamic content.

Configure the email action with attachments

Now you will set up the email that each recipient receives. The email includes a descriptive subject line with the report name and current month, plus the refreshed Excel file as an attachment.

Click New step below the Get file content action (still inside the Apply to each loop).
Search for Send an email (V2) and select it under the Office 365 Outlook connector.
In the To field, select RecipientEmail from the dynamic content panel.
In the Subject field, you will build a dynamic subject line. Click inside the field, then switch to the Expression tab in the dynamic content panel.
Type the following expression and click OK:

concat('Monthly Report - ', items('Apply_to_each')?['FileName'], ' - ', formatDateTime(utcNow(), 'MMMM yyyy'))

What does this expression do?

The concat function joins text pieces together. It combines the static prefix "Monthly Report - " with the FileName from your config list and the current month and year.

The formatDateTime(utcNow(), 'MMMM yyyy') part returns the full month name and year, such as "May 2026". This makes each email subject clear and time-stamped.

In the Body field, type a brief message such as: "Hi, please find your monthly report attached. This file was refreshed automatically on the 1st of this month."
Click Show advanced options at the bottom of the email action to reveal the Attachments section.
In Attachments Name - 1, switch to the Expression tab and enter:

concat(items('Apply_to_each')?['FileName'], '.xlsx')

In Attachments Content - 1, select File Content from the dynamic content panel (under the Get file content action). This attaches the actual Excel file.

Why use an expression for the attachment name?

The concat expression appends .xlsx to the FileName from your config list. This ensures the recipient's email client recognizes the attachment as an Excel file and displays the correct icon.

Click Save at the top of the flow editor.

Don't see the Attachments fields?

Click Show advanced options at the bottom of the Send an email (V2) action card. The Attachments Name and Content fields are hidden by default.

If you see multiple attachment rows, use only the first pair (Name - 1 and Content - 1). Each file iteration sends one attachment per email.

Help me configure email attachments in Power Automate.

Test the full pipeline end to end

With every piece in place, it is time to run the entire flow and confirm that your test files get refreshed and emailed to you.

Make sure your Windows PC is on, you are signed in, and Power Automate Desktop is running.
In the flow editor at make.powerautomate.com, click Test in the top-right corner.
Select Manually and click Test.
Watch your desktop. Power Automate Desktop will open each Excel file, refresh it, and save it. Then the cloud flow sends the emails.
Check your inbox for the test emails. You should see one email per config list entry, each with a subject like "Monthly Report - [your file name] - [current month year]" and an attached .xlsx file.

What should I see?

Each email arrives from your own Outlook account (since you used your own email in the test config entries). The subject contains the file name and current month. Opening the attachment should show the refreshed data.

In the flow run history at make.powerautomate.com, each action should show a green checkmark. Click into any action to see its inputs and outputs.

Flow run failed or no email received?

If the desktop flow action failed, confirm your PC was unlocked and Power Automate Desktop was running. Attended mode requires an active user session.

If the Get file content action failed, double-check that the FilePath values in your SharePoint config list match the actual file paths on your SharePoint site. The path is case-sensitive.

If the email action failed, check that the RecipientEmail values are valid email addresses.

Help me debug my Power Automate flow run failure.

Secret mission

Add Error Handling and Teams Summary Notification

Your automation works, but what happens when a file path is wrong or a refresh fails? In production, silent failures are dangerous. In this bonus challenge, you'll add error handling that captures per-file success or failure and posts a summary notification to Microsoft Teams with total processing time.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely within your existing Power Automate Premium license, so there are no additional ongoing costs.

Resources you used:

Cloud flow: Monthly Report Distribution (at make.powerautomate.com).
Desktop flow: Refresh Excel Report (in Power Automate Desktop).
SharePoint list: Monthly Report Config.
Machine registration (visible under Monitor > Machines in the Power Automate portal).
Power Automate Desktop app (installed on your Windows PC).

Keep everything running

No action needed. Choose this if you plan to use the automation in production.

Leave the cloud flow turned on. It will automatically trigger on the 1st of each month.
Ensure your PC is on and you are logged in at the scheduled time for attended mode.
Once your 2-3 test files work reliably, add the remaining files to the Monthly Report Config SharePoint list. Adding a new report is as simple as adding a row.
Consider switching to unattended mode if you do not want your PC to need to be actively in use at the scheduled time.
Power Automate Desktop remains installed on your machine and your machine registration stays active.

Pause - I'll come back to this later

Turn off the cloud flow so it does not run on the next 1st of the month. Your desktop flow, SharePoint list, and machine registration all remain intact.

Go to make.powerautomate.com and select My flows in the left navigation pane.
Select the Monthly Report Distribution flow, then select the vertical ellipsis (⋮).
Select Turn off.
When you are ready to resume, return to My flows, select the flow, and select Turn on on the menu at the top.

Delete - I don't want to use this again

Remove all project resources completely. Follow each item below.

Delete the cloud flow: go to make.powerautomate.com and select My flows. Open the Monthly Report Distribution flow details page. Select All runs and cancel any pending runs. Return to the details page and select Delete on the menu at the top.
Delete the desktop flow: go to make.powerautomate.com and select Desktop flows in the left navigation pane. Select the vertical ellipsis (⋮) next to the Refresh Excel Report flow, then select Delete. Select Delete again when prompted.
Delete the SharePoint list: go to your SharePoint site and open the Monthly Report Config list. Select Settings, then select List settings. Select Delete this list and confirm with OK.
Remove the machine registration: go to make.powerautomate.com and select Monitor in the left navigation, then select Machines. Select your machine from the list, then select Delete machine in the command bar.
Uninstall Power Automate Desktop: open Windows Settings, select Apps, then Apps & features. Search for Power Automate, select it, and select Uninstall.

That's a Wrap!

Nice work! You've built a production-ready monthly automation pipeline that eliminates repetitive Excel refresh-and-email cycles entirely.

You've learned how to:

Built a scheduled cloud flow that triggers on the 1st of every month and orchestrates an entire reporting pipeline from refresh to delivery.
Created a desktop flow that reliably refreshes Power Query connections in Excel, working around the cloud-only limitation that prevents Office Scripts from handling this.
Set up configuration-driven email delivery using a SharePoint list, so adding new files or recipients is as simple as adding a row.
Added error handling and a Teams summary notification for production monitoring, ensuring you know exactly which files succeeded or failed each month.

Ready to quiz yourself?