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 =
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="viewport" content="width=device-width">
Hello ' + @variable + ' ....... REST OF BODY CONTENT.
SET @email_body2 = @email_body1
@recipients = 'firstname.lastname@example.org',
@blind_copy_recipients = 'email@example.com',
@subject = 'email subject'
@body = @email_body2,
@body_format = 'HTML',
@profile_name = 'HTML EMAIL';
SET @email_body1 = "Hello <Customer>, Rest of body"
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 "+"