cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IpmoVim
Frequent Visitor

Weekly Office Schedule/Announcement

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.

IpmoVim_5-1662767277571.png

Note: I Added 17 days for testing purposes.

 

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

 

IpmoVim_0-1662766684428.png

 

IpmoVim_1-1662767019699.png

IpmoVim_2-1662767033711.png

 

IpmoVim_10-1662768323142.png

 

 

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

IpmoVim_6-1662767391335.png

IpmoVim_7-1662767397492.png

 

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.

IpmoVim_8-1662768036272.png

 

 

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

 

 

1 REPLY 1
David-
Solution Sage
Solution Sage

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:

David_0-1663007124581.png

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:

David_1-1663007208983.png

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:

David_2-1663007301894.png

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.

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,117)