Microsoft Power Automate - Check Outlook Shared Calendar and Get your employee's leave status, and automatically send an email for the leave summary every day

 

When you want to know who is on leave today, and this automation is to collect the leave summary and send an email to you by every day morning

1. Get calendar (V2)

2. Compose Start

Inputs: formatDateTime(utcNow(),'yyyy-MM-ddT00:00:00Z')

3. Compose End

Inputs: formatDateTime(addDays(convertTimeZone(utcNow(),'UTC','China Standard Time'),1),'yyyy-MM-ddT00:00:00')

4. Get events (V4)

Calendar id: [Select Your Shared Calendar]

Top Count: 500

5. Filer on-leave events

From: outputs('Get_events_(V4)')?['body/value']

Filter Query:

or(

contains(toLower(item()?['subject']), 'leave'),

contains(toLower(item()?['subject']), 'sick'),

contains(toLower(item()?['subject']), 'timeoff'),

contains(toLower(item()?['subject']), 'time-off'),

contains(toLower(item()?['subject']), 'medical')

)

6. Filter Today Now

From: body('Filter_on-leave_events')

Filter Query: and(

    lessOrEquals(

        ticks(item()?['start']),

        ticks(utcNow())

    ),

    greater(

        ticks(item()?['end']),

        ticks(utcNow())

    )

)

7. Select Date Format

From: body('Filter_Today_Now')

Map:

Subject = item()?['subject']

Start = formatDateTime(item()?['Start'],'dd-MMM-yyyy')

End = formatDateTime(item()?['End'],'dd-MMM-yyyy')

8. Create HTML Table

From: body('Select_Date_Format')

Columns = Custom

Staff = item()?['subject']

Leave Start = item()?['Start']

Leave End = item()?['End']

9. Send an email (v2)

Body = body('Create_HTML_table')