Powershell: Using SendGrid to send mail with attachment

Question

I am trying to send emails with excel attachments as noreply@domain.com. The sending works but the issue is with the attachment.

This is what the script looks like. It works in that it gets send but the attachment gets corrupted. Before this i was using the outlook.application method which works perfectly. Only issue is that I want to hide my email so I don't get replied to. But i cant do that there so I am using sendgrid.

EDIT1: got the initial skeleton code from https://github.com/tsrob50/SendGridFunction/blob/master/SendGridFunction.ps1 The attachment stuff came from u/Pandapokeman sending me here: https://dzone.com/articles/how-to-send-an-email-with-attachement-powershell-sendgrid-api

function Send-SendGridEmail {
  param(
    [Parameter(Mandatory = $true)]
    [String] $destEmailAddress,
    [Parameter(Mandatory = $true)]
    [String] $fromEmailAddress,
    [Parameter(Mandatory = $true)]
    [String] $subject,
    [Parameter(Mandatory = $false)]
    [string]$contentType = 'text/plain',
    [Parameter(Mandatory = $true)]
    [String] $contentBody,

    #Added after edit1
    [parameter(Mandatory = $false)]
    [string]$FileName,
    [parameter(Mandatory = $false)]
    [string]$FileNameWithFilePath,
    [parameter(Mandatory = $false)]
    [string]$AttachementType
  )

  <#
.Synopsis
Function to send email with SendGrid
.Description
A function to send a text or HTML based email
See https://sendgrid.com/docs/API_Reference/api_v3.html for API details
This script provided as-is with no warranty. Test it before you trust it.
www.ciraltos.com
.Parameter apiKey
The SendGrid API key associated with your account
.Parameter destEmailAddress
The destination email address
.Parameter fromEmailAddress
The from email address
.Parameter subject
Email subject
.Parameter type
The content type, values are “text/plain” or “text/html”.  "text/plain" set by default
.Parameter content
The content that you'd like to send
.Example
Send-SendGridEmail
#>
  ############ Update with your SendGrid API Key ####################
  $apiKey = "myKey"

  $headers = @{
    'Authorization' = 'Bearer ' + $apiKey
    'Content-Type'  = 'application/json'
  }
  #Convert File to Base64
  $FileContent = get-content $FileNameWithFilePath
  $ConvertToBytes = [System.Text.Encoding]::UTF8.GetBytes($FileContent)
  $EncodedFile = [System.Convert]::ToBase64String($ConvertToBytes)

  $body = @{
    personalizations = @(
      @{
        to = @(
          @{
            email = $destEmailAddress
          }
        )
      }
    )
    from             = @{
      email = $fromEmailAddress
    }
    subject          = $subject
    content          = @(
      @{
        type  = $contentType
        value = $contentBody
      }
    )
    attachments = @(
        @{
            content=$EncodedFile
            filename=$FileName
            type= $AttachementType
            disposition="attachment"
        }
    )
  }

  try {
    $bodyJson = $body | ConvertTo-Json -Depth 4
  }
  catch {
    $ErrorMessage = $_.Exception.message
    write-error ('Error converting body to json ' + $ErrorMessage)
    Break
  }

  try {
    Invoke-RestMethod -Uri https://api.sendgrid.com/v3/mail/send -Method Post -Headers $headers -Body $bodyJson 
  }
  catch {
    $ErrorMessage = $_.Exception.message
    write-error ('Error with Invoke-RestMethod ' + $ErrorMessage)
    Break
  }

}
$htmlBody = @"
//STUFF
"@
$splat2 = @{
  destEmailAddress = 'dest'
  fromEmailAddress = 'noreply@domain.com'
  subject          = 'Test Email'
  contentType      = 'text/html'
  contentBody      = $htmlBody
  FileName         = "name.xlsx"
  FileNameWithFilePath = "path/name.xlsx"
  AttachementType ="text/xlsx"
}

Someone from reddit suggested using: "The example code you link to uses html files as an example, but you are sending binary files. You will want to get the file's byes using Get-Content -Encoding Byte instead, otherwise you might have issues with newline sequences getting messed up due to Get-Content's default behaviour."

For which i replaced:

$FileContent = get-content $FileNameWithFilePath
with: $FileContent = Get-Content -Encoding Byte $FileNameWithFilePath

Which didn't work either.

EDIT2: Tried:

AttachementType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Still corrupted

Answer 1

Thanks a lot for posting the solution Milan. I was having the same issue and it drove me nuts.

$base64string = [Convert]::ToBase64String([IO.File]::ReadAllBytes($FileNameWithFilePath))

Using the above part instead of the three lines after "#Convert File to Base64" did it for me as well.

I want to mention that "$base64string" replaces the "$EncodedFile" variable in the attachment section in the code above, ie.

attachments = @(
    @{
        content=$base64string 
        filename=$FileName
        type= $AttachementType
        disposition="attachment"
    }

Answer 2

This one liner is what fixes it for me. The file comes in uncorrupted. Along with HAL9256 gave me.

$base64string = [Convert]::ToBase64String([IO.File]::ReadAllBytes($FileNameWithFilePath))
AttachmentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

None of the other encoding is in. its all removed/commented out