troubleshooting Question

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

Avatar of Mandy_
Mandy_ asked on
Microsoft ExcelPowershellExchange
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" = ''

Open 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])}
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"

Open in new window

appreaciate so much for all your help
Meir Rivkin
Full stack Software Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
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