Automate sending files in the folder to a certain email at the end of month

[BenWade]

Hi everyone,

I need your help to automate a repetitive task. During the month, I collect meal receipts (just by taking photos) and then email them to the accounting department for reimbursement. So basically, I have a folder in SharePoint. In this folder, I have one Excel file that I fill throughout the month, and I have photos of my meal receipts. Employees have to email photos and the filled xlsx file to a certain email address(e.g.,accounting@mailcom) on the last working day of the month at 17:00 CET. For instance, I sent them today (31.10.2023), and in November, I will send them on 30.11.2023.

Can someone help me with this workflow? Files in a certain folder should be sent automatically to a certain email address on the last working day of month at 17:00.

It would be nice if, after the files have been sent, photos were automatically removed(sent to the recycle bin), but not the xlsx file, as I am filling the same Excel file month after month.

I found these two flows. They basically do what I want but since I am a newbie I don’t know how to combine these two flows.

this one helps to identify the last working day
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Send-an-e-mail-notification-the-last-wor...

and this one to send all files: https://learn.microsoft.com/en-us/microsoft-365/community/power-automate-send-sharepoint-files-as-at...

The subject of an email can be: 'Meal receipts' and body:

Dear team,

I have attached meal receipts. 

Thank you

Thank you in advance

@SudeepGhatakNZ 

[BenWade]

I forgot to mention. The subject of an email can be: 'Meal receipts' and body:

Dear team,

I have attached meal receipts. 

Thank you

[BenWade]

cannot edit neither post nor comment...
on the last working day of the month at 17:00 CET

[BenWade]

.

[BenWade]

@Rhiassuring 

@v-wenjuan-msft 

@v-dezhili-msft 

Hi guys. Any chance to get your help?

[SudeepGhatakNZ]

@BenWade ,

Do you have a single folder where all the photos are kept?

Is there a reason you are sticking to excel as opposed to a SharePoint list?

Can the users not add an item in the list and attach the meal receipts to it?

The Power Automate will be much easier then.

[BenWade]

1) it is up to me to have a single folder only for photos or not. I don’t have a single folder only for photos because it doesn’t make sense. I have 1 folder.  Throughout the month, I take photos of receipts, put them into this folder and fill the xlsx file which is also in this folder. So basically the content of this folder(1xlsx file and around 20 photos) should be sent automatically on the last working day of the month. This is the goal.
2. Excel file is what employees have to fill. I can’t use the SP list instead of it.

PS

I mentoikend in my post these two links. Maybe they can be useful. 

“

this one helps to identify the last working day
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Send-an-e-mail-notification-the-last-wor...

and this one to send all files at once:

https://learn.microsoft.com/en-us/microsoft-365/community/power-automate-send-sharepoint-files-as-at...

“

[SudeepGhatakNZ]

Sure, I'll send you a sample flow. Give me a day

[SudeepGhatakNZ · Accepted solution]

[BenWade]

Dear Sudeep, 

Thank you for your help 🙏🙏🙏.

could you please adjust the flow for OneDrive business  folder. I messed up, the folder is on my OneDrive, not on SharePoint Site🤦‍♂️🤦‍♂️

[BenWade]

I am trying to adjust it on my own but my flow fails due to this issue:

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

formatDateTime(variables('Last Day of the Month'),'yyyy-MM-dd')

here is my flow:

@SudeepGhatakNZ  Other parts is like yours. Do not know what to do now.

@SudeepGhatakNZ If I may ask. you shortened the flow (last working day of the month) from the link on my post:

Are you sure that your expression achieves the same result?
this one:

Do you want to say that the guy that did the flow overcomplicated it?

[SudeepGhatakNZ]

Hi @BenWade ,

Basically I calculate the last working day using the following logic.

1. Find what is the next month
2. Find the first working day of next month using startofmonth function
3. Subtract 1 day from it to find the last day of the current month.

Would it matter if that turns out to be a Sunday or Saturday or Public Holiday.

That would overcomplicate the flow.

[BenWade]

Dear @SudeepGhatakNZ ,

this is exactly why I would prefer to have this solution
for the last working day of the month as it considers Sunday, Saturday and holidays. Would be better if you use this one and add to this sending all the files from Onedrive for business folder. I have attached the flow for the last working day of the month.

[BenWade]

@SudeepGhatakNZ I don’t know why but sent files cannot be open. Neither photos nor xlsx file. When you test the flow could you please put any jpeg and xlsx files in the folder and try to open them after the flow has been triggered. I don’t understand why it happens. What flow does to them that they can’t be open afterwards. 

[SudeepGhatakNZ]

Send me your flow

[BenWade]

@SudeepGhatakNZ I used this flow and only added OneDrive part.
here is how it looks:

[SudeepGhatakNZ]

ContentBytes should refer the FileContent not Body

[BenWade]

@SudeepGhatakNZ You were completely correct, I changed to 'File Content' and it worked. 
Here's my final flow(video in .zip). Could you please verify if  this flow achieves my goal or not or there are mistakes somewhere in the flow? Maybe you can test my flow somehow.

I tried to add the last step to remove files 'is not equal to' xlsx, but fro soem reason it fails. I think I have the wrong expression or?

Is this correct or not?    last(split(items('Foreach_2')?['{FilenameWithExtension}'],'.'))

[SudeepGhatakNZ]

What value does the expression return? Add a compose and enter that expression to test. Check in the run mode what value does it return?

[BenWade]

I am using your initial proposal. Only replaced sp site to onedreive folder. 

Need help with this:

am I typing it correctly or for onedrive we need another expression to remove all files except xlsx?

last{split(items('Foreach_2'}?['{FilenameWithExtension}'],'.'))

If the same expression should be used paste it in the comments please so I can copy and paste.

And btw your initial step in 'Manually trigger the flow' So I will have to run the flow manually every month? If yes, can we change it so it automatically checks dates and runs the flow when the last working day is identified?