none
Import CSV file into email Body RRS feed

  • Question

  • Hi everyone, I am trying to write a script to email using PowerShell. Issue I am not able to figure out is how to Import CSV file in the middle of body text in table format. Any help you can provide is greatly appreciated. Thank you, Asim


    $ol = New-Object -comObject Outlook.Application
    $mail = $ol.CreateItem(0)
    $Mail.Recipients.Add("XXX@YYY.ZZZ")
    $Mail.Subject = "PS1 Script TestMail"
    $Mail.Body = " Some Text before data from CSV

    Import CVS


    Some text after data from CSV

     "
    $Mail.Send()

    Thursday, December 1, 2011 5:48 AM

Answers

  • You cannot include a csv file in the body of an email.  The body must be a string type.

    This might work:

    $mail.body = 'Some text' + (Import-Csv somefile.csv | Out-String)


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    • Edited by Bigteddy Thursday, December 1, 2011 6:08 AM
    • Marked as answer by AsimC Thursday, December 1, 2011 10:23 PM
    Thursday, December 1, 2011 6:06 AM
  • Hello AsimC,

    Suppose you have the CSV file with following content:

    "Name","Grade"
    "John Doe","A+"
    "Jean Doe","B"

    Then you can use following script to send mail. If you are using PowerShell V2, I suggest you using Send-MailMessage cmdlet. Because the security policy of Outlook will not allow you send mail somtimes.

    Of course,the format can be fine tuned. It depends on your needs. Please also replace the parameter value of Send-MailMessage with your own value.

     

    $studentsInfo = Import-Csv -Path D:\Scripts\studens.csv | ConvertTo-Html -Fragment
    $mailBody = 
    @"
    Hello Professor,</br>
    Here are the grades of each student. Please have a look.</br>
    $studentsInfo</br>
    
    Best Regards,</br>
    Anna Smith
    "@
    
    Send-MailMessage -Body $mailBody -BodyAsHtml `
    -From "administrator@corp.contoso.com" -To "administrator@corp.contoso.com" `
    -Subject "Grades of each student" -Encoding $([System.Text.Encoding]::UTF8) `
    -Credential $(Get-Credential) -SmtpServer "127.0.0.1"

     


    Here is the test result:

     


    After I watched the video "Richard St. John's 8 secrets of success" on TED.com, I learned this: "Being good at you job is not enough, you should be damn good at it."


    • Edited by Huajun (Greg) Gu Thursday, December 1, 2011 9:48 AM
    • Proposed as answer by Bigteddy Thursday, December 1, 2011 5:09 PM
    • Marked as answer by AsimC Thursday, December 1, 2011 10:23 PM
    Thursday, December 1, 2011 6:46 AM

All replies

  • You cannot include a csv file in the body of an email.  The body must be a string type.

    This might work:

    $mail.body = 'Some text' + (Import-Csv somefile.csv | Out-String)


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    • Edited by Bigteddy Thursday, December 1, 2011 6:08 AM
    • Marked as answer by AsimC Thursday, December 1, 2011 10:23 PM
    Thursday, December 1, 2011 6:06 AM
  • Normally, we would send the csv file as an attachment to preserve the formatting.
    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    • Marked as answer by AsimC Thursday, December 1, 2011 10:22 PM
    • Unmarked as answer by AsimC Thursday, December 1, 2011 10:23 PM
    Thursday, December 1, 2011 6:12 AM
  • Hello AsimC,

    Suppose you have the CSV file with following content:

    "Name","Grade"
    "John Doe","A+"
    "Jean Doe","B"

    Then you can use following script to send mail. If you are using PowerShell V2, I suggest you using Send-MailMessage cmdlet. Because the security policy of Outlook will not allow you send mail somtimes.

    Of course,the format can be fine tuned. It depends on your needs. Please also replace the parameter value of Send-MailMessage with your own value.

     

    $studentsInfo = Import-Csv -Path D:\Scripts\studens.csv | ConvertTo-Html -Fragment
    $mailBody = 
    @"
    Hello Professor,</br>
    Here are the grades of each student. Please have a look.</br>
    $studentsInfo</br>
    
    Best Regards,</br>
    Anna Smith
    "@
    
    Send-MailMessage -Body $mailBody -BodyAsHtml `
    -From "administrator@corp.contoso.com" -To "administrator@corp.contoso.com" `
    -Subject "Grades of each student" -Encoding $([System.Text.Encoding]::UTF8) `
    -Credential $(Get-Credential) -SmtpServer "127.0.0.1"

     


    Here is the test result:

     


    After I watched the video "Richard St. John's 8 secrets of success" on TED.com, I learned this: "Being good at you job is not enough, you should be damn good at it."


    • Edited by Huajun (Greg) Gu Thursday, December 1, 2011 9:48 AM
    • Proposed as answer by Bigteddy Thursday, December 1, 2011 5:09 PM
    • Marked as answer by AsimC Thursday, December 1, 2011 10:23 PM
    Thursday, December 1, 2011 6:46 AM
  • Bigteddy and Huajun Gu thank you for your help. I am all set with this.

    Asim

    Thursday, December 1, 2011 10:28 PM