How to send emails and automatically fetch an attachment from a folder

[judebot]

Hello, 

Would it be possible to Power Automate sending bulk emails and have it fetch a file attachment that is unique to that specific person? I have seen guides where the path to the file is predefined but I was wondering if I could tailor it to my case where the attachments are all in one folder but the file names are labeled with the person's unique identifier(employee ID). e.g. "103.pdf". I then have an excel file that lists all the employee IDs with their respective email address. If I could automate sending them out that would be really great. Currently been playing around with Power Automate but struggling with structuring the flow.

[v-chengfen-msft · Accepted solution]

Hi @judebot ,

I made a template for your reference:

Recurrence: UTC+8 time zone, triggered at 8:00 am

Define an array variable, if a user has multiple files, they can also be stored in an array to send mail

Filter query : substringof('@{items('Apply_to_each')?['UserID']}',FileLeafRef)

Filter out files with user IDs in their filenames

Get the contents of the file, put it into an array

send file array

Here is result:

Best Regards

Cheng Feng

[judebot]

Hi Cheng. This works perfectly. I can't thank you enough. I would have never gotten this to work on my own so kudos to you. I really appreciate your help. Thank you so much!

[judebot]

Hi @v-chengfen-msft, Hope you could help me out again. So after testing, seems like other people are getting attachments that aren't theirs. So they're getting their attachments and then those of others as well. I have checked multiple times and I do think I have followed all the steps accurately. Any thoughts on what might be happening?

[v-chengfen-msft]

Hi @judebot ,

Could you show a screenshot of the flow you built?

Best Regards

Cheng Feng

[judebot]

[v-chengfen-msft]

HI @judebot ,

Judging from the screenshots you provided, there is no problem with the overall flow

 substringof:

substringof('103',FileLeafRef) will filter out all files containing '103' in the file name, if a file name is 2103.xlsx will also be filtered out.

Could you tell me if the same part will appear for the employee id or if a certain employee id is a part of another employee id?

Here is a link about filter query for your reference:

Power Automate SharePoint Get Items Filter Query + 12 Examples - EnjoySharePoint

Best Regards

Cheng Feng

[judebot]

Hi @v-chengfen-msft , 

I can confirm that employee IDs are unique non-repeating 7 digit figures. Sending also the screenshot from my excel file with the ID and email.

[VictorIvanidze · Accepted solution]

Before "apply to each 2"  set "Set variable" action for variable Test. Value should be [].

[judebot]

Hi @VictorIvanidze 

This worked like a charm. Thank you so much! You're the man!

[judebot]

Hi again @v-chengfen-msft @VictorIvanidze, this has been working perfectly for me and I can't thank you enough. I was just wondering if I could turn this into a Power App and what changes would I have to make? Turns out, a lot of people need a tool just like this and making a more app styled interface would help.  

[TomWallner]

We are trying to create a power automate flow that will pull PTSN and SMS (preview) usage from the teams admin center and use the data from two specific users and upload it to a sharepoint site afterwords. Then send an automatic weekly email to them letting them know it's done. Can anyone help?

[VictorIvanidze]

Hi @TomWallner 

please create a new thread.

[johnchow240997]

Hi @VictorIvanidze 
I have followed step by step for the solution but i keep getting not specified for the get file content when I test the flow. Do you know why?