CODEWITHSUNDEEP
arraysemailpowershell
Casousadc
Casousadc

Reputation: 147

Email Formatted Array

I got a script that creates two arrays (each has 1 column and variable number of lines). I want to format these two arrays and e-mail it to an Outlook account. Code and sample data below. 

$Values2 = @(Get-Content *\IdealOutput.csv) 

$OutputLookUp2 = @()
foreach ($Value in $Values2) {
    $OutputLookUp2 += $Excel.WorksheetFunction.VLookup($Value,$range4,3,$false)
}

$Excel.Workbooks.Close()
$Excel.Quit()

$EmailFrom = "[email protected]"  
$EmailTo = "[email protected]"
$EmailBody = "$Values2 $OutputLookup2" 
$EmailSubject = "Test"
$Username = "sample"
$Password = "sample"

$Message = New-Object Net.Mail.MailMessage `
    ($EmailFrom, $EmailTo, $EmailSubject, $EmailBody)
$SMTPClient = New-Object Net.Mail.SmtpClient `
    ("smtp.outlook.com", portnumber) #Port can be changed
$SMTPClient.EnableSsl = $true 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential `
    ($Username, $Password);
$SMTPClient.Send($Message)

Both $OutputLookUp2 and $Values2 are one column with variable number of lines.

Example:

$Outputlookup2 = 
X1 
X2

$Values2 = 
Y1 
Y2

I would like the output to the body of the e-mail to be:

X1 Y1
X2 Y2

And I would like to avoid HTML as it will be sent via text as well.

Upvotes: 0

Views: 58

Answers (1)

Matt
Matt

Reputation: 46710

Assuming my interpretation is correct this seems simple enough. For every $Values2, which is just a line from a text file, find its similar value in the open spreadsheet. You are have the loop that you need. Problem is you are building the item lists independent of each other. 

$Values2 = @(Get-Content *\IdealOutput.csv) 

$OutputLookUp2 = @()
foreach ($Value in $Values2){
    $OutputLookUp2 += "$Value $($Excel.WorksheetFunction.VLookup($Value,$range4,3,$false))"
}

Now $OutputLookUp2 should contain your expected output in array form.

If the array does not work you could also just declare it as a string and the add newlines as you are building it. You will notice the "`r`n" at the end of the string. 

$Values2 = @(Get-Content *\IdealOutput.csv) 

$OutputLookUp2 = ""
foreach ($Value in $Values2){
    $OutputLookUp2 += "$Value $($Excel.WorksheetFunction.VLookup($Value,$range4,3,$false))`r`n"
}

In both example you can just flip the order of the $value and the lookup easy. If you need a header you can add that when you declare $OutputLookUp2.

There is always room for improvement

If you want to take this a little further in the direction that Ansgar Wiechers was eluding to...

$OutputLookUp2 = Get-Content *\IdealOutput.csv | ForEach-Object{
    "$_ $($Excel.WorksheetFunction.VLookup($_,$range4,3,$false))"
} | Out-String

Upvotes: 2

Related Questions