Instantly using Power Automate create CSV file and send email attachment

Instantly using Power Automate create CSV file and send email attachment

No comments

 379 total views,  5 views today

In this “Power Automate create CSV file and send email attachment” article, we will learn using Power Automate how to create a CSV file and how to send an email with the attachments.

Business Use Cases: Using Power Automate create CSV file and send email with attachment in Power Automate

There are many use-cases for this requirement, for this demo, we have highlighted the below two:

Use Case 1: Checked Out Files report

Many times, we can see that a lot of files in the SharePoint Online document library will be in the “checked out” state. If you want those files to be checked in by the owner of the document, you can develop a manually executed or scheduler-type Power Automate flow that will query your document library and prepare the checked-out file report.

A copy of this file you can store in a report document library, and you can send one more copy of the file to the concerned person. In the demo, we will show this.

Use Case 2: Approved files report

Suppose you have a document library with thousands of documents where there is a column for document status with the value “Draft and Approved”. If you want to send all approved files to your manager as email attachments, you can develop a manually executed or scheduler-type Power Automate flow that will query your document library and prepare the approved file report.

Then, you can send all the approved files as email attachments automatically to your manager. In the demo, we will show this.

Complete Flow: Using Power Automate create CSV file and send email with attachment in Power Automate

Below is the complete Power Automate flow that creates a CSV file and sends an email with an attachment:

Using Power Automate create CSV file and send email with attachment in Power Automate

Using Power Automate create CSV file and send email with attachment in Power Automate - Part2

Using Power Automate create CSV file and send email with attachment in Power Automate-Part3
Using Power Automate create CSV file and send email with attachment in Power Automate

Flow Explanation: Using Power Automate create CSV file and send email with attachment in Power Automate

In this section, we will explain the flow step by step.

Step 1: Variable initialization

Create the following two variables:

  • varFileAttachments: Type of Array
  • varAttahmentmentFileName: A string with the following default value: @concat(‘Checked Out Files Report,’ utcNow(), ‘.csv’)
Create array variable in Power Automate
Create array variable in Power Automate

Step 2: Get checked out files from SharePoint Online document library

Add a “Get files (properties only)” action. To filter the checked-out files, add the below filter query:


CheckoutUserId Does not equal null

Get checked out files from SharePoint Online document library
Get checked out files from SharePoint Online document library

Note:

  • For your scenario, you can filter using any other criteria.

Step 3: Select data operation to map the needed columns

Add a select data operation to map the needed columns. Enter the following in the “From” field:


@{outputs('Get_files_(properties_only)')?['body/value']}

Select data operation to map the needed columns
Select data operation to map the needed columns

Step 5: Create a CSV table

Add a create CSV table data operation, and in the from field, pass the output of the previous “Select” step as below:


@{body('Select')}

Add a create CSV table data operation - to convert to CSV
Add a create CSV table data operation – to convert to CSV

Step 6: Create a CSV file in SharePoint document library

Add a Create File action and pass the site address and folder path.

In the file name, pass the variable “varAttahmentmentFileName” (select from the dynamic value tab).

In the File Content, pass the output of “Create CSV table” (select from the dynamic value tab).

Create a CSV file in SharePoint document library using Power Automate
Create a CSV file in SharePoint document library using Power Automate

The above “Create file” action will create a CSV file in the “Shared Documents” library.

Step 7: Get file content from the newly created file

Add a “Get file content” action and pass the site address and file identifier.

Select the file ID from the create file action (from the dynamic value tab).

Get file content action in Power Automate
Get file content action in Power Automate

Step 8: Add data to array variable collection for the email attachment

Add an “Append to array variable” action and pass the below parameters:

  • Name: Select the File Name variable (“varAttahmentmentFileName”).
  • ContentBytes: Body of Get file content (select the file content from the dynamic value tab).
Append to array variable in Power Automate
Append to array variable in Power Automate

Below is the syntax to pass the value to array variable for the file attachment functionality in the email.


{
"Name": "Pass the file name with extension from dynamic content or you can pass your own custom file name",
"ContentBytes": "Pass the file content from get file content (dynamic value tab)"
}

Step 9: Add send email action (V2/V3) to send the attachment in the email

Add a “Send an email (V2/V3)” action and the below parameters:

  • To: Your email address
  • Subject: A meaningful text
  • Body: A meaningful paragraph
  • Attachments: Select the attachment variable (“varFileAttachments”) from the dynamic value tab.
send email action (V2-V3) to send the attachment in the email
send email action (V2-V3) to send the attachment in the email

Note:

  • You can pass more than one attachment file.

Step 10: Demo – Checked out files report

Now, if you execute the flow, you can see that a “Checked Out Files Report CSV” has been created in the “Documents” library.

Demo - Create checked out file report CSV in Power Automate
Demo – Create checked out file report CSV in Power Automate

Note:

  • As we have executed the flow a couple of times, multiple files are created.

 

At the same time, we can see that one email has arrived in your inbox with the checked-out files report as an attachment.

Email attachment in Power Automate Demo
Email attachment in Power Automate Demo

Use Case 2 (Approved Files): Send email with attachment in Power Automate

In this section, we will learn how to get all approved files from a document library and send those files as email attachments.

Step 1: Get all approved files from SharePoint Online document library

Add a “Get files (properties only)” action and pass the site address and library name as the parameters.

In the filter query, add the following:


DocumentStatus equals approved

Get all approved files from SharePoint Online document library using Power Automate

Using Power Automate, retrieve all approved files from the SharePoint Online Document Library.

 

Note:

  • For this demo, we have filtered the document library by the document status column. However, for your scenario, you can filter with some other criteria.

Step 2: Send email attachments with files from the SharePoint document library

Add an “Apply to each” control.

Enter the following value in the Select an output from Steps column:


@{outputs('Get_files_(properties_only)_-_Get_Approved_Files')?['body/value']}

Inside the loop, perform the following:

Add a “Get file content” action where you pass the site address and file identifier. The file identifier should be selected from the “get file properties only” (approved files) action.

Add an “Append to array variable” data operation and pass the below parameters:

  • Name: Select “varFileAttachments” from the dynamic value variables section.
  • For the value, pass the following:

{
"Name": "@{items('Apply_to_each')?['{FilenameWithExtension}']}",
"ContentBytes": "@{body('Get_file_content_2')}"
}

Send email attachments with files from SharePoint document library using Power Automate

Send email attachments with files from SharePoint document library using Power Automate

 

Finally, outside of the apply to each control, add a “Send an email” action with the following parameters:

  • Your e-mail address
  • Subject: A meaningful text
  • Body: A meaningful paragraph
  • Attachments: Select the attachment variable (“varFileAttachments”) from the dynamic value tab.

Send an email configuration - Send email attachments with files from SharePoint document library using Power Automate

Send email attachments with files from the SharePoint document library using Power Automate.

Step 3 (Approved Files) Demo: Send email with attachment in Power Automate

Navigate to your document library and filter by the document type’s approved status value.

Get all approved files in SharePoint Online document library view
Get all approved files in the SharePoint Online Document Library.

Now, if you execute the flow, you can see that all the approved files from the document library have arrived in your mailbox as attachments.

Demo: Send all documents from the SharePoint document library as email attachments.
Demo: Send all documents from the SharePoint document library as email attachments.

Summary: Using Power Automate create CSV file and send email with attachment using Power Automate

Thus, in this article, we have learned the following with respect to sending email attachments using Power Automate:

  • How to create a CSV file in SharePoint Online’s document library using Power Automate
  • How to send a CSV file as an email attachment using Power Automate
  • How to send all files from your document library as email attachments using Power Automate
  • How to use Get file content in Power Automate
  • How to create a CSV file using Power Automate
  • How to loop through each files in SharePoint using apply to each control
  • How to use append to array variable in Power Automate

See Also: Power Automate Tutorials

If you like our articles, please appreciate our efforts by liking and sharing them with your friends and colleagues. Please join the mailing list to get our articles in your inbox as and when we publish them.

Happy automating and learning!

About Post Author

Do you have a better solution or question on this topic? Please leave a comment