Use Microsoft Graph & PowerShell to send Email with CSV Attachments without saving files to disk

The Microsoft Graph API is an incredibly powerful tool at an engineers disposal, combining this with PowerShell via the Graph SDK opens up a world of possibilities.

Prerequisites

• The Microsoft Graph PowerShell SDK.
• A User/Managed Identity/Service Principal with the Mail.Send permission.

I recently ran into a scenario where I needed to perform reporting on an Azure Table and using Send-MgUserMail made the most sense. After using the AzTable PowerShell module to pull the data I required and save it as a PSCustomObject variable, I had the data in a format that can easily be displayed as a table or exported to csv.

I knew I wanted to attach this data to the email I was going to send as a csv, but previously I'd only ever seen that be achieved by first writing the data as a csv to disk using Export-Csv and then reading the raw bytestream back into the script via Get-Content. In my case, this script will be running on a schedule via Azure Automation so I was trying to avoid having to do this if possible.

Enter Convert-Csv and Out-String. The below snippet shows how you can use the pipeline to convert a PSCustomObject to a CSV and then to a raw string which can then be converted into a format that Send-MgUser mail can use to send as an attachment all while never touching the disk.

$ReportString = $TableReport | ConvertTo-Csv | Out-String
$ReportBytes = [System.Text.Encoding]::UTF8.GetBytes($ReportString)
$Reportb64Attachment = [Convert]::ToBase64String($ReportBytes)

Now that you have your Base64 encoded attachment, you need to pass it as a parameter to Send-MgUser mail. Below is an example of how to do this.

# Define the email parameters
$messageParameters = @{
    Message = @{
        ToRecipients = @(
            @{
                emailAddress = @{
                    address = "me@lightningsec.com"
                }
            }
        )
        attachments = @(
            @{
              '@odata.type' = '#microsoft.graph.fileAttachment'
              'name' = "Report-$(Get-Date -Format 'MM/dd/yyyy').csv"
              'contentType' = 'text/plain'
              'contentBytes' = $Reportb64Attachment
            }
          )
        Subject = "Azure Monitoring $(Get-Date -Format 'MM/dd/yyyy')"
        Body = @{
            ContentType = "HTML"
            Content = $content
        }
    }
}
# Send the email
Send-MgUserMail -UserId 'integrations@lightningsec.com' -BodyParameter $messageParameters

This can be extended to send multiple attachments per email, and you can see how I am using Get-Date inline format the attachment filename. In addition to attachments you can also use HTML in the email body to provide a richer reporting experience, but that's a topic for another day. If you don't want to use HTML, change the ContentType to 'Text' and insert a string variable into the Content parameter.