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:

 image

 

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:

image

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:

  • run the VB Script and for that you will need to update the path to the CSV file in the script and change the country name on line 22. The script needs to be executed on all the machines where you wish to import the holidays
  • run the PowerShell script using the following syntax: ScriptFileName.ps1 COUNTRY_NAME EMAIL_ADDRESS. For example: importBankHolidays.ps1 Deutschland administrator@contoso.com

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