Building a flow that will send a recap email by end of the day

[FlowInside178]

Hi all,

I'm building a flow that in each end of day (around 17pm local time of mine: we live in SEA with GMT + 7 time zone) will send a concise email with summary information from the Sharepoint like below:

1/How many products that was introduced to customer (this column is multiple choice from sharepoint)

2/How many purchased/not purchased (this column is Yes/No from sharepoint)

3/How many attachments that was made.

With 1-2-3 point above, we want to build a flow that will send an email that will recap these information. Here is the pic from my sharepoint. Thank you in advance, we are looking forward to receive your support.

[v-yetonggu-msft]

Hi @FlowInside178 ，

Before creating my flow, I have a few questions to ask you:
1) Is the item information in the review email sent today the item created today?
2) Is the review email sent once a day during the working day? Or seven times a week?

Best Regards,

Sunshine Gu

[FlowInside178]

hi @v-yetonggu-msft , thank you for your reply. here is my answer from your query

1) Is the item information in the review email sent today the item created today? --> yes correct, at 17pm or 18pm each day (exclude saturday & sunday), it's similar like : after a working day you will send a recap report to your manager that what you have did today.
2) Is the review email sent once a day during the working day? Or seven times a week? -> Send once a day (exclude Saturday & Sunday) at 17pm-18pm.

Let me know if you have any concerns.

[Nived_Nambiar]

Hi @FlowInside178 

So you need a total count for each columns specified right ?

like for eg-

1. 1/How many products that was introduced to customer (this column is multiple choice from sharepoint) - you need count for each products in summary email ?

2. How many purchased/not purchased (this column is Yes/No from sharepoint) - count of yes value ?

3. How many attachments that was made. - could you share more information about this column ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

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

[v-yetonggu-msft]

Hi @FlowInside178 ,

Please check this test:

My Outlook Inbox:

My SharePoint List:

My Flow:

Created gt '@{utcNow('yyyy-MM-dd')}T00:00:00Z' and Created lt '@{utcNow('yyyy-MM-dd')}T23:59:00Z'

{
"Tilte":@{items('Apply_to_each')?['Title']},
"Product":@{join(xpath(xml(json(concat('{"body":{"value":', items('Apply_to_each')?['Product'] , '}}'))), '/body/value/Value/text()'),',')},
"Purchased":@{items('Apply_to_each')?['Purchased_x003f_']},
"AttachmentLength":@{length(outputs('Get_attachments')?['body'])}
}

<style>
Table {
  font-family: Arial, Helvetica, sans-serif;
  background-color: #EEEEEE;
  border-collapse: collapse;
  width: 100%;
}

Table td, Table th {
  border: 1px solid #ddd;
  padding: 3px 3px;
}

Table th {
  font-size: 15px;
  font-weight: bold;
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>

Best Regards,

Sunshine Gu

[FlowInside178]

hi @v-yetonggu-msft , thank you for your great help. It looks promising, I have followed your flow and success from my end. I have one concern that the daily email will be sent with the item created within that date, am I correct? For example today email will be sent with item created on 17 April and tomorrow it will send only item which will be on 18 April not including previous day right?

- How can we change the date on the sending email to our local format? Since the format date is very difficult for us to see, we want the format like "DD/MMM/yyyy HH:mm:ss"

[v-yetonggu-msft]

Hi @FlowInside178 ,

1)Yes, the flow will only send the information of the item created today.

Information for items created tomorrow will not be sent.

2)You can use the formatDateTime function in Append to array variable to format the date as 'DD/MMM/yyyy HH:mm:ss'.

formatDateTime(items('Apply_to_each'),'DD/MMM/yyyy HH:mm:ss')

Best Regards,

Sunshine Gu

[v-yetonggu-msft]

Hi @FlowInside178 ,

I forgot to remind you that the time stored in the SharePoint list is UTC standard time, which is going to be converted to local time.

I did another test:

Created gt '@{addHours(utcNow('yyyy-MM-dd'),-7)}' 

formatDateTime(addHours(items('Apply_to_each')?['Created'],7),'dd/MMM/yyyy HH:mm:ss')

My Outlook Inbox:

Best Regards,

Sunshine Gu

[FlowInside178]

Hi @v-yetonggu-msft , you made our day. Thank you so much for your support, we have applied your flow and success.

There is only one concern from us that if the flow can do or not, can we add the summary text below the table in the email like this?

Thank you in advance!

[v-yetonggu-msft]

Hi @FlowInside178 ,

You can add a summary at the bottom of the HTML table.

Like this:

Best Regards,

Sunshine Gu

----------------------------------------------------------------------------------------------------------------------------------------If I solve your problem, please mark my response as a solution so I can help more people who are experiencing this challenge in the future.

[FlowInside178]

Hi @v-yetonggu-msft , thank you for your reply.  I have tried to add the function to count the number of order and was successful.

But I don't know how to count the boolean value of 'Purchased?' column so could you guide me (the red marker)? This column is type of boolean Yes/No so I can't use the length function.

Thank you in advance ^^!

[v-yetonggu-msft]

Hi @FlowInside178 ,

Please add a filter array, and in the CSS Code format of the 'send an email' control, nest '<b></b>' outside the length function to bolden the number.

I did a test for your reference,

My Flow:

item()?['Purchased']
true
length(body('Filter_array'))

<p>@{outputs('Compose_CSS__Table_Style')}@{body('Create_HTML_table')}<br>
<b>@{length(body('Filter_array'))}</b> order has been made</p>

The Result:

Best Regards,

Sunshine Gu

[FlowInside178]

Hi @v-yetonggu-msft , thank you so much, this will be another option for us. Because what we want is the summary text below the table in email which will count how many order was made in that day (including success/unsuccess) and how many success/unsuccess purchase has been made.

Like below - following by your image

4 order has been made

3 Successful Purchase/ 1 Unsuccessful Purchase

[v-yetonggu-msft · Accepted solution]

Hi @FlowInside178 ,

The Result:

My Send an email parameters:

<p>@{outputs('Compose_CSS__Table_Style')}@{body('Create_HTML_table')}<br>
<b>@{length(variables('Information'))}</b> order has been made</p>
<b>@{length(body('Filter_array'))}</b> Successful Purchase/<b>@{sub(length(variables('Information')),length(body('Filter_array')))}</b> Unsuccessful Purchase</p>

 Best Regards,

Sunshine Gu

[FlowInside178]

Hi @v-yetonggu-msft , you made our day. Thank you so much, I have applied your solution and my manager is very happy so we can launch the flow, just one last thing that how can we apply the CSS table for this summary text in email?

From this:

4 order has been made

3 Successful Purchase/ 1 Unsuccessful Purchase

Into this:

Total order	4
Successful/Unsuccessful purchase	3/1

[v-yetonggu-msft]

Hi @FlowInside178 ,

I have helped you solve the first few questions you asked.

Could you please mark my reply as a solution?

For new questions, please create a new post and I will help you solve the problem in more detail.

Best Regards,

Sunshine Gu

[FlowInside178]

thank you @v-yetonggu-msft , we will create a new post and hope we can receive your help. Cheers!