How can I chain Export-CSV to Send-MailMessage without having to save the CSV to disk in PowerShell?

Question

I would like to email a list of AD users as a CSV but I don't want to have to save the CSV to disk before I email it.

Here is the code that gets me the data:

get-aduser -filter * -properties Telephonenumber |
where Telephonenumber -ne $Null | 
select givenname, surname, telephonenumber | 
sort surname

Now I want to add on something like:

| Export-Csv | 
Send-MailMessage -From [email protected] -to [email protected] -subject "Outlook address book export" -SmtpServer EdgeTransport

Is there anyway to add the CSV data as an attachment in memory without saving the file to the file system?

Solution 1

The -Attachments parameter for Send-MailMessage requires an array of paths, so you must write a file to disk somewhere.

To send your CSV data as the body of the email, use convertto-csv -notypeinformation instead of export-csv.

$myData = get-aduser -filter * -properties Telephonenumber | where Telephonenumber -ne $Null | select givenname, surname, telephonenumber | sort surname|convertto-csv -notypeinformation
Send-MailMessage -From [email protected] -to [email protected] -subject "Outlook address book export" -SmtpServer EdgeTransport -body $mydata

Solution 2

The proposed/accepted answer above is incorrect and won't work. The -Body parameter of the Send-MailMessage expects an string object (System.String). The $mydata object created in the example is a System.array object. If you use it as shown above the command will fail.

What you can do is create a file, fill it with the CSV data, email it as an attachment and then delete it.

Using your code above, for example:

$attachment = New-Item filename.csv -ItemType file
get-aduser -filter * -properties Telephonenumber | where Telephonenumber -ne $Null | select givenname, surname, telephonenumber | sort surname | Export-Csv $attachment -NoTypeInformation
Send-MailMessage -Attachments $attachment -Body $SmtpBodyTrue -BodyAsHtml -From $SmtpSender -To $MailTo -Subject $SmtpSubject -SmtpServer $SmtpServer
Remove-Item $attachment
$attachment = $null

Solution 3

I know this is a super old thread, but the above answers are no longer correct. You can send in-memory objects as attachments without writing to a file first. You need to use .net methods, but it works well.

The key is the .net method [System.Net.Mail.Attachment]::CreateAttachmentFromString

http://geekswithblogs.net/mkoerner/archive/2012/01/19/powershell-emailing-strings-as-attachments.aspx

Solution 4

I know this is a very old post but I thought I would give my input after being in a similar position.

The below is a basic function that I have written to create a CSV attachment from an object etc.

One of the main issues with getting this to work was firstly convertto-csv outputs everything in quotes (I know this has been fixed PS6 +, but not useful when using PS5. Secondly when casting the CSV data to a string and converting it to a memory stream it was escaping Carriage Return / Line Feed, to get around this I appended [System.Environment]::NewLine to every row in the CSV.

Function ConvertTo-CSVEmailAttachment {
Param(
    [Parameter(Mandatory=$true)]
    [String]$FileName,
    [Parameter(Mandatory=$true)]
    [Object]$PSObject,
    $Delimiter
    )
    If ($Delimiter -eq $null){$Delimiter = ","}
    $MS = [System.IO.MemoryStream]::new()
    $SW = [System.IO.StreamWriter]::new($MS)
    $SW.Write([String]($PSObject | convertto-csv -NoTypeInformation -Delimiter $Delimiter | % {($_).replace('"','') + [System.Environment]::NewLine}))
    $SW.Flush()
    $MS.Seek(0,"Begin") | Out-Null
    $CT = [System.Net.Mime.ContentType]::new()
    $CT.MediaType = "text/csv"
    Return [System.Net.Mail.Attachment]::new($MS,$FileName,$CT)
}

To use this you would require to have your object ready as their is no pipeline functionality taken into consideration (As I said basic function).

Example:

 $ADList = get-aduser -filter * -properties Telephonenumber | where Telephonenumber -ne $Null | select givenname, surname, telephonenumber |sort surname
 $EmailAttachment = ConvertTo-CSVEmailAttachment -FileName "ADList.CSV" -PSObject $ADList

One of the answers is correct in the sense that Send-MailMessage requires a string to a file path to function.

However you can use the .net library [Net.Mail.MailMessage] to create the email instead of using Send-MailMessage:

$SMTPserver = "This.Will.Be.Your.Email.Server.Endpoint"
$from = "[email protected]"
$to = "[email protected]"
$subject = "This is the subject Line"
$emailbody = "This is the body of the email"

$mailer = new-object Net.Mail.SMTPclient($SMTPserver)
$msg = new-object Net.Mail.MailMessage($from, $to, $subject, $emailbody)
$msg.Attachments.Add($EmailAttachment) #### This uses the attachment made using the function above. 
$msg.IsBodyHTML = $false
$mailer.send($msg)

Anyway I hope this helps someone that is in a similar situation in the future.

View more solutions