cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simhadri
Level: Powered On

Need to trigger Single email for same set of records in Excel using Microsoft Flow

I have below records available in excel, So now I have to read Onedrive excel and then send the email notification to the employees by Group Wise only one notification per user using Microsoft Flow.

 



EmpID EmapName Start Time End Time Email ID
1 raju 22/8/2017 11:00:00 22/8/2017 22:00:00 raju_s@hotmail.com
1 raju 23/8/2017 11:00:00 23/8/2017 22:00:00 raju_s@hotmail.com
2 kumar 22/8/2017 11:00:00 22/8/2017 22:00:00 kumar@hotmail.com
2 kumar 23/8/2017 11:00:00 23/8/2017 22:00:00 kumar@hotmail.com
  

Email Notification per user as mentioned in the below format

 

Subject: [Empname Dynamic] Swipe Report

 

email Body - Please look your swipe in / Out details.

 

Start Time End Time

22/8/2017 11:00:00 22/8/2017 22:00:00

23/8/2017 11:00:00 23/8/2017 22:00:00

  

same for the second user also we need to send only one notification to the other users with their details.

 

 

Can you help how to achieve this in Microsoft Flow

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi @simhadri,

 

Please take a try with the following workaorund:

1. Add a proper trigger, for testing purpose, here I use the Flow button trigger,

2. Add the Excel Get Rows, specify the OneDrive connection, locate the file and the formatted data table,

3. Add a Select Action, configure From to Value content of Excel Get Rows, under the map entry, Enter enName, then on the right side, select the EmployName as value input,

4. Add a Compose Action, Input the following formula, which is used to distinct the EmployeeName,

 

"@union(body('Select'),body('Select'))"

5. Add Variable->Initialize Variable action, Name set to NameArray, type set to Array, Value set to the Compose Output,

 

6. Add a do until, condition set to 

 

@empty(variables('NameArray'))

 

7. Within do until, add a Filter Array, From set to Value of Excel Get Row, condition set to the following:

 

@equals(item()?['EmployeeName'], first(variables('NameArray'))?['EmName'])

8. Add another select (Select_2) after the Filter Array, From set to Body of Select Array, map set to:

 

  StartTime :  formatDateTime(item()?['StartTime'],'dd/MM/yyyy HH:mm:ss') ----Use Expression

  EndTime  : formatDateTime(item()?['EndTime'],'dd/MM/yyyy HH:mm:ss') -----Use Expression

9. Add Create HTML table action, From set to the output of the Select_2, including Header,

10. Add the Send an Email Action,

To set to Expression: first(body('Filter_array'))?['EmailId']

Subject set to Expression: first(variables('NameArray'))?['EmName']

Body set to: Please look your swipe in / Out details: Output content from Select_2,

11. Add another compose (Compose_2), inputs set to:

"@skip(variables('NameArray'),1)"

12. Add Set Variable, choose NameArray, value set to Output of Compose_2.

Image reference:

22.PNG23.PNG24.PNG25.PNG

 

Running results:

26.PNG

27.PNG

Regards,

Michael

 

 

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Community Support Team
Community Support Team

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi @simhadri,

 

Please take a try with the following workaorund:

1. Add a proper trigger, for testing purpose, here I use the Flow button trigger,

2. Add the Excel Get Rows, specify the OneDrive connection, locate the file and the formatted data table,

3. Add a Select Action, configure From to Value content of Excel Get Rows, under the map entry, Enter enName, then on the right side, select the EmployName as value input,

4. Add a Compose Action, Input the following formula, which is used to distinct the EmployeeName,

 

"@union(body('Select'),body('Select'))"

5. Add Variable->Initialize Variable action, Name set to NameArray, type set to Array, Value set to the Compose Output,

 

6. Add a do until, condition set to 

 

@empty(variables('NameArray'))

 

7. Within do until, add a Filter Array, From set to Value of Excel Get Row, condition set to the following:

 

@equals(item()?['EmployeeName'], first(variables('NameArray'))?['EmName'])

8. Add another select (Select_2) after the Filter Array, From set to Body of Select Array, map set to:

 

  StartTime :  formatDateTime(item()?['StartTime'],'dd/MM/yyyy HH:mm:ss') ----Use Expression

  EndTime  : formatDateTime(item()?['EndTime'],'dd/MM/yyyy HH:mm:ss') -----Use Expression

9. Add Create HTML table action, From set to the output of the Select_2, including Header,

10. Add the Send an Email Action,

To set to Expression: first(body('Filter_array'))?['EmailId']

Subject set to Expression: first(variables('NameArray'))?['EmName']

Body set to: Please look your swipe in / Out details: Output content from Select_2,

11. Add another compose (Compose_2), inputs set to:

"@skip(variables('NameArray'),1)"

12. Add Set Variable, choose NameArray, value set to Output of Compose_2.

Image reference:

22.PNG23.PNG24.PNG25.PNG

 

Running results:

26.PNG

27.PNG

Regards,

Michael

 

 

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

simhadri
Level: Powered On

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

I have tried below solution but seems not getting the required results, Can you share your email id so that i can give Edit access to my flow , So that you can guide me .

Community Support Team
Community Support Team

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi @simhadri,

 

I don't think my Email account would be allowed to edit the flow created in your organization.

If you have any trouble following the steps I posted earlier, please let me know, and I will try to explain it in a more clear way.

 

Regards,

Michael

 

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
angus
Level: Power Up

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi

 

Just wanted to say thanks - this saved me a load of time doing a similar task on SharePoint list items.

 

Could you recommend any documentation for Flow functions (e.g. union), as the documentation seems a bit hit and miss.

 

thanks

Angus

 

sunithav
Level: Powered On

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi Angus,

 

I'm trying to group sharepoint list items by a column and tried the steps explained for excel file. However i couldnt get it working. Would you be able to share your implementation here please so that people who are trying to do the same would be benefited?

 

Thanks,

Sunitha

Highlighted
Angusham
Level: Powered On

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi

 

Yes, no problem.

 

I basically replaced the Get Rows in the example given here with a Get Items from a SharePoint list.

 

The Select was from the value of that Get Items, and I selected the field I wanted to group by. (EmailAddress in my case).

 

I added the compose with the expression:

union(body('Select'),body('Select')) (I confess I just copied this from the example!)

 

The name array and doUntil were the same as above (although I called by field Requestedby in the name array).

 

The filter was the tricky bit - I set this as follows in the expression builder:

equals(item()?['Requested_x0020_By']['Email'], first(variables('NameArray'))?['RequestedBy']) 

 

Bear in mind that Requested_x0020_By was the field in my SharePoint list that was a person, so ['Email'] gave me their email address. In the NameArray, I'd called the field RequestedBy.

 

In the Select2, I had to use the Expression builder to specify the fieldnames to extract from the SharePoint list, as mentioned above - so for example item()?['EndTime'] to get the EndTime from the SharePoint list.

After that I pretty much followed the instructions above.

 

Please reply/let me know if there's a specific stage causing you an issue.

 

thanks

 

Angus

 

 

Perez
Level: Powered On

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi @v-micsh-msft,

I’m trying to use your suggestion here to my flow but failed to do it...

I have an excel with list of project risks, each risk in the excel is assigned to a specific owner.

I need the flow to send a mail to each owner with all the risks assigned to him.

I used the select-->compose--> initiate variable as you suggested below and it works fine as I can see when running the flow.

my issue is that when running the filter in the do until loop I can see the input is received, but output is "[]"

filter expression: @equals(item()?['Owner'], first(variables('OwnerArray'))?['ItOwner'])

where:

Owner, is the value from the excel output

OwnerArray, is the name i gave to the array in the Initialize varaible action

ItOwner is the name in the select

 

configuration screenshots:

Flow#1.pngFlow#2.png

 

 

 

results screenshots:

result#1.pngresult#2.png

 

 

will appreciate if you can assist with understanding what went wrong with my filter.

if somthing is not clear in my request i will be happy to farthere elaborate.

thanks in advance

Eyal

Perez
Level: Powered On

Re: Need to trigger Single email for same set of records in Excel using Microsoft Flow

Hi @v-micsh-msft,

I’m trying to use your suggestion here to my flow but failed to do it...

I have an excel with list of project risks, each risk in the excel is assigned to a specific owner.

I need the flow to send a mail to each owner with all the risks assigned to him.

I used the select-->compose--> initiate variable as you suggested below and it works fine as I can see when running the flow.

my issue is that when running the filter in the do until loop I can see the input is received, but output is "[]"

filter expression: @equals(item()?['Owner'], first(variables('OwnerArray'))?['ItOwner'])

where:

Owner, is the value from the excel output

OwnerArray, is the name i gave to the array in the Initialize varaible action

ItOwner is the name in the select

 

configuration screenshots:

Flow#1.pngFlow#2.png

 

 

 

results screenshots:

result#1.pngresult#2.png

 

 

will appreciate if you can assist with understanding what went wrong with my filter.

if somthing is not clear in my request i will be happy to farthere elaborate.

thanks in advance

Eyal

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,082)