Send emails to people in weekly reports

[Anonymous]

Hello, i'm a novice in pa/flow and would love some help in building this flow if possible?

I need the flow to save the excel file I receive every week from our HR system (sent to outlook) to a folder within Sharepoint, then it will email the people within that file.

As it's a new file every week, would it be able to do that, or does the data all have to be in one file?

Thanks a lot for any help and can clarify if anyone needs. I have access to office 365.

[annajhaveri]

@Anonymous , you could you When new email arrives trigger for the inbox in which the excel file arrives, then loop through attachments, and add Create file action to save attachment to SharePoint, please see the screenshot below. Then add List rows present in a table action to get details of people from the file.  

[Anonymous]

Thanks @annajhaveri  So with this solution, I'd be able to firstly save and then send the people within the excel file my premade email? The screenshot cuts off for me so I'm not sure if that's all the stages.

[Anonymous]

Hi, I just realised I actually have a completely different issue. I want the flow to be able to recognise a new file being added to the sharepoint folder, and then read the excel file, but since the file name will change each time (the date changes in the name), how could I make it so it just picks the newest file added without needing to know the name?

Thanks for any help and advice.

[annajhaveri · Accepted solution]

@Anonymous , please develop your flow as follows. Few things to be take care of

1. Save your excel file to Documents library of your site if you follow exact instructions below

2. I have the excel file as follows for the sample detailed here, you need to define your data in excel as table

3. This example assumes that you have only one file attachment to the email, if you have more then few changes will need to be done 

Steps:

1. Add "When a new email arrives (V3) 2" trigger to your flow and configure it as follows:

2. Then add Export Email action and configure it as follows

3. Add Initialize Variable action and configure it as follows:

4. Add Apply to each action, to loop through attachments,  in this action, add Get Attachment, Create file and Set variable action and configure it as shown below. This step is to loop through attachments of email and save the attachment to SharePoint and store the filename of attachment in variable.

Now we need to get data from excel which we saved to SharePoint, this is a bit tricky as your file is dynamic, so below steps for that

5. Add Send an HTTP request to SharePoint action and configure it as follows

6. Add compose action and use expression in the action as follows

body('Send_an_HTTP_request_to_SharePoint')['Id']

7.  Add Get Tables action and configure it as follows, select SharePoint site, select document library and specify Compose action output for file parameter

8. Add "List rows present in a table" action, select SharePoint site, document library, file value as output of Compose action and table value as below expression

first(body('Get_tables')?['value'])?['Id']

9. Now add apply to each action to loop through rows retrieved from excel, add send email action in the apply to each action to send emails individually, and if you want to send one email to all email addresses, then concat email addresses by adding compose action to apply to each action and add send email action after the apply to each action. To get the value of excel cell, use following expression in the apply to each action, here 'Email' is column name in my excel, so you need to replace with the column name of your excel from which you want the value.

items('Apply_to_each_2')['Email']

[Anonymous]

Thanks a lot, my power automate is not responding properly at the moment but it looks exactly like the elements i have been googling and I didn't know what to do after the dynamic aspect of it. thanks!