CODEWITHSUNDEEP
powershell
cryano
cryano

Reputation: 5

Powershell - Reading CSV File as Individual Lines

I have this powershell script to get some email counts which works fine if I only have one user in the csv file but when I put two or more it reads it all as one line and then I get an invalid SMTP address error. I can't for the life of me figure out how to get it to read each line individually. Could someone take a look at this powershell script and help me out?

 ############ Start Import the Exchange 2010 modules if available, otherwise import 2007.
if (Get-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010 -Registered -ErrorAction SilentlyContinue) {
    Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
} else {
    Add-PSSnapin -Name Microsoft.Exchange.Management.PowerShell.Admin
}

############ Start Variables
[Int] $intSent = $intRec = 0
$emails = Get-Content "C:\Test.csv"
$dt = (get-date).adddays(-1)
$tab2 = @()
$tabInfo = @()
############ End variables

############ Start HTML Style
$head = @'
<style>
body { background-color:#FFFFFF;
       font-family:Tahoma;
       font-size:11pt; }
td, th { border:1px solid black; 
         border-collapse:collapse;
         text-align:center;
         background+color:#e0e0e0;
         width:300px;}
th { color:#ffffff;
     background-color:#20a000;
     text-align:center;}
table, tr, td, th { padding: 1px; margin: 0px }
table { margin-left:15px; }
</style>
'@
############ End HTML Style

############ Start retrieve email address + NB sent/received mails
foreach ($i in $emails) {

$intRec = 0                       #Number of received mails
$intSent = 0                      #Number of sent mails
$intTotalSentInt = 0              #Number of sent internal mails
$intTotalSentExt = 0              #Number of sent external mails
$intTotalRecInt = 0               #Number of received internal mails
$intTotalRecExt = 0               #Number of received external mails
$address = $emails                #Email address
$object = new-object Psobject     #Create the object
$objectInfo = new-object Psobject  #Create the object info

############ Sent mails
Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start $dt -Sender $emails -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach {

    If ($_.Recipients -match "domain.com") {
                $intTotalSentInt++
}

    If ($_.Recipients -notmatch "domain.com") {
                $intTotalSentExt++
    }
}

############ Received mails
Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start $dt -Recipients $emails -EventID DELIVER | ForEach {

    If ($_.Sender -match "domain.com") {
                $intTotalRecInt += [Int] $_.RecipientCount
                } Else {
                # From an external sender
                $intTotalRecExt += [Int] $_.RecipientCount
    }
}

############ Insert address + number of sent/received mails
$object | Add-member -Name "User" -Membertype "Noteproperty" -Value $emails
$object | Add-member -Name "Internal Emails Sent" -Membertype "Noteproperty" -Value $IntTotalSentInt
$object | Add-member -Name "External Emails Sent" -Membertype "Noteproperty" -Value $IntTotalSentExt
$object | Add-member -Name "Internal Emails Received" -Membertype "Noteproperty" -Value $intTotalRecInt
$object | Add-member -Name "External Emails Received" -Membertype "Noteproperty" -Value $intTotalRecExt

$tab2 += $object
}

############ Sort by number of sent emails
$tab2 = $tab2 | Sort-Object Sent -descending

############ ConvertTo-HTML
$body =  $tabInfo | ConvertTo-HTML -head $head
$body += $tab2 | ConvertTo-HTML -head $head

############ Send emails with results
send-mailmessage -to "[email protected]" -from "[email protected]" -subject "Emails Sent and Received from $dt" -body ($body | out-string) -BodyAsHTML -SmtpServer "x.x.x.x"

############ end of Script

Sample CSV file that just contains email addresses

Upvotes: 0

Views: 257

Answers (1)

anto418
anto418

Reputation: 175

Line 45, inside the foreach ($i in $emails), you're doing $address = $emails which puts the entire list of emails in $address. I assume you meant $address = $i ?

Edit: and I missed the one which is probably the problematic one, line 50, you're passing $emails which is an array whereas -Sender takes a string.

Upvotes: 1

Related Questions