mrgunston mrgunston - 3 months ago 26
HTML Question

Is it possible to use sql variables in HTML email when using sp_send_dbmail?

I am sending an email using sp_send_dbmail on MSSQL server 2012 using HTML format.

This works perfectly however, I want to be able to insert an SQL variable in the HTML email body content. For example, I would expect the code to look as per below however, for some unknown reason, inserting the variable seems to break the HTML code. I suspect that this is happening because when the variable is inserted, all remain HTML tags are automatically closed.

DECLARE @email_body1 NVARCHAR(max);
DECLARE @email_body2 NVARCHAR(max);
DECLARE @fundraising_link NVARCHAR(max);
DECLARE @firstname NVARCHAR(max);
DECLARE @email_leadid INT;
DECLARE @send_email CHAR(5);
DECLARE @email_address NVARCHAR(max);
DECLARE @segment NVARCHAR(max);
DECLARE @variable VARCHAR(max);
SET @variable = '''Paul''';
SET @email_body1 =

'

<!DOCTYPE html><html><head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="viewport" content="width=device-width">
</head>
<body>
Hello ' + @variable + ' ....... REST OF BODY CONTENT.
</body></html>

';

SET @email_body2 = @email_body1

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@domain.com',
@blind_copy_recipients = 'email@domain.com',
@subject = 'email subject'
@body = @email_body2,
@body_format = 'HTML',
@profile_name = 'HTML EMAIL';


Am I doing something wrong? Or is there an easiser way to achieve what I need?

Answer
SET @email_body1 = "Hello <Customer>, Rest of body"

Then

SET @email_body1 = REPLACE(@email_body1, '<Customer>', @CustomerName)

where @CustomerName is NVARCHAR variable with customer name

Note that tag should be unique, e.g.

Alternative solution (as mentioned in another answer) - just use "+"

Comments