Powershell - Create SMTP getting data from excel cells and writing back smtp to excel cell

hi folks,

I've a running code to create SMTP Adresses getting Firstname.lastname from CSV,
getting Letter-code from CSV and compare with hash-table to build a smtp adress
like firstname.lastname.extensions@domain.com.

The activities show me that it would be much better to handle this directly with
a excel worksheet. That means getting the data from excel worksheet called "ED"
and filename test.xlsm instead of the CSV.

The Data for the name getting from cells B2-B50  (see picture)
The Data for the company code getting from Cells C2-C50   (see picture)
The finished smtp-adresses should write back to Column SMTP from cell J2-J50



Hash-table

"CXK" = 'cxk.com'
"LZA" = 'lza.com'
"TSX" = 'tsx.com'

Select allOpen in new window

$data = $null
$data = @{}
Select-String c:\hash2.txt -Pattern "([a-z]+).+?=.+?([a-z.]+)" |
ForEach { $_.matches} |
ForEach { $tokens = @($_.groups[1].value,$_.groups[2].value); $data.Add($tokens[0],$tokens[1])}
 #$data
Import-Csv c:\import1.csv | ForEach `
{

$_.name.ToLower() -match "^(?<last>[a-z -]+),( (?<middle>[a-z]+))? (?<first>[a-z-]+)( (?<code>[a-z0-9()]+))?$" | Out-Null
$first = $matches["first"] -replace "[^a-z]","."
$middle = $matches["middle"] -replace "[^a-z]","."
$middle2 = $matches["middle"] -replace "[^a-z]","."
$last = $matches["last"] -replace "[^a-z]","."
$code = $matches["code"] -replace "[()]"
$company = $data[$_.company]
If ($middle)
{ $middle = "." + $middle }
If ($code)
{ $code = "." + $code }
    
    
If ($_.UserId -like "U1*" -or $_.UserId -like "U8*" -and $_.Company -like "LSY*") {
$newSMTP = "{0}.{1}.{2}@{3}" -f $first,$last,"sp",$company 
#$newSMTP = "{0}{1}{2}.{3}{4}@{5}" -f $first,$middle,$middle2,$last,$code,$sp,$company
$mbox = Get-Mailbox $_.UserID
$mbox
}
Else {
$newSMTP = "{0}{1}{2}.{3}{4}@{5}" -f $first,$middle,$middle2,$last,$code,$company
$mbox = Get-Mailbox $_.UserID
$mbox
}   
    
If ($mbox.PrimarySMTPAddress -ne $newSMTP)
{
Write-Host  -ForegroundColor Green "Setting primary SMTP $newsmtp address for $mbox"
$newsmtp | out-file -filepath c:\smtp4.csv -append


#set-mailbox -identity $_.UserID -PrimarySmtpAddress $newsmtp -EmailAddressPolicyEnabled $false
#} else {
#Write-Host -ForegroundColor red -backgroundcolor yellow "$mbox has an SMTP-Address already exists in system"
#}
}

Select allOpen in new window

appreaciate so much for all your help