$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,'