Add Company Holidays to Your Outlook Calendar Using Power Automate

Below are all the steps required to add your company holidays to your Outlook calendar. This is a simple approach that can easily be updated to pull the holidays and dates from other data sources like Exel, SharePoint, Dataverse…

Overview of the flow that will be created:

All of the holidays (events) will be stored in an array using a compose action.

[
{"Event":"Holiday - New Years","Date":"01/01/2022"},
{"Event":"Holiday - MLK Day","Date":"01/17/2022"},
{"Event":"Holiday - Presidents Day","Date":"02/21/2022"},
{"Event":"Holiday - Memorial Day","Date":"05/30/2022"},
{"Event":"Holiday - Juneteenth Day","Date":"06/20/2022"},
{"Event":"Holiday - Independence Day","Date":"07/04/2022"},
{"Event":"Holiday - Labor Day","Date":"09/05/2022"},
{"Event":"Holiday - Thanksgiving Day","Date":"11/24/2022"},
{"Event":"Holiday - Day After Thanksgiving","Date":"11/25/2022"},
{"Event":"Holiday - Christmas Day","Date":"12/26/2022"}
]

After that, a parse JSON event will be used to make the events available in the apply to each action. Note: after the parse JSON action is added to the flow, click the generate from sample button and paste in the holidays JSON from the step above.

Next, add an apply to each action to the flow. In the action, the output box will hold the body of the parse JSON action.



The convert time zone action is used to standardize the date format. For the base time use the Date field from the parse JSON action.

The last item that needs to be added is the outlook create event action. Note: for the End time property a formula is used: addDays(body(‘Convert_time_zone’),1)






Save the flow and run it.

Edit/Update –
For the End time value, you can input this expression: addHours(body(‘Convert_time_zone’),23) . If you set Is all day event to Yes, you’ll need a timespan that covers most if not all of the day.
Also, if you want to set Is reminder on, it must be input as minutes, so 17 hours would be 1020 minutes.


Use PowerShell to add Holidays to Outlook Calendar

Geeking around with PowerShell today trying to add all company holidays to my Outlook calendar. In the script, I’m creating all day appointments and setting the Show As to out of office. Simple enough!

CLOSE Outlook before running the script.

function get-mailfolders {
	$outlookfolders = @()
	$outlook = New-Object -ComObject Outlook.Application
	foreach ($folder in $outlook.Session.Folders){ 

		foreach($mailfolder in $folder.Folders ) {
			$olkf = New-Object PSObject -Property @{
				Path = $($mailfolder.FullFolderPath)
				EntryID = $($mailfolder.EntryID)
				StoreID = $($mailfolder.StoreID)
			} 

			$outlookfolders += $olkf
		}
	}
	$outlookfolders
}

$outlook = new-object -com Outlook.Application
$folder = get-mailfolders | where {$_.Path -like "*calendar*" -and $_.Path -and $_.Path -like "*$mailbox*"}
$calendar = $outlook.Session.GetFolderFromID($folder.EntryID, $folder.StoreID) 

$holidays = @{"01/01/2017"="New Year’s Day"; "01/16/2017"="Martin Luther King Day"; "02/02/2017"="Presidents Day"; "05/29/2017"="Memorial Day"; `
	"07/04/2017"="Independence Day"; "11/4/2017"="Labor Day"; "11/23/2017"="Thanksgiving Break"; "11/24/2017"="Thanksgiving Break"; "12/25/2017"="Christmas Day"}

foreach($holiday in $holidays.GetEnumerator() | Sort Key)
{
	[string]$hName = $holiday.Value
	$hDate = Get-Date $holiday.Key

	$appt = $calendar.Items.Add(1)
	$appt.Start = $holiday.key.ToString()
	$appt.AllDayEvent = $true
	$appt.Subject = $hName
	$appt.Body = $hName

	<#
	Show As / Status
	0 = Free
	1 = Tentative
	2 = Busy
	3 = Out of Office
	#>
	$appt.BusyStatus = 3

	$appt.Save()
}

You might see this error if you haven’t CLOSED Outlook:

new-object : Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to
the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).
At C:\Code\PS\SPO_BHP\Set_Holidays.ps1:19 char:12
+ $outlook = new-object -com Outlook.Application
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ResourceUnavailable: (:) [New-Object], COMException
+ FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Commands.NewObjectCommand

This post helped guide me in the right direction:
Create a calendar item

Task ‘SharePoint’ reported error (0x80004005): an error occurred in this SharePoint list

From what I have read in other blogs and forums, this might not be the fix for everyone.

Error in Outlook:
0x80004005

Using Fiddler, I was seeing the same reference to http://randomSite.SP.com.

In my case, it turned that I had an outdated / not used URL in my Alternate Access Mappings.
Default Zone was set to http://goodSite.SP.com
Intranet Zone was set to http//randomSite.SP.com

Once http://randSIte.SP.com was removed, the Outlook error went away.
(removing the mapping did NOT induce an IISreset.)

Alternate Access Mappings can be found here:
http://YourCentralAdminSite/_admin/AlternateUrlCollections.aspx

Full error:
Task ‘SharePoint’ reported error (0x80004005) : ‘An error occurred in this SharePoint List (site name – list name). Try updating the folder again. If the problem continues, contact the SharePoint site administrator. HTTP 500.
The server returned the following error message: Exception of type
‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.’

Fiddler referenced this web service:
GetListItemChangesSinceToken