web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Automate Create Excel files for...

Power Automate

Answered

Create Excel files for different Teachers and send them an e-mail showing their Students

(0) Share

Report

Report

Posted on by KC-25021932-0

2

Hi. I have an Excel spreadsheet listing teacher´s names, teacher´s e-mails, student´s names, courses, beginning dates and end dates.

What I want is for Automate to read the Excel file and create an e-mail for each teacher with an attached spreadhsheet listing all of the columns in the original spreadsheet, but just with the courses that teacher gives. The message is: Hey, teacher xxxx, attached are your students and courses next month.

Can somebody help? For now, if you can fill your own e-mail in the "Teacher e-mail column" for test purposes...

Thanks,

Student and Teach...

Categories:

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (1)

Sort by

Verified answer

DJ_Jamba 2,837 Moderator on at

Like (0)

Report
Copy link

Link copied!
Make sure you format the data in the Excel as a Table.

Make sure the excel file is in your one drive or a Sharepoint library

Flow steps:

1. Location field is the One drive or Sharepoint location of your excel file

2. Select action

Using the following as the input:

@{outputs('List_rows_present_in_a_table')?['body/value']}
switch mapping to text mode and map fields as follows:

{ "Course": @{item()?['Course']}, "Beginning Date": @{item()?['Beginning Date']}, "End Date": @{item()?['End Date']}, "Teacher": @{item()?['Teacher']}, "Teacher E-mail": @{item()?['Teacher E-mail']}, "Student": @{item()?['Student']} }

3. Another select action.

Same input as above.
Switch mapping to text mode and enter this:
concat(item()?['Teacher E-mail'],'|',item()?['Teacher'])

4. Use compose to build a distinct list of techers

With this as input:
union(body('Teachers'), body('Teachers'))

5. Apply to each using the compose above as the input

Inside the apply to each:
a) Filter array:

The From field is the first select you created in Step 2

Switch filter to advanced mode and enter this:
@equals(item()?['Teacher E-mail'], first(split(items('Loop_over_teachers'), '|')))

b) Create CSV table

The From field is the body property from the filter array above

c) Send an email

You can copy all the field codes below straight into the associated field

To field:
@{first(split(items('Loop_over_teachers'),'|'))}

Body field:
Hey @{last(split(items('Loop_over_teachers'),'|'))}, Attached are your students and courses next month. (Email would go to: @{first(split(items('Loop_over_teachers'),'|'))})

Attachments Name:

@{last(split(items('Loop_over_teachers'),'|'))}.csv

Attachments Content:

@body('Create_CSV_table')

Test it...

Was this reply helpful? Yes No
Building with Why 351 Moderator on at

Like (0)

Report
Copy link

Link copied!

I have a couple of videos which work this concept out.

https://www.youtube.com/@BuildingwithWhy

DJ Jamba looks correct, but if you want to see a demo of those steps check out my videos.

https://youtu.be/ZJdCZYd_uco I am working with Excel sending emails to parents but the principles can be applied to going to the teachers.

Was this reply helpful? Yes No

Join the conversation