How to Export SharePoint List to Excel and Send an Email Using Power Automate?

Last week, I worked on a Power Automate scenario where I needed to export the TS MGMT Meeting SharePoint list to Excel and email it to the manager. The goal was to export all SharePoint items to Excel and then send an email with the Excel file as an attachment.

In this tutorial, I will explain how to export SharePoint List to Excel and send an email using Power Automate.

Export SharePoint List to Excel and Send an Email Using Power Automate

Before creating the flow, we need to create a SharePoint list. Here, I created a SharePoint list with the following columns and data types:

Also, create a blank Excel file in your SharePoint document library, as shown below.

Now follow the below steps:

1. In the Power Automate, click the Instant Cloud flow, enter the Flow name, and choose the trigger flow (i.e., manually trigger a flow).

2. Now choose a Get file content action for SharePoint Connector and configure the required parameters:

• Site Address: Select the SharePoint site address from the dropdown where we create the blank Excel file.
• File Identifier: Choose the Excel file we created before.

3. Then save the flow and test it. After the flow runs, copy the body of the “Get file content” action.

4. Then, edit the flow and add a Compose action. In the input field of the Compose action, paste the body of the Get file content action.

After that, delete the Get file content action and the blank Excel file from your document library.

5. Next, add a Get items action to retrieve all items present in the SharePoint list and provide the following parameters:

• Site Address: Select the SharePoint site address.
• List Name: Select the name of the SharePoint list you want to retrieve items from.

6. Now, add an action to store Excel files in a SharePoint library. Click the + icon to add the Create file action (under the SharePoint connector) and provide the required parameters:

• Site Address: Select the SharePoint site address from the dropdown menu where you store all your Excel files.
• Folder Path: Select the folder path.
• File Name: Provide the Excel file name with the .xlsx extension. Here, use the SharePoint ID column from dynamic content.
• File Content: Use the dynamic content Output from the Compose action.

7. Now, let’s create an Excel table. Click the + icon to add the Create table action (under the Excel Online (Business) connector) and provide the following parameters:

• Location: Select the SharePoint site address from the dropdown menu.
• Document Library: Select the document library where the Excel file is stored.
• File: Provide the dynamic content from the Create file action, using body/Id.
• Table Range: Specify the range based on the number of columns you have. For example, if you have seven columns, use A1:G1. Adjust this range according to your columns.
• Table Name: Enter a name for the table.
• Column Names: List all the column names you want to include in the table.

8. Now, let’s add a row to the table. Click the + icon to add the Add a row into a table action (under the Excel Online (Business) connector) and provide the required parameters:

• Location: Select the SharePoint site.
• Document Library: Choose the Documents folder.
• File: Provide the dynamic content from the Create file action, using body/Id.
• Table: Select the table name from the dynamic content.
• Row: Provide the values for each column in the table. Map the dynamic content for each column as needed.

9. Next, add a delay action and provide the count as 1 and Unit as Minute.

10. Now add a Get file content action for SharePoint Connector and configure the required parameters:

• Site Address: Select the SharePoint site address from the dropdown.
• File Identifier: Provide the dynamic content from the Create file action, using body/Id.

11. Now, add a Send an email action to send the Excel file. To do this, add the Send an email action with the following parameters:

• To: Enter the recipient’s email address (e.g., the manager’s email).
• Subject: Provide a subject for the email.
• Body: Write the body content of the email, including any message or instructions.
• Attachments: Attach the Excel file by using the dynamic content File Content from the Create file action.

Run the Flow to Export the SharePoint List to Excel and Send an Email

Now, it’s time to save the flow. Click on Test -> Select Manually -> click Test. After the flow runs successfully, go to the SharePoint library, and you will see that the file has been successfully created.

Also, you can check Outlook emails; the email comes with an Excel attachment.

In this tutorial, I explained how to export a SharePoint list to Excel and send it via email using Power Automate. We also covered how to create a table dynamically, define columns, and add rows in Excel.

Related Power Automate tutorials:

• Export Power BI Report to Excel using Power Automate
• Filter Excel Files Based On Date Using Power Automate
• Remove the Top Few Lines of an Excel File Using Power Automate
• Delete All Items in the SharePoint List Using Power Automate

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.