none
Can I have both static text and the output of an array in the body of an email? RRS feed

  • Question

  • I have been scouring forums for something on this for a while now and have not found a whole lot. I'm trying to send a group of emails out to server owners in our company for a cleanup/consolidation project. I have a working script and of course now my boss wants it changed. I have the script set up to find all the servers a person owns and put it into a CSV file. Then it attaches it to an email with instructions on how to get everything updated. Now my boss does not want the list in an attachment but right in the body of the email. I'm pretty sure this is something Powershell can do, but I'm clueless on where to begin let alone how that would work.

    Here is my current script minus the confidential stuff :) I just need to know where to start since it can't be in html format. I'd probably know if I knew powershell a little better, but well I've only been working with it for a few months now.

    #Get SQL Database objects and places them into array $Sqldb
    $Sqldb = New-Object System.Data.DataSet "myDataSet"
    $sqlConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=<SQL Server>;Initial Catalog=ServerBook;Integrated Security=True")
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter("SELECT ServerName,Owner,SupportContact,SupportExternal,ServerDescription FROM v_ServerBook_BasicInfo", 
    $sqlConn)
    $adapter.Fill($Sqldb)	
    #reorganizes $Sqldb by property owner
    $Sqldb = $Sqldb.Tables[0] | Sort-Object -Property Owner
    #stores list of unique owners and eliminates duplicates
    $owners = ($Sqldb.owner | Get-Unique) 
    #eliminates null values from the list of owners
    $owners = $owners | where{$_ -ne "" -and $_ -ne $null -and $_ -ne [dbnull]::value} 
    foreach($owner in $owners){
    #Get the servers for the current owner
    $servers = $Sqldb | where{$_.owner -eq $owner}
    #CSV output filename ($owner.csv)
    $newfile = "\\file\path\$owner.csv"
    #Export the servers to the filename
    $servers | select ServerName, Owner, SupportContact, SupportExternal, ServerDescription | Export-Csv -path $newfile -NoTypeInformation 
    #copies the server owners name to $ownername variable
    $ownerName = $owner 
    #switches the format of the ownername variable to lastname,firstname
    $Username = "$($Owner.Split(" ")[1]), $($Owner.Split(" ")[0])"
    #gets email address for owner from AD
    $EmailAddress = Get-ADuser -Filter "Name -eq '$Username'" -properties emailaddress -server ads.domain.com | select-object emailaddress 
    #splits the $emailAddress vaiable to help eliminate extra text original text looks like @{emailaddress = John.Smith@Email.com}
    $Emailaddress = $Emailaddress -split '[=}]' ,0
    #removes extra text at the beginning of the variable leaving just John.Smith@email.com
    $Emailaddress = $Emailaddress.TrimStart("@{emailaddress")
    #stores the body of the email into a variable
    $message = "Hello,
                                     
    Blah Blah Blahbity Blah
    Thank You,
    <Signature Block>"
            
    #Sets the from email address
    $emailFrom = "Jane.Doe@email.com" 
    #Sets the email subject
    $subject="Test Email" 
    #sends the email with attachement for the current owner
    Send-MailMessage -To "$EmailAddress" -Subject $subject -Body $message -SmtpServer "<smtp.domain.com>" -From $emailFrom  -Attachments $newfile -DeliveryNotificationOption OnSuccess
    }

    Friday, June 20, 2014 9:27 PM

Answers

  • Here's a simple example of sending an html email having static content, and dynamic data from objects created using your posted example:

    $DataStrings=
    (@'
    ServerName	Owner	SupportContact	SupportExternal	ServerDescription
    Server1	Me	Me	TrendMicro OfficeScan 10.5
    Server2	Me	Me	TrendMicro Deep Security Test
    Server3	Me	Me	Test Server
    Server4	Me	Me	Application Server
    '@).split("`n") |
    foreach {$_.trim()}
    
    $Data = $DataStrings -replace '\s+',' ' |  ConvertFrom-Csv -Delimiter ' ' 
    
    $EmailBody = 
     @"
       <html>
       <h2> Report Title </h2>
       Opening static content
        <hr>
    
        <body> $($Data | ConvertTo-HTML -As TABLE -Fragment) </body>
    
        <hr>
        Closing static content
      </html>
    "@
    
    $MailParams = 
    @{
     From = 'user@domain.com'
     To   = 'other.user@domain.com'
     Subject='Very Important Email Subject'
     Body = $EmailBody
     BodyAsHTML = $true
     SMTPServer = 'smtpserver@domain.com'
     DeliveryNotificationOption = 'OnSuccess'
     }
    
     Send-MailMessage @MailParams


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "




    Saturday, June 21, 2014 3:46 AM
    Moderator

All replies

  • An easy way to do this is with an expandable here-string:

    $StringArray = 'Server1','Server2','Server3','Server4'
    
    $EmailBody = @"
    This is the static text header of the email body.
    
    $($StringArray | Out-String)
    
    This is the static text footer of the email body.
    "@
    
    $EmailBody
    
    
    
    This is the static text header of the email body.
    
    Server1
    Server2
    Server3
    Server4
    
    
    This is the static text footer of the email body.

    Then just use $EmailBody as the body of your email.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Friday, June 20, 2014 9:39 PM
    Moderator
  • So I can just put my output variable into the email body variable.... since it's a table with headers should I do anything special for formatting the table?

    My output now looks like :

    ServerName Owner SupportContact SupportExternal ServerDescription
    Server1 Me Me TrendMicro OfficeScan 10.5
    Server2 Me Me TrendMicro Deep Security Test
    Server3 Me Me Test Server
    Server4 Me Me Application Server

    Well it's a little garbled there but you get the idea

    • Edited by zwb0069 Friday, June 20, 2014 11:21 PM Left part out
    Friday, June 20, 2014 11:20 PM
  • You can try using:

    $data | format-table -autosize | out-string

    but the proportional fonts the email clients use will probably mess with the alignment some.

    If you want to include tabular data and have it look nice you should probably switch to html.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Saturday, June 21, 2014 12:52 AM
    Moderator
  • Here's a simple example of sending an html email having static content, and dynamic data from objects created using your posted example:

    $DataStrings=
    (@'
    ServerName	Owner	SupportContact	SupportExternal	ServerDescription
    Server1	Me	Me	TrendMicro OfficeScan 10.5
    Server2	Me	Me	TrendMicro Deep Security Test
    Server3	Me	Me	Test Server
    Server4	Me	Me	Application Server
    '@).split("`n") |
    foreach {$_.trim()}
    
    $Data = $DataStrings -replace '\s+',' ' |  ConvertFrom-Csv -Delimiter ' ' 
    
    $EmailBody = 
     @"
       <html>
       <h2> Report Title </h2>
       Opening static content
        <hr>
    
        <body> $($Data | ConvertTo-HTML -As TABLE -Fragment) </body>
    
        <hr>
        Closing static content
      </html>
    "@
    
    $MailParams = 
    @{
     From = 'user@domain.com'
     To   = 'other.user@domain.com'
     Subject='Very Important Email Subject'
     Body = $EmailBody
     BodyAsHTML = $true
     SMTPServer = 'smtpserver@domain.com'
     DeliveryNotificationOption = 'OnSuccess'
     }
    
     Send-MailMessage @MailParams


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "




    Saturday, June 21, 2014 3:46 AM
    Moderator