Showing posts with label Send DB Mail using Sql Server. Show all posts
Showing posts with label Send DB Mail using Sql Server. Show all posts

How to send mail from SQL Server?

Below are the steps to send email from a database in Microsoft Sql Server:

Step 1: Get the database default mail profile using the below query, if you don't have a database mail profile create one.

DECLARE @defaultProfileName NVARCHAR(100)=''
SELECT TOP 1 @defaultProfileName=name
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_principalprofile pp ON pp.profile_id = p.profile_id AND pp.is_default = 1

Step 2: Use the below query to send email with the appropriate arguments such as toEmailAddress, ccEmailAddress, bccEmailAddress, subject, mailBody etc.

DECLARE @toEmailAddress NVARCHAR(100), @ccEmailAddress NVARCHAR(100), @bccEmailAddress NVARCHAR(100), @subject NVARCHAR(200), @mailBody NVARCHAR(MAX),@filePath NVARCHAR(MAX)
SET @toEmailAddress = 'abc@gmail.com'
SET @ccEmailAddress = 'def@gmail.com'
SET @bccEmailAddress = 'ghi@gmail.com'
SET @subject = 'test subject'
SET @mailBody = 'body of the email'
SET @filePath = 'C:\Test\abc.pdf'
IF(ISNULL(@defaultProfileName,'')<>'')
BEGIN
     EXEC msdb.dbo.sp_send_dbmail @profile_name=@defaultProfileName,
    @recipients=@toEmailAddress,
    @copy_recipients=@ccEmailAddress,
    @blind_copy_recipients=@bccEmailAddress,
    @subject=@subject,
    @body=@mailBody,
    @body_format = 'HTML',
    @file_attachments=@filePath
END

Followers

Popular Posts