We have been getting a few requests for code to automate the import of bank holidays in Outlook and prevent duplication.
The easiest way to achieve this was to actually use the updated Outlook.hol file containing bank holiday definitions that Outlook uses when using the holiday import wizard.
I have written both a VBS and a PowerShell script for this, but before using the scripts we need to add some data in our CSV file.
1. Locating the Outlook.hol file
The Outlook.hol file is located in the 1033 subfolder of the Office install folder. For example: C:\Program Files\Microsoft Office\Office12\1033\Outlook.hol. We need to locate this file and open it using Notepad.exe and copy the holidays that we need into a CSV file.
Say we need the holidays for Germany. We just need to find Germany in the Outlook.hol file and copy all the entries:
2. Creating the CSV file
Once the entries have been copied from the Outlook.hol file, open a new instance of Excel and paste the data in. Next, go to the Data tab on the Ribbon and select Text To Columns and split the first column in two columns using Comma “,” as a delimiter. Once you have done that, insert a new column in front of the first column and type the name of the Country on all the rows and set Country, Holiday, Date as the column titles.
The result should be similar to this:
Alternatively, if you don’t want to use Excel, paste the copied data in a new Notepad document, insert the name of the country on each line and add Country, Holiday, Date on the first line:
Country,Holiday,Date Deutschland,Allerheiligen,01/11/2009 Deutschland,Allerheiligen,01/11/2010 Deutschland,Allerheiligen,01/11/2011 Deutschland,Allerheiligen,01/11/2012 Deutschland,Allerheiligen,01/11/2013 Deutschland,Allerheiligen,01/11/2014 Deutschland,Allerheiligen,01/11/2015 Deutschland,Allerheiligen,01/11/2016 Deutschland,Allerheiligen,01/11/2017 Deutschland,Allerheiligen,01/11/2018 Deutschland,Allerheiligen,01/11/2019 Deutschland,Allerheiligen,01/11/2020 Deutschland,Allerheiligen,01/11/2021 Deutschland,Allerheiligen,01/11/2022 Deutschland,Allerheiligen,01/11/2023 Deutschland,Allerheiligen,01/11/2024 Deutschland,Allerheiligen,01/11/2025 Deutschland,Allerheiligen,01/11/2026 Deutschland,Allerheiligen,01/11/2027 Deutschland,Allerheiligen,01/11/2028
Next, save the CSV file.
3. Running the script
You can choose on of the following:
Please note that the EWS code in the PowerShell script requires Exchange Impersonation Permissions. To set up Exchange Impersonation permissions, please follow the instructions at http://msdn.microsoft.com/en-us/library/exchange/bb204095(v=exchg.140).aspx
VB Script:
1: Const olFolderCalendar = 9
2: Const olAppointmentItem = 1
3: dim fs,objTextFile
4: set fs=CreateObject("Scripting.FileSystemObject")
5: dim arrStr
6: set objTextFile = fs.OpenTextFile("Holidays.csv",1, -2)
7:
8: Dim olApp
9: Dim olCalFld
10: Dim olNS
11: Dim olAppt
12: Dim found
13:
14: On Error Resume Next
15: Set olApp = CreateObject("Outlook.Application")
16: Set olNS = olApp.GetNamespace("MAPI")
17: Set olCalFld = olNS.GetDefaultFolder(olFolderCalendar)
18:
19: Do while NOT objTextFile.AtEndOfStream
20: arrStr = split(objTextFile.ReadLine,",")
21: found = False
22: If (arrStr(0)= "Deutschland") Then
23: For Each item In olCalFld.Items
24: If (item.Subject = arrStr(1)) And (DateValue(item.Start) = DateValue(arrStr(2))) then
25: found = True
26: End If
27: If Err.Number <> 0 Then
28: Err.Clear
29: End If
30: Next
31: If Not found Then
32:
33: Set olAppt = olApp.CreateItem(olAppointmentItem)
34: With olAppt
35: .Subject = arrStr(1)
36: .Start = arrStr(2)
37: .AllDayEvent = True
38: .ReminderSet = False
39: .Location = "Deutschland"
40: .Save()
41: End With
42: End if
43: End If
44:
45: Loop
46:
47: objTextFile.Close
48:
49: Set olCalFld = Nothing
50: Set olNS = Nothing
51: Set olApp = Nothing
52:
53: set objTextFile = Nothing
54: set fs = Nothing
PowerShell script:
1: [String] $EmailAddress = $args[0]
2: [String] $Country = $args[1]
3: $file = Import-Csv -Path "C:\Holidays.csv"
4:
5: write-host "Accessing mailbox" $EmailAddress
6: write-host "Importing holidays for" $Country
7: [String] $dllPath = "C:\Program Files\Microsoft\Exchange\Web Services\1.2\Microsoft.Exchange.WebServices.dll"
8: [Void] [Reflection.Assembly]::LoadFile($dllPath)
9:
10: $Service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2)
11: #$Service.AutodiscoverUrl($mbxName, {$True})
12: $Service.ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $EmailAddress);
13: $Service.Credentials = New-Object Net.NetworkCredential('administrator', 'P@ssw0rd1', "zone")
14: $Service.Url = new-Object Uri("https://zonehubc.zone.lab/ews/exchange.asmx")
15:
16: # This is the root folder from where we want to start searching for the folder we want to delete.
17: # Once we find it, then we will go recursively down that folder.
18: # Other option would be to get the FolderID and start the search straight from there
19: $CalendarFolderId = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar, $EmailAddress)
20: #$RootFolderID = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Root, $mbxName)
21: $CalendarFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($Service, $CalendarFolderId)
22:
23: foreach ($line in $file)
24: {
25: if ($line.Country -eq $Country)
26: {
27: write-host "Holiday name and date: " $line.Holiday $line.Date
28: $SearchFilterCollection = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+SearchFilterCollection([Microsoft.Exchange.WebServices.Data.LogicalOperator]::And)
29: $SearchFilter1 = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.AppointmentSchema]::Subject,$line.Holiday)
30: $Start = new-object System.DateTime
31: $Start = $Start = [System.DateTime]::Parse($line.Date)
32: $SearchFilter2 = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.AppointmentSchema]::Start, $Start)
33: $SearchFilterCollection.Add($SearchFilter1)
34: $SearchFilterCollection.Add($SearchFilter2)
35:
36: $itemView = new-object Microsoft.Exchange.WebServices.Data.ItemView(20)
37: $itemView.PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
38:
39: $findResults = $Service.FindItems([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$SearchFilterCollection,$itemView)
40:
41: if ($findResults.Items.Count -eq 0)
42: {
43: $Appointment = New-Object Microsoft.Exchange.WebServices.Data.Appointment -ArgumentList $service
44: #Set Subject
45: $Appointment.Subject = $line.Holiday
46: #Set Start Time
47: $Appointment.Start = [System.DateTime]::Parse($line.Date)
48: #Set Start Time
49: $Appointment.End = [System.DateTime]::Parse($line.Date).AddDays(1)
50: #Mark as all day event
51: $Appointment.IsAllDayEvent = $true;
52: #Show as free
53: $Appointment.LegacyFreeBusyStatus = [Microsoft.Exchange.WebServices.Data.LegacyFreeBusyStatus]::Free;
54: #Create Appointment will save to the default Calendar
55: $Appointment.Save($CalendarFolderId)
56: write-host "Element created!"
57: }
58: else
59: {
60: write-host "Holiday exists!"
61: #foreach($item in $findResults.Items)
62: #{
63: # $item.Load()
64: # $item.Delete([Microsoft.Exchange.WebServices.Data.DeleteMode]::HardDelete)
65: # write-host "Element deleted!"
66: #}
67: }
68: }
69: }
70:
71: $findResults = $null
72: $itemView = $null
73: $SearchFilter1 = $null
74: $SearchFilter2 = $null
75: $SearchFilderCollection = $null
76: $CalendarFolder = $null
77: $CalendarFolderId = $null
78: $Service = $null