Cannot understand why this parameter is returning a null value

Question

This PowerShell script runs a SQL query and populates an Excel spreadsheet, then emails that spreadsheet:

$FileName = "C:\Users\user\Documents\Report.xlsx";
$ConnectionString = "OurConnectionString"
$secpasswd = "emailpassword"
$cred = New-Object System.Management.Automation.PSCredential ("emailaddress", $secpasswd)
$SmtpCred = $cred
$ToAddress = '[email protected]'
$FromAddress = '[email protected]'
$SmtpServer = 'smtp.server.com'
$SmtpPort = '587'
$Subject = 'Report'
$Body = "Here is your report"

$SqlQuery = @"
SELECT TOP 10 * FROM dbo.table
"@;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = $ConnectionString;

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
$DataSetTable = $DataSet.Tables["Table"];

$xlsFile = @($DataSetTable | Export-Excel $FileName -AutoSize)

$SqlConnection.Close()

$Attachment = $xlsFile

$mailparam = @{
    To = $ToAddress
    From = $FromAddress
    Subject = $Subject
    Body = $Body
    Attachments = $Attachment
    SmtpServer = $SmtpServer
    Port = $SmtpPort
    Credential = $SmtpCred
}

Send-MailMessage @mailparam -UseSsl

The error I get when I run this is:

Send-MailMessage : Cannot validate argument on parameter 'Attachments'. The
argument is null, empty, or an element of the argument collection contains a
null value. Supply a collection that does not contain any null values and then
try the command again.
At C:\Users\Desktop\Untitled1.ps1:58 char:18
+ Send-MailMessage @mailparam -UseSsl
+                  ~~~~~~~~~~
     + CategoryInfo          : InvalidData: (:) [Send-MailMessage], ParameterBindingValidationException
     + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.SendMailMessage

I can't figure out how $Attachment is ending up as null or empty? I know I've written scripts similar to this in the past and they worked fine. Am I missing something in my script that someone else can see? Further, I don't technically need to save the Excel file to the file system first, I just need to email it as an attachment. So it's ok if the answer eliminates the saving to the file system part, if that's even do-able.

Lucas Wolfe Lucas Wolfe · Accepted Answer · 2019-11-11T21:23:00

The -Attachments parameter expects a string or list of strings.

see Send-MailMessage

This should be the full path(s) to the file(s) you need to attach, thus requires you to store them first. Generally, if I don't need the files afterwards you can just delete them. Also note that it is required you pass the FullName.

As I cannot see the Output of the Export-Excel function I will assume it returns a File Object then this should work

$mailparam = @{
    To = $ToAddress
    From = $FromAddress
    Subject = $Subject
    Body = $Body
    Attachments = $xlsFile.FullName
    SmtpServer = $SmtpServer
    Port = $SmtpPort
    Credential = $SmtpCred
}

If you are not returned a File object you will need to get the item first and pass the `FullName'

$MyExcelFile = Get-Item -Path 'C:\MyExcelFile.xlsx'

 $mailparam = @{
        To = $ToAddress
        From = $FromAddress
        Subject = $Subject
        Body = $Body
        Attachments = $MyExcelFile.FullName
        SmtpServer = $SmtpServer
        Port = $SmtpPort
        Credential = $SmtpCred
    }

Cannot understand why this parameter is returning a null value

1 Answers