Sql-server – Require to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

database-mailsql-server-2008-r2

I need to send an e-mail from SQL Server in the below format, as requested by the client:

Dear colleague,

See attached file with your current list of high priority ticket notifications.

Following tickets are listed:

* High priority tickets: last article time is at least 7 calendar days in the past.
* Very high priority tickets: last article is at least 3 calendar days in the past.

Please check if the priority of these tickets is still correct.
If so, don’t forget to focus on them.

Thanks in advance for reserving some time to work on them.

As of now I have been using the below format to send short messages:

Declare @BODY VARCHAR(50)
declare @id  varchar
declare @email_id varchar(50)
DECLARE emailCursor CURSOR FOR
SELECT id, email_id FROM email_address
OPEN emailCursor
FETCH NEXT FROM emailCursor INTO @id, @email_id
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @BODY = 'The following ticket'  +  @id  + 'is open' 
    EXEC msdb.dbo.sp_send_dbmail
        @recipients = @email_id,
        @profile_name='Currency Rate Notifier',
        @subject = 'Open tickets',
        @body = @BODY
    FETCH NEXT FROM emailCursor INTO @id, @email_id
END
CLOSE emailCursor
DEALLOCATE emailCursor

Any help would be appreciated.

Best Answer

When calling sp_send_dbmail, you can set @body_format='HTML' and place the HTML source code in the @body parameter.

Important: I'm not sure if there will be a plain-text version of your message for recipients that can't or won't read HTML formatted e-mail.

Like Mark says, you should check the documentation for sp_send_dbmail for more information. Also, if you want the challenge, you could use the FOR XML construct in a SELECT statement to generate an XML document where the elements are named and arranged like HTML tags.

Related Question