SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


Sidebar

Sponsor:


Recently Changed Pages:

View All Pages


View All Tags







WIKI Disclaimer: As with most other things on the Internet, the content on this wiki is not supported. It was contributed by me and is published “as is”. It has worked for me, and might work for you.
Also note that any view or statement expressed anywhere on this site are strictly mine and not the opinions or views of my employer.


Pages with comments

PageDateDiscussionTags
2019/11/18 13:52 1 Comment

View All Comments

spls

Use PowerShell with SharePoint Online Lists and Office DocProperties

The script below retrieves information from sharepoint online lists and puts that in prepared office files using docproperties.

Using the Templates

The sharepoint sites should have a tempate folder, to modify or prepare the templates follow these steps:

  • Go to the Templates section by clicking on the name on the left side
  • Click on the template you want to edit, this will open the template in Word or Excel. Note that all editing should be done with the desktop version of Word and Excel. If the online version opens, close the document without saving and right click the document to select Open → Open In Word/Excel.
  • All the text and layout can be edited as required.
  • To add or modify the Advanced Properties which get updated from the Sharepoint Online lists follow these steps:
    • To view all splsilable Advanced Properties:
      • Go to File → Properties → Advanced Properties
      • Click on Custom
      • In the properties section all the advanced properties are listed with a descriptive name that is similar to the name of the property in the sharepoint list
    • To view which Advanced Property is used:
      • Select all text in the template and right click anywhere
      • Click the “Toggle Field Codes” option
      • Now all properties are shown in the template
    • To add an Advanced Property:
      • Place the cursor on the location where you want to add the property
      • Go to Insert → Quick Parts → Field
      • Locate and click “DocProperty” in the Field Name section
      • Select the Advanced Property you want to add and click OK

Control Through SP List

The script below can be started manually or scheduled (see requirements within the script). A advice to create a scheduled task from the Windows Taks Schedular and then start it manually. It checks the provided list name for a few important fields:

  • Title: This is a description of the run you are going to start, and will also be the name of the folder where the documents will be saved.
  • Template: This is the letter you want to create. If you want to use a different template simply modify the name. Note that the exact name of an existing template is required.
  • Processed: By default the value is set to “Initiating”. This will not start the creation of letters. When all information is correct change the value to “Create Letters”. When all documents are created the value will automatically change to “Done”. To re-run or run the process with a different template change the value to “Create Letters” again.
  • Output: This sets the type of output that will be created:
    • Normal: Creates the letters for all of the sp list rows
    • Print: Creates the letters for all of the sp list rows and immediately prints all letters to the default printer
    • Test: Create the letters for 6 sp list rows
    • Test Print: Creates the letters for 6 sp list rows and immediately prints the letters to the default printer

The Script

# Author: Sjoerd Hooft / https://www.linkedin.com/in/sjoerdhooft/
 
### Versioning and functionality ##################################################################
### SPLS - SharePoint List Script
# 2019 - Sjoerd Hooft #
### Create Word letters:
#### 1001 Template
#### 1005 Dividend
#### 1006 AddressLabels
### Create Excel docs
#### 1007 attendance (used during the meeting)
#### 1008 payout (this is sent to finance for dividend payout)
### Technical functionality
#### The script uses SharePoint Online lists as datasource for creating Word and Excel templates 
### Technical Maintenance Contact
#### ....
### Application Key Users
#### ....
# 
###################################################################################################
 
### Requirements ##################################################################################
### Powershell
# Command line: -Executionpolicy Bypass -nologo -noninteractive -command "& {D:\spls\splsletters.ps1}"
# Powershell: set-executionpolicy bypass
### Account to run script with (sa_spls):
# IE Enhanced Security Configuration -> Off
# Add sharepoint online to trusted sites: getshifting.sharepoint.com
# Visit site getshifting.sharepoint.com
# Open Word and Excel documents from SP Online as sharepoint user (sa_spls_online@getshifting.com)
# Whenever asked, set Internet Explorer to remember your credentials 
### Scheduled Tasks
# Excel/Word bug: These directories have to exist for this script to work with excel/word while ran as a scheduled task
# 32bit: * C:\Windows\System32\config\systemprofile\Desktop
# 64bit: * C:\Windows\SysWOW64\config\systemprofile\Desktop
# Expected Error Excel: Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'xxxxxx'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook.
# Expected Error Word: This can vary as Word fails to open the fail but that does not produce an error. The first action upon the file will provide an error, so it depends on the template. 
## Possible error: Exception calling "InvokeMember" with "5" argument(s): "Method 'System.__ComObject.Item' not found."
### Printing
# The print option requires a default printer installed on the system where the script runs
###################################################################################################
 
### Bugs ##########################################################################################
### No script bugs known yet 
# 
###################################################################################################
 
### How-To ########################################################################################
# Add an item to the SharePoint Online list spls Process. Orchestrator (or a different schedular) starts the script. Depending on the selected template the correct letters or management files get created. 
###
# When changing and testing
### Set the Output column to Test and the "Send report to" column to your own email address. Give the title a unique name. This will only create 5 letters in a new directory under Documents and will send the report to your email address. 
###################################################################################################
 
### Script Overview ###############################################################################
### Fase 1 ########################################################################################
# Set script variables
# Start Logging
# Check for required modules
# Reporting Variables 
# SharePoint Variables
# Application Variables 
# Default functions 
# Application functions
### Fase 2 ########################################################################################
# Collect spls Process Information
### Fase 3 ########################################################################################
# Start Excel or Word
### Fase 4 ########################################################################################
# Create Labels if applicable
### Fase 5 ########################################################################################
# Create Letters or files
## Collect spls ShareHolders 
## Collect spls General Information if required
## Collect spls Shares Information if required
## Calculate Dividend if Applicable
## Create Letters or files depending on template
### Fill Advanced Properties
## Print Letter if applicable
## Update spls Process list 
### Fase 6 ########################################################################################
# Quit Word or Excel
### Fase 7 ########################################################################################
# Send email report 
### Fase 8 ########################################################################################
# Cleanup temporary files
# Stop Logging 
###################################################################################################
 
########################################## Start Fase 1 ###########################################
 
### Script Variables ###
$scriptname = [System.IO.Path]::GetFilenameWithoutExtension($MyInvocation.MyCommand.Path.ToString())
$scriptlocation = Split-Path $myinvocation.mycommand.path
$date = (Get-Date).ToString('yyyyMMdd-HHmm')
 
### Start transcript for full logging capabilities ###
start-transcript -path "$scriptlocation\$scriptname-$date-logtranscript.txt"
 
### Define global logfile ###
$globallog = "$scriptlocation\$scriptname-globallog.txt"
 
### Check Required Modules ###
if (Get-Module -Listsplsilable -Name Microsoft.Online.SharePoint.PowerShell) {
    Write-host "Module Microsoft.Online.SharePoint.PowerShell exists"
	# Module needs to be imported and Installed: #Install-Module -Name Microsoft.Online.SharePoint.PowerShell
	Import-Module Microsoft.Online.SharePoint.PowerShell}
else {Write-host "Module Microsoft.Online.SharePoint.PowerShell does not exist"
	exit 100
}
 
### Reporting variables ###
# Tomail will be overwritten with value from spls Process list, default to and cc will be spls application management: tam@getshifting.com
$toemail = "tam@getshifting.com"
$ccmail = "tam@getshifting.com"
$mailserver = "smtp1.getshifting.com" 
$subject = "$date $scriptname report" 
$fromemail = "$scriptname@getshifting.com"
$sendlogfile = $false 
# Email Report Body
$info = "L.S., <br>"
$info += "<br>"
$info += "This is the automatic report for spls Letters. See below for the message log.<br>"
$info += "<br>"
 
### SharePoint Variables ###
$spusername = "sa_spls_online@getshifting.com"
# NOTE: Run once per server per user: read-host -assecurestring | convertfrom-securestring | out-file "$scriptlocation\config\spcreds.txt"
$sppassword = Get-Content "$scriptlocation\config\spcreds.txt" | ConvertTo-SecureString
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $spusername,$sppassword
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
 
### SharePoint Variables (running in manual mode) ###
#$cred = Get-Credential
#$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
 
### Application variables ###
$splssite = "https://getshifting.sharepoint.com/sites/teamsite-spls/"
$splssiterelative = "/sites/teamsite-spls/"
$splstemplates = $splssite + "Word Templates/"
$splstargetdir = "Shared Documents"
 
# Various spls Lists
$splsprocesslist = "spls Process"
$splsshareholderslist = "spls Shareholders"
$splsshareslist = "spls Shares"
$splsgenerallist = "spls General Information"
 
# Connect to spls Site 
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($splssite)
$Context.Credentials = $Credentials
$Context.ExecuteQuery()
 
# Filename Variables
# Characters to keep in the file name 
$pattern = '[^a-zA-Z0-9-,. ]'
# All other characters will be replaced by
$replacechar = '-'
# Max length of shareholder specific part of filename
$filenamelength = 40
 
# 4008 presentielijst Variables
$datarow4007 = 2
$datarow4008 = 5
 
# Test modus Variables 
$testmodeteller = 0
$testmodecount = 6
 
### Default Functions ###
Function Send-Email ($subject, $info){
	if ($sendlogfile -eq $true){
		Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -BodyAsHtml -Attachments "$scriptlocation\$scriptname-$date-logtranscript.txt"
	}else{
		Send-MailMessage -To $toemail -From $fromemail -cc $ccmail -SmtpServer $mailserver -Subject $subject -Body $info -BodyAsHtml 
	}
}
 
function Get-ScriptLineNumber { 
	return $MyInvocation.ScriptLineNumber }
 
new-item alias:__LINE__ -value Get-ScriptLineNumber
 
### Application Functions ###
# Create Folder on SharePoint
Function CheckSPOFolderExists(){
  param
    (
        [Parameter(Mandatory=$true)] [string] $MeetingFolder
    )
    Try {
        $Web = $Context.Web
        $Context.Load($Web)
        $Context.ExecuteQuery()
 
        #Check if folder already exists
        Try {
            $Folder = $Web.GetFolderByServerRelativeUrl("Shared Documents/" + $MeetingFolder)
            $Context.Load($Folder)
            $Context.ExecuteQuery()
			Write-host $(__LINE__) "Sharepoint folder $meetingfolder already exists. We will use this folder for the letters. Existing documents will be overwritten. "
        }
        Catch {
            Write-host $(__LINE__) "Sharepoint folder $meetingfolder doesn't exist yet."
            #Get the List Root Folder
            $ParentFolder=$Web.GetFolderByServerRelativeUrl($splstargetdir)
 
            #Create New Folder
            $Folder = $ParentFolder.Folders.Add($MeetingFolder)
            $ParentFolder.Context.ExecuteQuery()
            Write-host $(__LINE__) "Sharepoint folder $meetingfolder was created successfully!" 
        }
		#Check if folder already exists - again for temp dir
        Try {
            $Folder = $Web.GetFolderByServerRelativeUrl("Shared Documents/" + $MeetingFolder + "/Temp")
            $Context.Load($Folder)
            $Context.ExecuteQuery()
			Write-host $(__LINE__) "Sharepoint Temp folder in $meetingfolder already exists. We will use this folder for the temp letters. Existing documents will be overwritten. "
        }
        Catch {
            Write-host $(__LINE__) "Sharepoint Temp folder in $meetingfolder doesn't exist yet."
            #Get the List Root Folder
            $ParentFolder=$Web.GetFolderByServerRelativeUrl($splstargetdir + "/" + $MeetingFolder)
 
            #Create New Folder
            $Folder = $ParentFolder.Folders.Add("Temp")
            $ParentFolder.Context.ExecuteQuery()
            Write-host $(__LINE__) "Sharepoint Temp folder in $meetingfolder was created successfully!" 
        }
    }
    Catch {
        Write-host $(__LINE__) "$($_.Exception.Message)"
		$message = "$(__LINE__) $date - Something went wrong with the SharePoint Folder. Check the transcript log for more details"
		$message | Out-File $globallog -append
		$info += "$message <br>"
		$sendlogfile = $true
		Send-Email $subject $info
		exit 10
    }
}
 
# Creating the temporary Shareholder Letter
Function Copy-SPOFile(){
  param
    (
        [Parameter(Mandatory=$true)] [string] $SourceTemplate,
		[Parameter(Mandatory=$true)] [string] $targetLetterTemp
    )
    Try {
		#Copy the File
        $MoveCopyOpt = New-Object Microsoft.SharePoint.Client.MoveCopyOptions
        $Overwrite = $True
        [Microsoft.SharePoint.Client.MoveCopyUtil]::CopyFile($Context, $SourceTemplate, $targetLetterTemp, $Overwrite, $MoveCopyOpt)
        $Context.ExecuteQuery()
	}
	Catch {
		Write-host $(__LINE__) "$($_.Exception.Message)"
		$message = "$(__LINE__) $date - Something went wrong creating the temporary ShareHolder letter for $splsShareHolderName. Check the transcript log for more details"
		$message | Out-File $globallog -append
		$info += "$message <br>"
    }
}
 
# Removing the temporary Shareholder Letter
Function Remove-SPOFile(){
  param
    (
        [Parameter(Mandatory=$true)] [string] $targetLetterTempRelative
    )
    Try { 
        #Get the file to delete
        $File = $Context.Web.GetFileByServerRelativeUrl($targetLetterTempRelative)
        $Context.Load($File)
        $Context.ExecuteQuery()
 
        #Delete the file
        $File.DeleteObject()
        $Context.ExecuteQuery()
     }
    Catch {
        Write-host $(__LINE__) "$($_.Exception.Message)"
		$message = "$(__LINE__) $date - Something went wrong deleting the temporary ShareHolder letter for $splsShareHolderName. It is probably locked. Check the transcript log for more details"
		$message | Out-File $globallog -append
		$info += "$message <br>"
    }
}
 
########################################## Start Fase 2 ###########################################
 
# Set list to spls Process
$listname = $splsprocesslist
# Checking for list items that need to be processed. If more than one list item needs to be processed, only the newest one gets processed. Subsequent list items get processed in subsequent script runs. 
$activerows = 0
 
# Getting spls Prccess Information 
try{
	$lists = $Context.web.Lists
	$list = $lists.GetByTitle($ListName)
	$listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
	$Context.load($listItems)
	$Context.executeQuery()
 
	foreach($Meeting in $listItems){
		# SharePoint holds internal names for list columns. Use command below to output all fields.
		#$Meeting.FieldValues
 
		# If processed column is "Create Letters" we should continue, otherwise there is nothing to do. 
		if ($Meeting["Processed"] -eq "Create Letters"){
			$activerows++
			$splsProcessID = $Meeting["ID"]
			$splsProcessPrint = $Meeting["Print"]
			$splsProcessTitle = $Meeting["Title"]
			$splsProcessTemplate = $Meeting["Template"]
			$splsProcessPrintDatum = $Meeting["Print_x0020_Datum_x0020_Letter"]
			$splsProcessNummerJaarvergadering = $Meeting["Nummer_x0020_jaarvergadering"]
			$splsProcessVoorgaandeJaar = $Meeting["Voorgaande_x0020_jaar"]
			$splsProcessDagDatumJaar = $Meeting["spls_x0020_Meeting_x0020_Datum_x0"]
			$splsProcessDagDatumLang = $Meeting["Dag_x0020_datum_x0020_lang"]
			$splsProcessDagDatumMedium = $Meeting["spls_x0020_Meeting_x0020_Datum_x00"]
			$splsProcessDagDatumKort = $Meeting["spls_x0020_Meeting_x0020_Datum_x01"]
			$splsProcessOpeningJaarvergadering = $Meeting["Opening_x0020_jaarvergadering"]
			$splsProcessStartJaarvergadering = $Meeting["Start_x0020_jaarvergadering"]
			$splsProcessEindeJaarvergadering = $Meeting["splsMeetingEindtijd"]
			$splsProcessUitersteAanleverDatumMedium = $Meeting["Uiterste_x0020_aanleverdatum_x00"]
			$splsProcessUitersteAanleverDatumKort = $Meeting["Uiterste_x0020_aanleverdatum_x000"]
			$splsProcessAgendaOntvangstDatum = $Meeting["Agenda_x0020_Ontvangst_x0020_Dat"]
			$splsProcessDividendDay = $Meeting["Dividend_x0020_Day"]
			$splsProcessDividendDayReactionDate = $Meeting["Dividend_x0020_day_x0020_reactio0"]
			$splsProcessDividendDayReactionTime = $Meeting["Dividend_x0020_day_x0020_reactio"]
			$splsProcessSendReport = $Meeting["Send_x0020_report_x0020_to"]
 
			# Change email adres of email report
			$toemail = "$splsProcessSendReport"
 
			Write-host $(__LINE__) "List items that need to be processed so far: $activerows"
			Write-host $(__LINE__) "Currently processing $splsProcessTitle"
			# Only log this information in test modus
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){
				Write-host $(__LINE__) "Listing all spls Process information: "
				$splsProcessID
				$splsProcessPrint
				$splsProcessTitle
				$splsProcessTemplate
				$splsProcessPrintDatum
				$splsProcessNummerJaarvergadering
				$splsProcessVoorgaandeJaar
				$splsProcessDagDatumJaar
				$splsProcessDagDatumLang
				$splsProcessDagDatumMedium
				$splsProcessDagDatumKort
				$splsProcessOpeningJaarvergadering
				$splsProcessStartJaarvergadering
				$splsProcessEindeJaarvergadering
				$splsProcessUitersteAanleverDatumMedium
				$splsProcessUitersteAanleverDatumKort
				$splsProcessAgendaOntvangstDatum
				$splsProcessDividendDay 
				$splsProcessDividendDayReactionDate
				$splsProcessDividendDayReactionTime
				$splsProcessSendReport
				Write-host $(__LINE__) "End Listing all spls Process information. There should be 20 rows of information. "
			}
		}
	}
	# Now stop everything if there is no work to so
	if ($activerows -eq 0){
		Write-host $(__LINE__) "No Active Rows Found: $activerows"
		# Stop transcript as there is no work to do 
		Stop-transcript
		# Remove logfile as there is no useful information anyway
		Remove-Item "$scriptlocation\$scriptname-$date-logtranscript.txt"
		# Append prove of running to global logfile
		$message = "$date - no work to do"
		$message | Out-File $globallog -append
		$info += "$message <br>"
		# $sendlogfile = $true
		# Do not send an email in production, nobody would read the emails anymore. 
		# Send-Email $subject $info
		exit 0
	}
	# Start processing spls letters 
	if ($activerows -ge 1){
		Write-host $(__LINE__) "We have work to do, starting the first of: $activerows"
		$message = "$date - $activerows meeting found to create letters for. Starting on $splsProcessTitle."
		$message | Out-File $globallog -append
		$info += "$message <br>"
		CheckSPOFolderExists -MeetingFolder $splsProcessTitle
	}
} 
catch{
	Write-host $(__LINE__) "$($_.Exception.Message)"
	# Stop transcript  
	Stop-transcript
	# Append prove of running to global logfile
	$message = "$(__LINE__) $date - something went wrong processing spls Process Information. Please consult transcript log"
	$message | Out-File $globallog -append
	$info += "$message <br>"
	$sendlogfile = $true
	Send-Email $subject $info
	exit 2
}
 
########################################## Start Fase 3 ###########################################
 
# Start excel if required
if ($splsProcessTemplate -like "*.xlsx"){
	Write-host $(__LINE__) "Creating excel file $targetLetterxlsxTemp for meeting $splsProcessTitle and starting excel"
	$SourceTemplate = $splstemplates + $splsProcessTemplate
	$simpletemplatename = ($splsProcessTemplate).ToString().Replace(".xlsx","")
	$targetLetterxlsx = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/" + $simpletemplatename + "-"+ $splsProcessTitle + ".xlsx"
	$targetLetterxlsxTemp = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/Temp/" + $simpletemplatename + "-temp.xlsx"
 
	# Copy the Presentielijst to SP folder
	Copy-SPOFile $SourceTemplate $targetLetterxlsxTemp
 
	# Set Excel Report Properties
	$Excel = New-Object -ComObject Excel.Application
	# Set to true for monitoring progress, to false for scheduled creation
	$Excel.visible = $false
	# Set to False to save existing files without warning
	$Excel.DisplayAlerts = $False
	# Open Excel file
	$workfile = $Excel.Workbooks.open($targetLetterxlsxTemp)
	$Sheet = $workfile.Worksheets.Item(1)
}
 
# Start word if required 
if ($splsProcessTemplate -like  "*.docx"){
	Write-host $(__LINE__) "Starting Word for meeting $splsProcessTitle"
	$app = New-Object -ComObject Word.Application
	$app.Visible = $false
}
 
########################################## Start Fase 4 ###########################################
 
# Creating Address Labels if required
if ($splsProcessTemplate -eq "1006 AddressLabels.docx"){
	Write-host $(__LINE__) "Creating labelfile $targetLetterLabelTemp voor meeting $splsProcessTitle"
	$SourceTemplate = $splstemplates + $splsProcessTemplate
	$simpletemplatename = ($splsProcessTemplate).ToString().Replace(".xlsx","")
	$targetLetterLabel = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/" + $simpletemplatename + "-"+ $splsProcessTitle + ".docx"
	$targetLetterLabelTemp = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/Temp/" + $simpletemplatename + "-temp.docx"
 
	# Copy the Label fiole to SP folder
	Copy-SPOFile $SourceTemplate $targetLetterLabelTemp
 
	# Open Word Document 
	$labeldoc = $app.Documents.Open($targetLetterLabelTemp)
	$Selection = $app.Selection
 
	# Avery 3484 labels (https://www.avery.nl/word-template-3484 )
	# right margin = 7.9 mm | 0.31 inch | 22 points
	# left margin  = 2.2 mm | 0.08 inch | 6 points --> Labels are not displayed correctly. Changed to 22. 
	$numberofcolumns = 2
    $Selection.PageSetup.Orientation    = 0     # Portrait
    #$Selection.PageSetup.PaperSize      = 2     # (Letter) 8.5 x 11 inch
	$Selection.PageSetup.PaperSize = [Microsoft.Office.Interop.Word.WdPaperSize]::wdPaperA4
    $Selection.PageSetup.Gutter         = 0 
    $Selection.PageSetup.HeaderDistance = 0    # 0.5 inch
    $Selection.PageSetup.FooterDistance = 0    # 0.5 inch
    $Selection.PageSetup.TopMargin      = 0  
    $Selection.PageSetup.BottomMargin   = 0
    $Selection.PageSetup.RightMargin    = 22
    $Selection.PageSetup.LeftMargin     = 22
 
	# Create table for label layout
    # Shareholderinfo has not run yet - variable should be considered as a max number of shareholders 
	$ShareholderCount = 180
	# Number of rows is the number of shareholders divided by the number of columns, rounded up to above in case of a uneven number
	#$Table = $Selection.Tables.add($Selection.Range,60,2)
    $Table = $Selection.Tables.add($Selection.Range,[int][Math]::Ceiling($ShareholderCount/$numberofcolumns),$numberofcolumns)
	# Optional: set font and font size
	#$Table.Style.Font.Name = 'Arial'
    #$Table.Style.Font.Size = '10'
	$LabelRow = 1
    $LabelColumn = 1
}
 
########################################## Start Fase 5 ###########################################
 
########### Collect spls Shareholders ##########
 
# Get all shareholders, set list to spls Shareholders 
$listname = $splsshareholderslist
 
try{
	$lists = $Context.web.Lists
	$list = $lists.GetByTitle($ListName)
	$listItemsSH = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
	$Context.load($listItemsSH)
	$Context.executeQuery()
 
	foreach($ShareHolder in $listItemsSH){
		# SharePoint holds internal names for list columns. Use command below to output all fields.
		#$ShareHolder.FieldValues
 
		# Get All ShareHolder Information
		$splsShareHolderID = $ShareHolder["ID0"]
		$splsShareHolderIsOrganization = $ShareHolder["IsOrganization"]
		$splsShareHolderName = $ShareHolder["Name"]
		$splsShareHolderName2 = $ShareHolder["Name2"]
		$splsShareHolderName3 = $ShareHolder["Name3"]
		$splsShareHolderAddress = $ShareHolder["Address"]
		$splsShareHolderPostalCodeCity = $ShareHolder["Postal_x0020_code_x0020_and_x002"]
		$splsShareHolderCountry = $ShareHolder["Country"]
		$splsShareHolderSalutation = $ShareHolder["Salutation"]
		$splsShareHolderName4 = $ShareHolder["Name4"]
		$splsShareHolderBankAccount = $ShareHolder["Bank_x0020_account"] 
		$splsShareHolderTelephoneNumber = $ShareHolder["Telephone_x0020_number"]
		$splsShareHolderCreditorNumber = $ShareHolder["Creditor_x0020_number"]
		$splsShareHolderDivTaxApplicable = $ShareHolder["Dividendtax_x0020_applicable"]
		$splsShareHolderIsActive = $ShareHolder["IsActive"]
		$splsShareHolderReasonInactive = $ShareHolder["Reason_x0020_for_x0020_inactive"]
		$splsShareHolderFormerShareHolder = $ShareHolder["FormerShareholder"]
 
		# Only log this information in test modus
		if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){
			Write-host $(__LINE__) "Listing all spls Shareholder information: "
			$splsShareHolderID
			$splsShareHolderIsOrganization
			$splsShareHolderName
			$splsShareHolderName2
			$splsShareHolderName3
			$splsShareHolderAddress
			$splsShareHolderPostalCodeCity
			$splsShareHolderCountry
			$splsShareHolderSalutation
			$splsShareHolderName4
			$splsShareHolderBankAccount
			$splsShareHolderTelephoneNumber
			$splsShareHolderCreditorNumber
			$splsShareHolderDivTaxApplicable
			$splsShareHolderIsActive
			$splsShareHolderReasonInactive
			$splsShareHolderFormerShareHolder
			Write-host $(__LINE__) "End Listing all spls ShareHolder information. Some fields might be empty. There should be a maximum of 17 rows of information. "
		}
 
		# If template is for dividend we need the general and share information as well 
		if (($splsProcessTemplate -like "4005*") -or ($splsProcessTemplate -like "*.xlsx")){
 
			########### Collect spls General Information ##########
			# Get shares, tax and dividend information. We only have to do this once as the information is static
			if (([string]::IsNullOrEmpty($splsInfoTotalNumberOfShares))){
				Write-host $(__LINE__) "Getting spls General Information"
 
				# Set listname to spls General Information
				$listname = $splsgenerallist
 
				try{
					$lists = $Context.web.Lists
					$list = $lists.GetByTitle($ListName)
					$listItemsInfo = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
					$Context.load($listItemsInfo)
					$Context.executeQuery()
 
					foreach($information in $listItemsInfo){
						#$information.FieldValues
						# We only need the information from this year
						if ($information["Year"] -eq $splsProcessDagDatumJaar){
							$splsInfoYear = $information["Year"]
							$splsInfoTotalNumberOfShares = $information["Total_x0020_number_x0020_of_x002"]
							$splsInfoNominalValue = $information["Nominal_x0020_value"]
							$splsInfoDividend = $information["Dividend"]
							$splsInfoTaxRate = $information["Tax_x0020_rate"]
 
							# Only log this information in test modus
							if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){
								Write-host $(__LINE__) "Listing all General Infomation"
								$splsInfoYear
								$splsInfoTotalNumberOfShares
								$splsInfoNominalValue
								$splsInfoDividend
								$splsInfoTaxRate
								Write-host $(__LINE__) "End listing General information. There should be 5 lines of information"
							}
						}
					}
					# Checking if $splsInfoTotalNumberOfShares is still empty. If it's not set something is wrong in the input
					if (([string]::IsNullOrEmpty($splsInfoTotalNumberOfShares))){
						Write-host $(__LINE__) "Something goes wrong with the spls input. "
						# Append prove of running to global logfile
						$message = "$(__LINE__) $date - something went wrong retrieving spls input. Please consult transcript log"
						$message | Out-File $globallog -append
						$info += "$message <br>"
						$sendlogfile = $true
						Send-Email $subject $info
						exit 54
					}
				}
				catch{
					Write-host $(__LINE__) "$($_.Exception.Message)" -foregroundcolor red
					# Stop transcript  
					Stop-transcript
					# Append prove of running to global logfile
					$message = "$(__LINE__) $date - something went wrong retrieving spls General Information for $splsProcessTitle. Please consult transcript log"
					$message | Out-File $globallog -append
					$info += "$message <br>"
					$sendlogfile = $true
					Send-Email $subject $info
					exit 55
				}
			}else{
				# spls General Information is already splsilable
				#Write-host $(__LINE__) "spls General Information is already splsilable"
			}
 
			########### Collect spls Shares Information ##########
			# Set list to spls Shares
			$listname = $splsshareslist
			$splsShareHolderTotalShares = 0
 
			try{
				$lists = $Context.web.Lists
				$list = $lists.GetByTitle($ListName)
				$listItemsShares = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
				$Context.load($listItemsShares)
				$Context.executeQuery()
 
				foreach($sharepackage in $listItemsShares){
					#$sharepackage.FieldValues
					if ($sharepackage["Shareholder"] -eq $splsShareHolderID){
						$splsSharesFirstShare = $sharepackage["Share_x0020_number"]
						$splsSharesLastShare = $sharepackage["Until_x0020_number"]
						$splsSharesShareHolder = $sharepackage["Shareholder"]
 
						# Get total number of shares in package and create a grand total of shares for the shareholder 
						$packageshares = $splsSharesLastShare - $splsSharesFirstShare + 1
						$splsShareHolderTotalShares = $splsShareHolderTotalShares + $packageshares
					}	
				}
 
				# Get percentage of ShareHolder Shares 
				$splsShareHolderTotalSharesPercentage = $splsShareHolderTotalShares / $splsInfoTotalNumberOfShares * 100
				Write-host $(__LINE__) "Total Shares for Shareholder ID $splsSharesShareHolder is $splsShareHolderTotalShares which is $splsShareHolderTotalSharesPercentage %"
			}
			catch{
				Write-host $(__LINE__) "$($_.Exception.Message)" -foregroundcolor red
				# Stop transcript  
				Stop-transcript
				# Append prove of running to global logfile
				$message = "$(__LINE__) $date - something went wrong getting the spls Shares for $splsProcessTitle. Please consult transcript log"
				$message | Out-File $globallog -append
				$info += "$message <br>"
				$sendlogfile = $true
				Send-Email $subject $info
				exit 56
			}
 
			########## Calculate all dividend ##########
			# Gross (bruto) dividend = TotalShares * Dividend 
			# Because locale is mixed between system and sharepoint list input, $splsInfoDividend is seen as a whole number instead of x,xx
			# This is server dependent. spls server correctly sees all info. 
			# - This line is only necessary if dividend is displayed in output as xxx instead of x.xx: $splsInfoDividendTemp = $splsInfoDividend / 100
			$splsInfoDividendTemp = $splsInfoDividend
			$splsShareHolderGrossDividendTemp = $splsShareHolderTotalShares * $splsInfoDividendTemp
			# Display as currency in dutch locale (must be kept on same line)
			[Threading.Thread]::CurrentThread.CurrentCulture = 'nl-NL'; $splsShareHolderGrossDividend = "{0:c}" -f  $splsShareHolderGrossDividendTemp
			# Amount of dividend tax = Gross dividend * tax percentage
			# Then round the number down (as explained here: https://download.belastingdienst.nl/belastingdienst/docs/aangifte_dividendbelasting_div0012z37fol.pdf)
			$splsShareHolderDividendTaxTemp = $splsShareHolderGrossDividendTemp * $splsInfoTaxRate
			# On request of Owner, please do not round the number down. All further calculations are done with $splsShareHolderDividendTaxTemp2
			# Round down:
			#$splsShareHolderDividendTaxTemp2 = [math]::floor($splsShareHolderDividendTaxTemp)
			# No round down, but set it to two decimals, away from zero (https://www.madwithpowershell.com/2013/10/math-in-powershell.html)
			$splsShareHolderDividendTaxTemp2 = [math]::round($splsShareHolderDividendTaxTemp,2,[system.midpointrounding]::AwayFromZero)
			# Display as currency in dutch locale (must be kept on same line)
			[Threading.Thread]::CurrentThread.CurrentCulture = 'nl-NL'; $splsShareHolderDividendTax = "{0:c}" -f $splsShareHolderDividendTaxTemp2
			# Net (netto) dividend = Gross dividend - dividend tax
			$splsShareHolderNetDividendTemp = $splsShareHolderGrossDividendTemp - $splsShareHolderDividendTaxTemp2
			# Display as currency in dutch locale (must be kept on same line)
			[Threading.Thread]::CurrentThread.CurrentCulture = 'nl-NL'; $splsShareHolderNetDividend = "{0:c}" -f  $splsShareHolderNetDividendTemp
			Write-host $(__LINE__) "Shareholder ID $splsSharesShareHolder Gross Dividend is $splsShareHolderGrossDividend so after deducting $splsShareHolderDividendTax for tax the total amount is $splsShareHolderNetDividend"			
		}
 
		########## Create Letters ##########
		# Business Rules:
		## If ShareHolder is not an active shareholder it shouldn't receive a letter
		## Applied logic to only create dividend letters if tax should be applied or not using the correct template
		if (($splsShareHolderIsActive -eq "n") -or ($splsShareHolderFormerShareHolder -eq "y")){
			Write-host $(__LINE__) "Shareholder $splsShareHolderName is not active. No letter will be created."
		}elseif (($splsProcessTemplate -eq "1005 Dividend met belasting.docx") -and ($splsShareHolderDivTaxApplicable -eq "n")){
			Write-host $(__LINE__) "Shareholder $splsShareHolderName is not tax eligible. No letter will be created."
		}elseif (($splsProcessTemplate -eq "1005 Dividend zonder belasting.docx") -and ($splsShareHolderDivTaxApplicable -eq "y")){
			Write-host $(__LINE__) "Shareholder $splsShareHolderName is tax eligible. No letter will be created."
		}elseif ($splsProcessTemplate -like "1007 attendance.xlsx"){
			Write-host $(__LINE__) "Adding shareholder $splsShareHolderName information to 1007 attendance"
 
			# Add data for each shareholder 
			$Sheet.Cells.Item($datarow4007, 2) = [string]$splsShareHolderName
			$Sheet.Cells.Item($datarow4007, 3) = [string]$splsShareHolderTotalShares
			$Sheet.Cells.Item($datarow4007, 4) = [string]$splsShareHolderTotalSharesPercentage
 
			$datarow4007++
 
		}elseif ($splsProcessTemplate -like "1008 payout.xlsx"){
			Write-host $(__LINE__) "Adding shareholder $splsShareHolderName information to 1008 payout"
 
			# Add data for each shareholder 
			if ($splsShareHolderIsOrganization -eq "y"){
				$splsShareHolderDividendName = $splsShareHolderName
			}else{
				$splsShareHolderDividendName = $splsShareHolderName2
			}
			$Sheet.Cells.Item($datarow4008, 1) = [string]$splsShareHolderDividendName
			$Sheet.Cells.Item($datarow4008, 2) = [string]$splsShareHolderBankAccount
			$Sheet.Cells.Item($datarow4008, 3) = [string]$splsShareHolderCreditorNumber
			$Sheet.Cells.Item($datarow4008, 4) = [string]$splsShareHolderTotalShares
			$Sheet.Cells.Item($datarow4008, 5) = [string]$splsShareHolderTotalSharesPercentage
			$Sheet.Cells.Item($datarow4008, 6) = [string]$splsInfoNominalValue
			$Sheet.Cells.Item($datarow4008, 7) = [string]$splsInfoDividend
			$Sheet.Cells.Item($datarow4008, 8) = [string]$splsShareHolderGrossDividendTemp
			$Sheet.Cells.Item($datarow4008, 9) = [string]$splsInfoTaxRate
			$Sheet.Cells.Item($datarow4008, 10) = [string]$splsShareHolderDividendTaxTemp2
			$Sheet.Cells.Item($datarow4008, 11) = [string]$splsShareHolderNetDividendTemp
			$Sheet.Cells.Item($datarow4008, 12) = [string]$splsShareHolderDivTaxApplicable
 
			$datarow4008++
 
		}elseif ($splsProcessTemplate -eq "1006 AddressLabels.docx"){
			Write-host $(__LINE__) "Adding shareholder $splsShareHolderName information to table in 1006 AddressLabels document"
			if ($splsShareHolderIsOrganization -eq "y"){
				$Label = "$splsShareHolderName `n"
				if (-not ([string]::IsNullOrEmpty($splsShareHolderName2))){
				$Label += "$splsShareHolderName2 `n"
				}
			}else{
				$Label = "$splsShareHolderName2 `n"
			}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderName3))){
				$label += "$splsShareHolderName3 `n"
			}
			$Label += "$splsShareHolderAddress `n"
			$Label += "$splsShareHolderPostalCodeCity `n"
			if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
				$Label += "$splsShareHolderCountry"
			}
			$Table.Cell($LabelRow,$LabelColumn).Height = 105  # points
			$Table.Cell($LabelRow,$LabelColumn).Width  = 298  # points
			$Table.Cell($LabelRow,$LabelColumn).VerticalAlignment = 1 # Centered
			$Table.Cell($LabelRow,$LabelColumn).Range.Text = $Label
 
			$LabelColumn += 1
			# Go to next row after column 2 
			if ($LabelColumn -eq 3) { $LabelColumn = 1 ; $LabelRow ++ }
 
		}elseif ($testmodeteller -lt $testmodecount){
			# In testmodus we only create $testmodecount letters. 
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){$testmodeteller ++}
 
			# Create Word Letters 
			$SourceTemplate = $splstemplates + $splsProcessTemplate
			$simpletemplatename = ($splsProcessTemplate).ToString().Replace(".docx","")
			$shareholderfilename = $splsShareHolderName -replace $pattern,$replacechar
			$shareholderfilenamelimited = $shareholderfilename.substring(0, [System.Math]::Min($filenamelength, $shareholderfilename.Length))
			$targetLetterTemp = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/Temp/" + $simpletemplatename + "-" + $shareholderfilenamelimited + "-temp.docx"
			$targetLetter = $splssite + $splstargetdir + "/" + $splsProcessTitle + "/" + $simpletemplatename + "-" + $shareholderfilenamelimited + ".docx"
 
			# Copy the Shareholder Letter to SP
			Write-host $(__LINE__) "Creating temporary file $targetLetterTemp from $SourceTemplate"
			Copy-SPOFile $SourceTemplate $targetLetterTemp
 
			# Open Word Document 
			Write-host $(__LINE__) "Open Word with $targetLetterTemp"
			#start-sleep 30 - use when testing SharePoint Online responsiveness
			$doc = $app.Documents.Open($targetLetterTemp)
 
			# Using Word Advanced Properties 
			$binding = "System.Reflection.BindingFlags" -as [type]
			$props = $doc.CustomDocumentProperties
 
			# Map spls Process List values to Word Advanced Properties. All Advanced Properties have to be splsilable in the Word Template. 
			Write-host $(__LINE__) "Map spls Process List values to Word Advanced Properties."
			# Map Word: PrintDatum to $splsProcessPrintDatum
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessPrintDatum"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "PrintDatum")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessPrintDatum)
			# Map Word: Nummer_jaarvergadering to $splsProcessNummerJaarvergadering
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessNummerJaarvergadering"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Nummer_jaarvergadering")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessNummerJaarvergadering)
			# Map Word: Dag_datum_jaar to $splsProcessDagDatumJaar
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDagDatumJaar"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dag_datum_jaar")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDagDatumJaar)
			# Map Word: Dag_datum_lang to $splsProcessDagDatumLang
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDagDatumLang"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dag_datum_lang")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDagDatumLang)
			# Map Word: Dag_datum_medium to $splsProcessDagDatumMedium
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDagDatumMedium"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dag_datum_medium")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDagDatumMedium)
			# Map Word: Dag_datum_kort to $splsProcessDagDatumKort
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDagDatumKort"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dag_datum_kort")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDagDatumKort)
			# Map Word: Opening_jaarvergadering to $splsProcessOpeningJaarvergadering 
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessOpeningJaarvergadering"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Opening_jaarvergadering")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessOpeningJaarvergadering)
			# Map Word: Start_jaarvergadering to $splsProcessStartJaarvergadering
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessStartJaarvergadering"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Start_jaarvergadering")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessStartJaarvergadering)
			# Map Word: Einde_Bspls to $splsProcessEindeJaarvergadering
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessEindeJaarvergadering"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Einde_Bspls")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessEindeJaarvergadering)
			# Map Word: Voorgaande_jaar to $splsProcessVoorgaandeJaar 
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessVoorgaandeJaar"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Voorgaande_jaar")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessVoorgaandeJaar)
			# Map Word: Uiterste_aanleverdatum_medium to $splsProcessUitersteAanleverDatumMedium
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessUitersteAanleverDatumMedium"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Uiterste_aanleverdatum_medium")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessUitersteAanleverDatumMedium)
			# Map Word: Uiterste_aanleverdatum_kort to $splsProcessUitersteAanleverDatumKort
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessUitersteAanleverDatumKort"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Uiterste_aanleverdatum_kort")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessUitersteAanleverDatumKort)
			# Map Word: Agenda_ontvangst_datum to $splsProcessUitersteAgendaOntvangstDatum
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessAgendaOntvangstDatum"}
			$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Agenda_ontvangst_datum")
			[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessAgendaOntvangstDatum)
 
			# Map spls Shareholder Address values to Word Advanced Properties. All Advanced Properties have to be splsilable in the Word Template. Not all Address information is splsilable, different formats apply to organizations. Each letter has a maximum of 6 Address Lines. 
			Write-host $(__LINE__) "Map spls Shareholder Address values to Word Advanced Properties."
			# Name
			# Name2 - can be empty for organizations. Is used as primary name for non-organizations. If empty, then Name3 is empty as well. 
			# Name3 - can be empty
			# Address 
			# Postal Code and City
			# Country - can be empty 
 
			# Organizations
			if ($splsShareHolderIsOrganization -eq "y"){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress1")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName )
				if ((-not ([string]::IsNullOrEmpty($splsShareHolderName2))) -and (-not ([string]::IsNullOrEmpty($splsShareHolderName3)))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName2 )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName3 )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
					if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
					}else{
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					}
				}elseif (([string]::IsNullOrEmpty($splsShareHolderName2)) -and ([string]::IsNullOrEmpty($splsShareHolderName3))){
				Write-host $(__LINE__) "Address field name2 and name3 are empty. Mapping all other Address properties. "
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
				if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
				}else{
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}else{
					Write-host $(__LINE__) "Either name2 or name 3 is empty, but not both. Due to dataset it is safe to assume that Name 3 is empty. Mapping all other Address properties. "
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName2 )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
					# Always checking country separately 
					if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
					}else{
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					}
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
			}else{
			# Non Organizations 
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress1")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName2 )
				if (-not ([string]::IsNullOrEmpty($splsShareHolderName3))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName3 )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
					# Always checking country separately 
					if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
					}else{
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					}
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}else{
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
					# Always checking country separately 
					if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
					}else{
						$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress4")
						[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					}
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress5")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress6")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
			}
 
			# In template 4004 only the name parts are shown, so address label 1,2,3 are shown, but should only list name information. 
			if ($splsProcessTemplate -eq "4004 wijzigingsformulier.docx"){
				if (($splsShareHolderIsOrganization -eq "y") -and ([string]::IsNullOrEmpty($splsShareHolderName2))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
				if (($splsShareHolderIsOrganization -eq "y") -and ([string]::IsNullOrEmpty($splsShareHolderName3))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
				if (($splsShareHolderIsOrganization -eq "n") -and ([string]::IsNullOrEmpty($splsShareHolderName3))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress2")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
				if (($splsShareHolderIsOrganization -eq "n") -and (-not ([string]::IsNullOrEmpty($splsShareHolderName3)))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "ToAddress3")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
				}
			}
 
			# Map other spls Shareholder values to Word Advanced Properties. All Advanced Properties have to be splsilable in the Word Template.  
			Write-host $(__LINE__) "Map other spls Shareholder values to Word Advanced Properties."
 
			# Map Word: Name to $splsShareHolderName
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderName"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderName))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Name")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName )
			}
			# Map Word: Name2 to $splsShareHolderName2
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderName2"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderName2))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Name2")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName2 )
			}
			# Map Word: Name3 to $splsShareHolderName3
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderName3"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderName3))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Name3")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName3 )
			}else{
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Name3")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
			}
			# Map Word: Address to $splsShareHolderAddress
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderAddress"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderAddress))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Address")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderAddress )
			}
			# Map Word: Postal_code to $splsShareHolderPostalCodeCity
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderPostalCodeCity"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderPostalCodeCity))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Postal_code")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderPostalCodeCity )
			}
			# Map Word: Country to $splsShareHolderCountry
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderCountry"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderCountry))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Country")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCountry )
			}else{
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Country")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, "" )
			}
			# Map Word: Salutation $splsShareHolderSalutation
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderSalutation"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderSalutation))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Salutation")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderSalutation )
			}
			# Map Word: Name4 $splsShareHolderName4
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderName4"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderName4))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Name4")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderName4 )
			}
			# Map Word: BankAccount $splsShareHolderBankAccount
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderBankAccount"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderBankAccount))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "BankAccount")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderBankAccount )
			}
			# Map Word: TelephoneNumber $splsShareHolderTelephoneNumber
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderTelephoneNumber"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderTelephoneNumber))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "TelephoneNumber")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderTelephoneNumber )
			}
			# Map Word: CreditorNumber $splsShareHolderCreditorNumber
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderCreditorNumber"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderCreditorNumber))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "CreditorNumber")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderCreditorNumber )
			}
			# Map Word: DivTaxApplicable $splsShareHolderDivTaxApplicable
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderDivTaxApplicable"}
			if (-not ([string]::IsNullOrEmpty($splsShareHolderDivTaxApplicable))){
				$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "DivTaxApplicable")
				[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderDivTaxApplicable )
			}
 
			# Map spls Dividend values to Word Advanced Properties. Only applicable if the template requires this (4005)			
			if ($splsProcessTemplate -like "4005*"){
				Write-host $(__LINE__) "Map spls Dividend values to Word Advanced Properties."
				# Map Word: NominalValue $splsInfoNominalValue
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsInfoNominalValue"}
				if (-not ([string]::IsNullOrEmpty($splsInfoNominalValue))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "NominalValue")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsInfoNominalValue )
				}
				# Map Word: Dividend $splsInfoDividend
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsInfoDividend"}
				if (-not ([string]::IsNullOrEmpty($splsInfoDividend))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dividend")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsInfoDividend )
				}
				# Map Word: Gross_Dividend $splsShareHolderGrossDividend
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderGrossDividend"}
				if (-not ([string]::IsNullOrEmpty($splsShareHolderGrossDividend))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Gross_Dividend")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderGrossDividend )
				}
				# Map Word: TotalShares $splsShareHolderTotalShares
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderTotalShares"}
				if (-not ([string]::IsNullOrEmpty($splsShareHolderTotalShares))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "TotalShares")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderTotalShares )
				}
				# Map Word: Dividend_Tax $splsShareHolderDividendTax
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderDividendTax"}
				if (-not ([string]::IsNullOrEmpty($splsShareHolderDividendTax))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dividend_Tax")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderDividendTax )
				}
				# Map Word: Net_Dividend $splsShareHolderNetDividend
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsShareHolderNetDividend"}
				if (-not ([string]::IsNullOrEmpty($splsShareHolderNetDividend))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Net_Dividend")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsShareHolderNetDividend )
				}
				# Map Word: Dividend_Reaction_Date $splsProcessDividendDayReactionDate
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDividendDayReactionDate"}
				if (-not ([string]::IsNullOrEmpty($splsProcessDividendDayReactionDate))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dividend_Reaction_Date")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDividendDayReactionDate )
				}
				# Map Word: Dividend_Reaction_Time $splsProcessDividendDayReactionTime
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDividendDayReactionTime"}
				if (-not ([string]::IsNullOrEmpty($splsProcessDividendDayReactionTime))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dividend_Reaction_Time")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDividendDayReactionTime )
				}
				# Map Word: Dividend_Day $splsProcessDividendDay
				if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){Write-host $(__LINE__) "mapping $splsProcessDividendDay"}
				if (-not ([string]::IsNullOrEmpty($splsProcessDividendDay))){
					$prop = [System.__ComObject].InvokeMember("Item", $binding::GetProperty, $null, $props, "Dividend_Day")
					[System.__ComObject].InvokeMember("Value", $binding::SetProperty, $null, $prop, $splsProcessDividendDay )
				}
			}
 
			# Update the Fields in the Word Document with the Values
			$doc.Fields.Update() | Out-Null
 
			# Save the temporary document as the final document 
			Write-host $(__LINE__) "Saving the Shareholder $splsShareHolderName letter."
			$doc.Saved = $false
			$doc.SaveAs($targetLetter)
			# Wait for the document to save
			start-sleep 3
 
			########## Print the Letter ##########
			# Print document, add local printer by IP address, set as default. G4: 25.25.25.241; G2: 25.25.25.221
			try{
				if (($splsProcessPrint -eq "Print") -or ($splsProcessPrint -eq "Test Print")){
					if ((get-printer | select name,printerstatus | where {$_.Name -like "G2*"}).PrinterStatus -eq "Normal"){
						Write-host $(__LINE__) "Printing the Shareholder $splsShareHolderName with ID $splsShareHolderID letter."
						$doc.PrintOut()
						start-sleep 10
					}else{
						Write-host $(__LINE__) "Printer is not available for $splsShareHolderName with ID $splsShareHolderID letter."
						## Add Printer status to logfile
						Get-printer | select *
						$message = "$(__LINE__) $date - Printer is not available for $splsShareHolderName with ID $splsShareHolderID letter. The file will still be created in the folder. Please print it manually. Consult the log for more details. "
						$message | Out-File $globallog -append
						$info += "$message <br>"
						$sendlogfile = $true
					}
				}
			}
			catch{
				Write-host $(__LINE__) "$($_.Exception.Message)" -foregroundcolor red
				# Append message to global logfile and email 
				$message = "$(__LINE__) $date - something went wrong while printing $splsShareHolderName with ID $splsShareHolderID. Please consult transcript log"
				$message | Out-File $globallog -append
				$info += "$message <br>"
				$sendlogfile = $true
			}
 
			# Close the document
			Write-host $(__LINE__) "Closing the Shareholder $splsShareHolderName letter."
			$doc.close()
			start-sleep 1
		}else{
			Write-host $(__LINE__) "Got to $testmodeteller shareholders, ignoring all others because we're in test mode"
		}
	}
	# Save the excel file 
	if ($splsProcessTemplate -like "*.xlsx"){
		Write-host $(__LINE__) "Save excel file $targetLetterxlsx"
		$Sheet.SaveAs($targetLetterxlsx)
		start-sleep 3
		$workfile.Close()	
	}
	#Save the labelfile
	if ($splsProcessTemplate -eq "1006 AddressLabels.docx"){
		Write-host $(__LINE__) "Save label document $targetLetterLabel"
		$Labeldoc.Saved = $false
		$labeldoc.SaveAs($targetLetterLabel)
		# Wait for the document to save
		start-sleep 3
	}
 
	########## Update sharepoint list item in spls Process - Set to Done ##########
	try{
		$listname = $splsprocesslist
		$lists = $Context.web.Lists
		$list = $lists.GetByTitle($ListName)
		$splsProcessItem = $list.GetItemById($splsProcessID)
		$splsProcessItem["Processed"] = "Done"
		$splsProcessItem.Update()
		$Context.load($listItems)
		$Context.executeQuery()
		Write-host $(__LINE__) "Sharepoint list $listname for ID $splsProcessID was set to done. Everything was processed." 
	}
	catch{
		Write-host $(__LINE__) "$($_.Exception.Message)" -foregroundcolor red
		# Append prove of running to global logfile
		$message = "$(__LINE__) $date - something went wrong while updating sharepoint list $listname for ID $splsProcessID and title $splsProcessTitle. Everything else probably went fine. Check the log."
		$message | Out-File $globallog -append
		$info += "$message <br>"
		$sendlogfile = $true
	}
 
}
catch{
	Write-host $(__LINE__) "$($_.Exception.Message)" -foregroundcolor red
	# Stop transcript  
	Stop-transcript
	# Append prove of running to global logfile
	$message = "$(__LINE__) $date - something went wrong while creating the ShareHolders letters. Please consult transcript log"
	$message | Out-File $globallog -append
	$info += "$message <br>"
	$sendlogfile = $true
	Send-Email $subject $info
	exit 2
}
 
########################################## Start Fase 6 ###########################################
 
# Close Word
if ($splsProcessTemplate -like  "*.docx"){
	Write-host $(__LINE__) "Quit Word"
	$app.quit()
	$app = $null
	[gc]::collect()
	[gc]::WaitForPendingFinalizers()
}
 
# Close Excel
if ($splsProcessTemplate -like  "*.xlsx"){
	Write-host $(__LINE__) "Quit Excel"
	$Excel.Quit()
	[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
	[gc]::collect()
	[gc]::WaitForPendingFinalizers()
}
 
########################################## Start Fase 7 ###########################################
 
# Send email report with end date 
$enddate = (Get-Date).ToString('yyyyMMdd-HHmm')
$info += "$enddate - Finished with creating the letters for $splsProcessTitle <br>"
Send-Email $subject $info
 
########################################## Start Fase 8 ###########################################
 
# Cleanup the temp letters
# Files are most of the times locked. Sometimes for more than a couple of hours. Only waiting for 2 minutes here, so you should regularly for files in temp dirs.  					
if (($splsProcessTemplate -like "*.docx") -and ($splsProcessTemplate -ne "1006 AddressLabels.docx")){		
	start-sleep 120 
	$testmodeteller = 0
	foreach($ShareHolder in $listItemsSH){
		if (($ShareHolder["IsActive"] -eq "y") -and ($testmodeteller -lt $testmodecount)){
			# Update testmodeteller if testmodus is set
			if (($splsProcessPrint -eq "Test") -or ($splsProcessPrint -eq "Test Print")){$testmodeteller ++}
			$splsShareHolderName = $ShareHolder["Name"]
			# Removal of temp file needs a relative url
			$targetLetterTempRelative = $splssiterelative + $splstargetdir + "/" + $splsProcessTitle + "/Temp/" + $simpletemplatename + "-" + $splsShareHolderName + "-temp.docx"	
			Write-host $(__LINE__) "Removing the temporary Shareholder $splsShareHolderName letter: $targetLetterTempRelative."
			Remove-SPOFile $targetLetterTempRelative	
		}
	}
}
 
# Stop logging 
Stop-transcript

Resources

Discussion

Enter your comment. Wiki syntax is allowed:
H P A J J
 
spls.txt · Last modified: 2019/11/21 09:50 by sjoerd