Issue in sending email with attachment in powershell

Question

function Send-EmailToDBA
{
    param(
        [Parameter(Mandatory = $true)][string]$emailBody,
        [Parameter(Mandatory = $true)][string]$emailSubject
    )

    $EmailFrom = "******@test.com" 
    #$EmailTo = "******@test.com" 
    $EmailTo = "******@test.com" 
    $file = "F:\db_dump\DEFAULT\!DBA\healthchecksscripts\Output\Errors.csv"
    $SMTPServer = "smtp1.test.com" 
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    $msg = new-object Net.Mail.MailMessage($EmailFrom, $EmailTo, $EmailSubject, $Emailbody)
    $attachment = new-object System.Net.Mail.Attachment $file
    $message.Attachments.Add($attachment)

    $msg.IsBodyHTML = $true
    $mailer.send($msg)
    ####Attachment#######

####################################################################

} # end of function

$DiskReport = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\DiskSpace.htm" ) | out-string
$FullBackup = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\ADA_FullBackup_Output.htm" ) | out-string
#$LogBackup = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\ADA_LogBackup_Output.htm" ) | out-string
$DBStatus = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\ADA_DBStatus_Output.htm" ) | out-string
$JobFailure = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\FailedJob24hrs.htm" ) | out-string
$Blocking = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\BlockingReport.htm" ) | out-string
$AgentStatus = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\AgentStatusReport.htm" ) | out-string
#$SQLErrors = (Get-Content "F:\db_dump\DEFAULT\!DBA\DailyChecks\Reports\BlockingReport.htm" ) | out-string

$EmialBodyFinal =  'Disk Space Report    '+ $DiskReport + 'Full Backup Report    '+ $FullBackup + 'Database Status Report    '+ $DBStatus + 'Job Failure Status    ' + $JobFailure + 'Blocking Report     '+$Blocking + 'SQL Agent Status '+$AgentStatus
#$EmialBodyFinal =  'Full Backup Report    '+ $FullBackup +'Log Backup Report     ' + $LogBackup + 'Database Status Report    '+ $DBStatus + 'Job Failure Status    ' + $JobFailure 


$datetime = Get-Date -Format "MM-dd-yyyy" 
$EmailSubject = "DBA Health Checks Report " + $datetime

Send-EmailToDBA -EmailBody $EmialBodyFinal -EmailSubject $EmailSubject 

Hello Team ,

I am facing below error while sending email with attachment in above code , can you please correct the code if possible?

Attachment is located at : F:\db_dump\DEFAULT!DBA\healthchecksscripts\Output\Errors.csv

Error :
You cannot call a method on a null-valued expression. At F:\db_dump\DEFAULT!DBA\healthchecksscripts\Combined Output.ps1:16 char:5 + $message.Attachments.Add($attachment) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull. Process Exit Code 0.

Answer 1

It worked for me as i have modified below code in the script :

$attachment = new-object Net.Mail.Attachment($file)
$msg.Attachments.Add($attachment)

Answer 2

Is there a reason why you're using SMTPClient instead of the PowerShell Send-MailMessage? Your function could be written like this:

function Send-EmailToDBA {
    param(
        [Parameter(Mandatory = $true)][string]$emailBody,
        [Parameter(Mandatory = $true)][string]$emailSubject
    )
    $param =    [PSCustomObject]@{
                    From = "******@test.com"
                    To = "******@test.com"
                    Subject = $emailSubject
                    Body = $emailBody
                    SMTPServer = "smtp1.test.com"
                    Attachments = ,"F:\db_dump\DEFAULT\!DBA\healthchecksscripts\Output\Errors.csv"
                    BodyAsHTML = $true
                }
    SendMailMessage @param
} # end of function