Pick excel files one by one and email them dynamically

[RenjithPL]

Hello All,

I have multiple excel files stored in SharePoint Folder, with one of the column values with Email Address.

Eg,

Email	SALES MANAGER	Agent	L2	L3	SalesAmount
abc@gmail.com	Stefano	Luca	0000004799	0000004842	0
abc@gmail.com	Stefano	Luca	0000004799	0000004842	0
abc@gmail.com	Stefano	Luca	0000004799	0000004842	0
abc@gmail.com	Stefano	Luca	0000004799	0000004842	0
abc@gmail.com	Stefano	Luca	0000004799	0000004842	675

I am looking to create a flow which can perform below actions.

1. Pick these files one by one.

2. Get the values of Email column.

3. Distribute the files to that Email.

4. This should happen in a cycle. 

Note: I can maintain the file names as you suggest, eg, may with date or a number, if that makes the job easier. 

Regards

Ren

[Nived_Nambiar]

Hi @RenjithPL 

One query, does each excel file Email column would have only value always which would be person to which we need to send email ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[RenjithPL]

Yes, when one query runs, it should pick all the files a folder one by one and sends email to the user, the email will be available in one of the column in the excel file. 

[Nived_Nambiar · Accepted solution]

Hi @RenjithPL 

Sharing a sample design how the flow should be  designed.

See below

 Assume the file to be send is stored in location in sharepoint and each file is having Email column. 

So see how the flow is designed 

1. List all files in folder which is located in sharepoint

2. Loop through each of the file using body dynamic content which is output of list folder action

Inside the loop try the below steps

 2.1 : Use get table to get list of all tables in the excel file like below

2.2: to get email id use list rows in table action to list only first row from table in excel file. (assuming only one table per excel file)

here note that I have specified Top parameter as 1 to capture 1st row and Select Query as Email so to capture Email column values.

2.3: Get the file content of current file which is iterated

2.4: using all details use send email to send email to required person specified in excel file by using file content as attachment like below

Expression: first(outputs('List_rows_present_in_a_table')?['body/value'])?['Email']

For attaching attachments use below approach

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[RenjithPL]

HI Nived, 

Really appreciate your effort. 

I am getting an error like this

The execution of template action 'Apply_to_each' failed:
the result of the evaluation of 'foreach' expression '@body('List_files_in_folder')' is of type 'Object'.
The result must be a valid array.

Regards

Renji 

[Nived_Nambiar]

Hi @RenjithPL 

just wondering why it is not working , just confirm whether the flow is designed like below

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[RenjithPL]

Hi Nived,

"List Present in a Table"

I gave the code like this first(outputs('Get_Tables')?['body/value']) 

Is the code correct ?

Also, Check this 

[Nived_Nambiar]

Hi @RenjithPL 

You have to use table id there, the expression you have used there represent first table object from the list of tables, from that you have to use ID attribute in the table field. 

first(outputs('Get_tables')?['body/value'])?['id']

The above should be the expression.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[RenjithPL]

Hi Nived, 

I am getting an error 

The 'inputs.parameters' of workflow operation 'List_rows_present_in_a_table' of type 'OpenApiConnection' is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: 'table'

[RenjithPL]

Hi Nived, 

I kind of assume why its not working, 

because the excel files available in the sharepoint are uploaded files have a structure like below, 

Is there any workaround to get these files ?