troubleshooting Question

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

Avatar of Mandy_
Mandy_ asked on
ExchangeMicrosoft ExcelPowershell
4 Comments1 Solution952 ViewsLast Modified:
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

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

Example of Excel data
"CXK" = ''
"LZA" = ''
"TSX" = ''

$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])}
Import-Csv c:\import1.csv | ForEach `

$ -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[$]
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
Else {
$newSMTP = "{0}{1}{2}.{3}{4}@{5}" -f $first,$middle,$middle2,$last,$code,$company
$mbox = Get-Mailbox $_.UserID
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"

appreaciate so much for all your help
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros