Assistance Needed for Daily Email Report

[nishadhac]

Hello,

Can anyone help me? I want to extract the most recently added data from an Excel file, generate a report from it, and send it as an email daily after 4 pm to a couple of email addresses.

[trice602]

Hi @nishadhac ,

I'm sure there are a bunch of people glad to help with this!  If you can share your excel workbook by pasting in a screenshot (not attaching) and indicate if the data is in a table, how you want to filter it by specific columns, etc, etc and how you want to share it -- those details are critically important to getting someone to response and help.

So think about providing the entire scope of work and share all the details.

[trice602]

Also include where the Excel file is stored:  OneDrive, SharePoint, File System, etc 😊

[nishadhac]

Hi @trice602 ,

I would like to retrieve the most recently updated data from the IMS Marked Date column and the Excel file stored in OneDrive for Business.

[trice602]

Hi @nishadhac ,

Perfect!  Thanks for adding - very helpful.  So to clarify, you want to add any rows that contained yesterday's date as an example?  I think I can put that together pretty quickly over coffee this morning ☕☕☕☕☕

[nishadhac]

Hi @trice602,

Yes, you are correct. It will update on a daily basis as a result of filling out the Microsoft form. I just want to receive the day-by-day results, not all the previous date records, as a report sent every day after 4 pm to selected email addresses.

[trice602 · Accepted solution]

Hi @nishadhac ,

Here's a sample flow for you to build on and make it your own.  In this example, I am sending out a daily report that contains all new rows for TODAY's DATE and then emailing to some people.

The first part of the flow has a trigger, when an email arrives (the complete report), I save it to OneDrive and then list rows in the table.

Include attachments is important in the trigger.

I have a variable we will use later, just initialize here

Next, I am saving the file I receive by attachment, the dynamic content is from the trigger, attachment name and attachment content bytes.

Next I list rows present in the table of the file I just saved.  I assume the table name is named Table1 and enter that as a custom value.  The file name is the dynamic content from the trigger Attachment Name, shown below.

The next two actions select the data and put in an html table.  When you create your map in the select, even though they show up like this before after saving/running, the way to enter in the map values is in the following format as an expression:

item()?['Attendance Days']

item()?['Class']

item()?['IMS_Marked']

item()?['Notes']

The create HTML table action uses the output of the Select action, shown above.

Next, I will format the html table a little with borders and cell spacing, using a set variable action.

replace(body('Create_HTML_table'),'<table>','<table border="1" style="padding:10px">')

Last step is to send the email out.  I use a dynamic subject line for today's date:

formatDateTime(utcNow(),'MM/dd/yyyy')

Here's a sample report by email with today's entries.  I didn't spend a lot of time formatting but you have the flow to make this your own.

This is the excel table I am using for the test.

Please mark one or more of my replies as a solution if they were helpful!

[trice602 · Accepted solution]

Hi @nishadhac ,

I was posting the flow example when you replied.  So assuming your form results are already getting saved to a OneDrive excel table, you would modify accordingly:

1)  Change the trigger to a recurrence to run once a day a 4 PM.

2)  Since the file is already created, you can skip creating the file and just list rows present in the table. 

Again, please consider marking one of more of these replies as a solution.  I enjoyed working on this post.

[trice602 · Accepted solution]

Hi @nishadhac ,

Here's the details on the Select values, again I know this part can be confusing so clarifying.  These are entered as expressions but again, after saving and running, they will look like this but you enter them in as an expression.

[nishadhac]

Hi @trice602 ,

Could you please share the created floor with me.