$xl = New-Object -ComObject "Excel.Application"

$cmdbwb = $xl.Workbooks.Open("F:\Ivan\HZCMDB.xlsx")

$cmdbws = $cmdbwb.Worksheets.Item("02--Laptop")

$row = Read-Host -Prompt "请输入Excel最左侧的行标"

$details = [pscustomobject][ordered]@{

EmployeeNo = $cmdbws.range("A$row").text

EmployeeName = $cmdbws.range("B$row").text

SeatNo = $cmdbws.range("C$row").text

Project = $cmdbws.range("D$row").text

Model = $cmdbws.range("E$row").text

ServiceTag = $cmdbws.range("F$row").text

FinanceNo = $cmdbws.range("G$row").text

RAM = $cmdbws.range("H$row").text

Hostname = $cmdbws.range("I$row").text

}

$letterwb = $xl.Workbooks.Open("F:\Laptop Issue letter\template.xlsx")

$letterws = $letterwb.Worksheets.Item("sheet1")

$letterws.Range("B1").Value2 = (Get-Date -Format yyyy-M-dd) #日期

$letterws.Range("B2").value2 = $details.EmployeeName #姓名

$letterws.Range("D9").value2 = $details.Model.Split(" ")[0] #型号

$letterws.Range("F9").value2 = $details.ServiceTag #序列号

$letterws.Range("H9").value2 = $details.FinanceNo #资产编号

$letterwb.SaveAs("F:\Laptop Issue letter\Laptop Issue_" + $details.EmployeeName + " (" + (Get-Date -Format yyyy-M-dd) + ").xlsx")

$xl.Quit()

$letterws = $null

$letterwb = $null

$cmdbws = $null

$cmdbwb = $null

$xl = $null

[GC]::Collect()

#[System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)

#Remove-Variable xl

$mail = New-Item -Path 'F:\Laptop Issue letter\mail.txt' -ItemType file -Force

$firstline = 'Dear ' + $details.EmployeeName + ','

$secondline = 'You are hereby being issued a Laptop having the Service tag ' + $details.ServiceTag + ' and Finance asset no ' + $details.FinanceNo + ' for official purpose.'

$thirdline = 'Pls print following attachment 3 copies and sign then submit to IS and Admin for tracking, one copy is kept by yourself. Admin will inform security guard to allow you take the laptop in and out office.'

Add-Content -Path $mail -Value $firstline

[Environment]::NewLine | Out-File $mail -Encoding ascii -Append

Add-Content -Path $mail -Value $secondline

[Environment]::NewLine | Out-File $mail -Encoding ascii -Append

Add-Content -Path $mail -Value $thirdline

[Environment]::NewLine | Out-File $mail -Encoding ascii -Append

Add-Content -Path $mail -Value 'yours,'