Weekly Office Schedule/Announcement

[IpmoVim]

Hi Power automate community,

I'm currently building a power automate flow that runs every week. This flow is supposed to run on Mondays and send an email with an overview of all the events for that week. All these events are retrieved from SharePoint Lists.

However, I'm currently stuck, as I don't know how to proceed with the flow. 

1. The first thing I did was: Initialize the Array Variables & Define the dates to compare with the SharePoint Lists.

Note: I Added 17 days for testing purposes.

2. I then filtered the SharePoint Lists and appended the results into an array variable.

3. Then I use the Union Function to merge all arrays together.

4. Now, this step is where I'm currently stuck. Like I mentioned previously, my goal is to send an e-mail with all the events for that specific week. That means, if an employee is on vacation when the mail is sent, it has to display the days in that week where the employee is on vacation.

Here an example of what the final product is supposed to look like.

Any type of help is more than welcome.

If there are any questions or if you need more information, please do not hesitate to ask.

I thank you all in advance.

Best Regards,

IpmoVim

[David-]

I am doing something similar that you can probably adapt. I send myself an e-mail each Monday of the events I have on my calendar. After your recurrence event add two compose actions to calculate that week's start and end date.

Start of Week has the following expression:

addDays(utcNow(),mul(dayOfWeek(utcNow()),-1),'yyyy-MM-dd')

End of Week has the following expression:

addDays(utcNow(),add(6,mul(dayOfWeek(utcNow()),-1)),'yyyy-MM-dd')

For this week, the above two expressions return 2022-09-11 and 2022-09-17 respectively.

I use these two variables in my filter query:

Then I use a Select action to get the data I want to send to myself, followed by a Create HTML table from the output of the Select action:

Then you just use the Output from the HTML action in the e-mail. You can style the table, but you will need to put the e-mail into HTML format to add your CSS:

You will need to repeat some of the actions to generate tables for your different SharePoint lists. I have not found a simple way to combine everything into one table when it is coming from multiple lists, however.