How to automate Export to Excel for a SharePoint list using flow and send an email with attachment

[Anonymous]

Hi,

I am looking to create a flow which can run every monday using recurrence and then export a list to excel, once exported, send an email with this excel file as an attachment, is this possible to automate?

Thanks in advance.

[Brad_Groux]

Yes, what you are seeking to do is possible, and the Flow would look something like this:

In the Recurrence you could set it to run weekly. In the Get items you call the SharePoint List, and the Create table points to an existing Excel file. You'd then use the Send an email (V2) to send the email with the spreadsheet attached.

If you'd like the Excel file to be empty, you can add a Get tables step, and a Delete tables step, to first clear the spreadsheet before adding the new table. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-yamao-msft]

Hi @Anonymous ,

I am afraid that it might be not possible to export a SharePoint list to Excel using Flow.

I have figured a workaround for your reference.

I the following scenario, I am going to generate an HTML table, then create a word document in OneDrive for Business, then send an email with the word document as an attachment.

The function used in the Compose action is to generate a border around the items:

replace(body('Create_HTML_table'),'<table>','<table border="1">')

Best regards,

Mabel

[Anonymous]

Thanks Mabel @v-yamao-msft , I will try this out.

Is it possible to use Create a file in SharePoint instead rather than OneDrive?

[Anonymous]

@v-yamao-msft  Hi, I am only able to make this work for .doc files. Looks like it does not work with the .xls or .xlsx extension at all.

[v-yamao-msft · Accepted solution]

Hi @Anonymous ，

As you mentioned, .xls or .xlsx are not available.

It is possible to Create file in SharePoint library instead of OneDrive.

Besides, you could also try with action Create csv table. Then create a csv file in the SharePoint library likes below.

Best regards,

Mabel

[vsolanon]

Hi @Anonymous ，

I was wondering if you were able to find a solution for this. I am currently looking for the same approach.

Thank you in advance!

Veronica

[Anonymous]

@vsolanon please review the accepted answer, currently CSV is supported on the flow side. Give it a try and let me know in case of issues.

[Anonymous]

Hi! Thanks for this answer. It was very useful, but I Have a question about it: When I Open the .CSV file in Excel it brings a lot of data delimited with ",". When I tabulate the columns all the data are disorganized. It seems to came with the data field some sort of URL for each data input. Am I doing something wrong? Is the a way to edit the format?

I Can´t add a photo with this because the file contains an URL.

[Anonymous]

Maybe the information in this question and answer chain is outdated, but I used the instructions at https://flow.microsoft.com/en-us/blog/automate-sending-weekly-reports/ to get data from a list, created a spreadsheet file, save spreadsheet to a SharePoint library, send an email to the designated email addresses with the spreadsheet attached to it, and to do all of this on a regularly reoccurring schedule.  The link provides instructions on how to create the spreadsheet file in OneDrive, but I was able to easily adapt it to SharePoint by switching out all of the OneDrive locations to SharePoint locations.

[Anonymous]

Hello.  This works except that when people try to open the spreadsheet from the email, there is an error message:

Message Rec'd When Opening Created Spreadsheet 

If I click "Yes" the spreadsheet does open.  Also, all the spreadsheets created through this flow will not open within SharePoint, I have to download them to the Excel App to view them.

Is there something more I can do to clean up the results of this flow?  Thanks.

[luuminhvuong93]

Current, ignore the error when opening the XLS.

Still better than export to CSV

[Anonymous]

@luuminhvuong93       Agreed!  Just making sure I was not missing anything.

[Sharon-S]

Can you expand each step so that we can see what you did?  Thank you

[Chris17]

You need to import the CSV data into a new Excel workbook in order to view the content correctly; see Import or export text (.txt or .csv) files - Microsoft Support