Send Emails with Attachments based on excel sheet and directory of attachments both on onedrive

[Rev_Ed]

I have to send emails to a number of different people with a specific attachment for that person. I created an Excel spreadsheet with the data for the email and uploaded it to Onedrive for Business. I also created a folder on Onedrive for Business with each of the attachments in PDF format. I am trying to automate this Power Automate. I have tried many different flows but always run into issues. Can anyone help. 

Error: 

2308	Newville	First United	111 Big Spring Ave.	ehb47@comcast.net	Statements1.pdf
7314	Camp Hill	Camp Hill	101 N. 23rd St.	ehb47@comcast.net	Statements2.pdf
7315	Carlisle	First	2A N. Hanover St.	ehb47@comcast.net	Statements3.pdf
7316	Carlisle	Second	528 Garland Drive	ehb47@comcast.net	Statements4.pdf
7317	Carlisle	Dickinson	12 Church Rd.	ehb47@comcast.net	Statements5.pdf
7318	Chambersburg	Central	40 Lincoln Way West	ehb47@comcast.net	Statements6.pdf
7319	Chambersburg	Falling Spring	221 N. Main St.	ehb47@comcast.net	Statements7.pdf
7321	Dillsburg	Monaghan	1185 Gettysburg Pike	ehb47@comcast.net	Statements8.pdf
7323	Duncannon	Duncannon	3 N. High St.	ehb47@comcast.net	Statements9.pdf
7325	Gettysburg	Gettysburg	208 Baltimore St.	ehb47@comcast.net	Statements10.pdf
7326	Gettysburg	Great Conewago	174 Red Bridge Rd.	ehb47@comcast.net	Statements11.pdf
7327	Gettysburg	Lower Marsh Creek	1865 Knoxlyn Rd.	ehb47@comcast.net	Statements12.pdf
7328	Greencastle	Greencastle	57 W. Baltimore St.	ehb47@comcast.net	Statements13.pdf
7330	Harrisburg	Capital	1401 Cumberland St.	ehb47@comcast.net	Statements14.pdf
7333	Harrisburg	Market Square	20 S. Second St.	ehb47@comcast.net	Statements15.pdf
7338	Hershey	Derry	248 E. Derry Rd.	ehb47@comcast.net	Statements16.pdf
7339	Lebanon	Christ	1300 Maple St.	ehb47@comcast.net	Statements17.pdf
7340	Lebanon	St. Andrews	600 S. 12th St.	ehb47@comcast.net	Statements18.pdf
7341	Loysville	Centre	3912 Freeman Hollow Rd.	ehb47@comcast.net	Statements19.pdf
7342	McConnellsburg	McConnellsburg	116 S. Second St.	ehb47@comcast.net	Statements20.pdf
7343	Mechanicsburg	Mechanicsburg	300 E. Simpson St.	ehb47@comcast.net	Statements21.pdf
7344	Mechanicsburg	Silver Spring	444 Silver Spring Rd.	ehb47@comcast.net	Statements22.pdf
7345	Mercersburg	Upper West Conecocheague	34 W. Seminary St.	ehb47@comcast.net	Statements23.pdf
7347	Middletown	Presbyterian Congregation of Middletown	Union and Water Sts.	ehb47@comcast.net	Statements24.pdf
7351	Newville	Big Spring	25 S. Corporation St.	ehb47@comcast.net	Statements25.pdf
7355	Shippensburg	Middle Spring	125 Middle Spring Rd.	ehb47@comcast.net	Statements26.pdf
7357	Waynesboro	Waynesboro	105 E. Main St.	ehb47@comcast.net	Statements27.pdf
7359	Millerstown	Perry Valley	301 Old Ferry Rd.	ehb47@comcast.net	Statements28.pdf
7362	Camp Hill	Christ	421 Deerfield Rd.	ehb47@comcast.net	Statements29.pdf
7567	Mifflin	Lower Tuscarora	54 Church Rd.	ehb47@comcast.net	Statements30.pdf
7593	East Waterford	East Waterford	POB 174	ehb47@comcast.net	Statements31.pdf
7609	McAlisterville	Lost Creek	Box 91	ehb47@comcast.net	Statements32.pdf
7611	Mifflintown	Westminister	208 Lemon St.	ehb47@comcast.net	Statements33.pdf
7635	Wells Tannery	Wells Valley	POB 27	ehb47@comcast.net	Statements34.pdf
8997	Mifflin	McCoysville	2562 McCoysville Rd.	ehb47@comcast.net	Statements35.pdf
9168	Dauphin	Hope	931 Red Hill Rd.	ehb47@comcast.net	Statements36.pdf
10164	Harrisburg	Pine Street	310 N. Third St.	ehb47@comcast.net	Statements37.pdf
10359	Harrisburg	Paxton	3500 Sharon St.	ehb47@comcast.net	Statements38.pdf
11019	Camp Hill	Harrisburg Korean	1127 Slate Hill Rd.	ehb47@comcast.net	Statements39.pdf
11159	Mechanicsburg	St. James	1425 Orr's Bridge Rd.	ehb47@comcast.net	Statements40.pdf
11436	Newport	Highland	11 Church Rd.	ehb47@comcast.net	Statements41.pdf
15128	Harrisburg	Faith Immanuel	1801 Colonial Rd.	ehb47@comcast.net	Statements42.pdf
23167	Hancock, MD	Mt. Olivet	POB 327	ehb47@comcast.net	Statements43.pdf
23233	Warfordsburg	Warfordsburg	12941 Buck Valley Rd.	ehb47@comcast.net	Statements44.pdf

[Rev_Ed]

Sorry All, I now see I forgot to include the "File Content" variable in Send Email V2. But once added I get this error...



ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@{outputs('List_rows_present_in_a_table')?['body/value']}@{outputs('Get_file_content')?['body']}' is of type 'String'. The result must be a valid array.

[mgrachii]

Hi There! Can you show a screenshot of the final flow is looking now after the final change you did to include the "File Content" variable in Send Email V2?

The new error seems to suggest that you may have something else in the "apply to each" action apart from the "value" input.

[Rev_Ed]

Here is the error and the flow

[mgrachii]

Thanks!

Try removing the "file content" variable from the "apply to each" card. You should have "value" green card only

Let me know if that worked!

[Rev_Ed]

Now I get a null content error

[mgrachii]

Do you still have "File content" at the bottom of the card?

#1 should be gone

#2 should stay

[Rev_Ed]

Yes...

[mgrachii]

Can you try removing #2 and adding it back by selecting from the "dynamic content" area?

If that doesn't work , can you hover over "file content x" to see the formula that is generating behind the scenes?

[Rev_Ed]

Remove #2 and added it back...still did not work. Here is the formula generated

[mgrachii]

Very strange.

When you excecute the flow, what do you see in the output of the "Get file content" card? do you see a link to open the output? when you click in "download" should open and show you a lot of text with a "body" attribute in the 4rth line. can you check if you see this?

[mgrachii]

Something that I would do is removing the cards and adding them back

[Rev_Ed]

I believe I should be using "Get File Metadata by Folder" and "Get File Content by Folder". But If I try to use those, it shows me the folder on onedrive "Statements" but when I click on it, nothing happens...it won't let me select it. 

As it is now I am only getting metadata and content for a specific file and not every one in the folder which corresponds to the attachments in the Excel spreadsheet.

[mgrachii]

did you try checking the "outputs" from the Get file content card? (see screenshot above) 

What do you get when you see the ouputs ?

[Rev_Ed]

Here is the output

{"statusCode":200,"headers":{"Pragma":"no-cache","x-ms-request-id":"2d1f7813-3cd6-3926-b21b-de5fcdbfd529","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Cache-Control":"no-store, no-cache","Set-Cookie":"ARRAffinity=fa9050a86380ecce883d8ff2e805dd22d58cc29332b6761a0fc08c7d82b3fa74;Path=/;HttpOnly;Secure;Domain=office365-wus.azconn-wus-01.p.azurewebsites.net,ARRAffinitySameSite=fa9050a86380ecce883d8ff2e805dd22d58cc29332b6761a0fc08c7d82b3fa74;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-wus.azconn-wus-01.p.azurewebsites.net","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Date":"Sun, 11 Jul 2021 23:40:51 GMT","Content-Length":"0","Expires":"-1"}}

[mgrachii]

I see. Seems like the OneDrive is not returning the file content. as you can see in the image previously sent and also below, when I replicate what you are doing I get a "body" attribute in the response. 

the response you shared doesn't include that. Can you make sure that the file and folder both exist , you can also try to remove the card and add it back to the flow, then add it again in the "send email" card.

can you show what you have in the "get file content" card? is it still the same text as you have in the initial post ?

{"statusCode":200,"headers":{"Pragma":"no-cache","x-ms-request-id":"71fe9f02-c92a-4568-a289-e9ebb4cff0ec","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Accept-Ranges":"bytes","Cache-Control":"no-cache","Date":"Sun, 11 Jul 2021 23:24:45 GMT","ETag":"\"{2EE55A98-2CCC-4476-8075-5521F6381CDC},3\"","Location":"https://unitedstates-002.azure-apim.net/apim/onedriveforbusiness/shared-onedriveforbu-3759cb5a-6ee4-4be9-aefd-c3f5-6b6b22e8/datasets/default/files/b%2521cMm6r297B0mxTVN63MXKFXGxXkTOcOBIu-RSOFYp9KmtW-Nf5WFdQZ3usnYP51K_.01O55YGGUYLLSS5TBMOZCIA5KVEH3DQHG4/content?inferContentType=true","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Length":"1286581","Content-Disposition":"attachment; filename=\"merged-docs.pdf\"","Content-Type":"application/pdf","Expires":"-1"},"body":{"$content-type":"application/pdf","$content":"JVBERi0xLjQKJdP0zOEKMSAwIG9iago8PAovQ3JlYXRpb25EYXRlKEQ6MjAyMTA1MzEwMDQwMDkrMDAnMDAnKQovQ3JlYXRvcihQREZzaGFycCAxLjUwLjUxNDcgXCh3d3cucGRmc2hhcnAuY29tXCkpCi9Qcm9kdWNlcihQREZzaGFycCAxLjUwLjUxNDcgXCh3d3cucGRmc2hhcnAuY29tXCkpCj4+CmVuZG9ia

[Rev_Ed]

Ok...I deleted Apply to each and Send Email. I then checked to make sure folders and files on OneDrive were there and were connected. They were. I ran the flow and here is the input and output.

Input...This is very large for content, so I only copied a bit of it, but it shows the input is getting everything we need.

{"host":{"apiId":"subscriptions/9f3a0e5f-8058-487f-9d45-d3ff51164c54/providers/Microsoft.Web/locations/westus/runtimes/unitedstates-002/apis/office365","connectionReferenceName":"shared_office365","operationId":"SendEmailV2"},"parameters":{"emailMessage/To":"ehb47@comcast.net","emailMessage/Subject":"2nd Qtr Statement","emailMessage/Body":"<p>Dear Partners in Ministry,<br>\n<br>\nTest...Test</p>","emailMessage/From":"Treasurer@carlislepby.org","emailMessage/Attachments":[{"Name":"Statements1.pdf","ContentBytes":{"$content-type":"application/pdf","$content":"JVBERi0xLjMNJeLjz9MNCjEgMCB

Output  however is the same as before...

{"statusCode":200,"headers":{"Pragma":"no-cache","x-ms-request-id":"50e0a813-ed77-06f2-9270-234037f96c1d","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Cache-Control":"no-store, no-cache","Set-Cookie":"ARRAffinity=e198afec6581a1603d516688b9e05d55e216a7a6a28fad970baf7d024a095305;Path=/;HttpOnly;Secure;Domain=office365-wus.azconn-wus-01.p.azurewebsites.net,ARRAffinitySameSite=e198afec6581a1603d516688b9e05d55e216a7a6a28fad970baf7d024a095305;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-wus.azconn-wus-01.p.azurewebsites.net","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","Date":"Mon, 12 Jul 2021 00:10:26 GMT","Content-Length":"0","Expires":"-1"}}

[mgrachii]

did you get the error again?

[Rev_Ed]

Yes...

Duration

00:00:36

Error

Action 'Send_an_email_(V2)' failed

Error Details

Parameter 'To' cannot be null or empty.
clientRequestId: bd555407-eea5-4334-ad12-c727e7a04567

[mgrachii]

That is an error a bit different.

Now the issue is that the "To" field is empty, do you have it populated?

Per the output you sent previously now it seems that the email now contains attachments, if you can share a screenshot to show how the "TO" field looks now