SQL Script to send mail with embedded image

Hello All,

If you are working on SQL then most of the time you think that we should get automated mail in our mailbox on any event/alert.

There is Database mail configuration facility is available in SSMS 2008. If this Database mail configuration is  setup with your SMTP profile account then you will get mails automatically.

If you want to send out mail to your team member or friend using this Database mail facility then you will need below code. Which will make your work easier and simpler. This code imbedd image in your mail too which will make your mail more customized and attractive

DECLARE @tableHTML  NVARCHAR(MAX) ;
    SET @tableHTML =
N'<H1>Going home!!!</H1>'+
N'<img src="2.gif" />';

    EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profilename',  --this is a profile name which is used in Database mail configuration setting
@recipients = 'user@gmail.com',
@subject = 'Sending Mail from SQL Server Test',
@file_attachments =N'C:\Users\Pictures\2.gif',  --this should be location where 2.gif picture is located
@body=@tableHTML,
@body_format = 'HTML';

You have keep 2.gif picture on server c$ or any drive from which you are going to send mail using SQL script.

you can do lot of modification in this content also e.g. you can add friend name after
N'<H1>Going Home!!!</H1> code so that it will look like more customized.

Get back to me if you need any help or any doubt while implementing it.