I'm trying to use the SQL Server Management Studio Database Mail to send automated HTML-formatted emails. It works fine when I give it a plain-text body, but as soon as I change it to HTML, the messages are blank. They still send with the proper subject and everything else, but the body isn't there.
Here's the relevant bit of code:
DECLARE @msg varchar(8000); --body of the message in html.
DECLARE @recipient_emails varchar(50); --List of email addresses for everyone who needs to receive this message.
DECLARE @subject_line varchar(100); --Subject line for the email.
DECLARE @num_of_msgs int; --Since some updates need to trigger multiple different emails, this is used to determine how many emails will be sent.
DECLARE @creator varchar(10); --Creator of the opportunity in CRM.
DECLARE @account_id int; --Account number for customer.
DECLARE @customer varchar(50); --Customer from whom the opportunity came.
DECLARE @opty_type varchar(30); --Type of opportunity.
DECLARE @opty_desc varchar(40); --Description of opportunity.
DECLARE @date_created datetime; --Datetime when opportunity was created.
DECLARE @notes varchar(500); --Notes about opportunity.
SELECT
@creator = CREATOR,
@account_id = ACCT_ID,
@opty_type = OPTY_TYPE_ID,
@opty_desc = DESCRIPTION,
@date_created = OPEN_DATE,
@notes = COMMENTS
FROM V_OPPORTUNITY
WHERE ID = @opty_id;
--Uses a CASE statement to determine recipient emails based on what activity has just been completed.
SET @recipient_emails =
CASE @description
WHEN 'Create Opportunity in CRM' THEN 'nate.lovell@aggiemail.usu.edu'
WHEN 'Request NDA' THEN 'Email 2'
WHEN 'Receive Formula' THEN 'Email 3'
WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Email 4'
WHEN 'Issue Ballpark Quote' THEN 'Email 5'
WHEN 'Credit Check' THEN 'Email 6'
ELSE ''
END;
--Uses a CASE statement to determine HTML message based on what activity has just been completed.
SET @msg =
CASE @description
WHEN 'Create Opportunity in CRM' THEN '<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>DLI NEW CRM OPTY EMAIL</title>
<style type="text/css">
body {
margin-left: 10px;
margin-top: 10px;
margin-right: 10px;
margin-bottom: 10px;
text-align: center;
background-color: #61FF51;
}
div#PAGECENTER {
width: 1000px;
margin-left: auto;
margin-right: auto
}
</style>
</head>
<body>
<table width="100%" border="2" cellspacing="5" cellpadding="5">
<tbody>
<tr>
<td colspan="4" align="center" valign="middle" style="font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace; font-weight: bold; font-size: 36px;"><p><!--<img src="/Images/DESLABLGtag3d.png" width="52" height="53" alt=""/>--><span style="font-size: 24px"> NEW OPTY CREATED IN CRM</span></p></td>
</tr>
<tr>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">CREATED BY</td>
<td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@creator+'</td>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">CUSTOMER</td>
<td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">SQL HERE</td>
</tr>
<tr>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">OPTY TYPE</td>
<td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@opty_type+'</td>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">DESCRIPTION</td>
<td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+@opty_desc+'</td>
</tr>
<tr>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">DATE CREATED</td>
<td width="35%" height="40" align="left" valign="middle" bgcolor="#E5E5E5" style="font-size: 16px; font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace;">'+CAST(@date_created AS varchar(20))+'</span></td>
</tr>
<tr>
<td width="15%" height="40" align="right" valign="middle" style="font-weight: bold">NOTES</td>
<td height="40" colspan="3" align="left" valign="middle" bgcolor="#E5E5E5" style="font-family: Consolas, ''Andale Mono'', ''Lucida Console'', ''Lucida Sans Typewriter'', Monaco, ''Courier New'', monospace">'+ISNULL(@notes,'')+'</span></td>
</tr>
</tbody>
</table>
<div id="PAGECENTER"></div>
</body>
</html>'
WHEN 'Request NDA' THEN 'Msg 2'
WHEN 'Receive Formula' THEN 'Msg 3'
WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Msg 4'
WHEN 'Issue Ballpark Quote' THEN 'Msg 5'
WHEN 'Credit Check' THEN 'Msg 6'
ELSE 'DEFAULT MSG'
END;
--Uses a CASE statement to determine subject line based on what activity has just been completed.
SET @subject_line =
CASE @description
WHEN 'Create Opportunity in CRM' THEN 'Subject 1'
WHEN 'Request NDA' THEN 'Subject 2'
WHEN 'Receive Formula' THEN 'Subject 3'
WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 'Subject 4'
WHEN 'Issue Ballpark Quote' THEN 'Subject 5'
WHEN 'Credit Check' THEN 'Subject 6'
ELSE 'DEFAULT SUBJECT'
END;
--Uses a CASE statement to determine number of messages based on what activity has just been completed.
SET @num_of_msgs =
CASE @description
WHEN 'Create Opportunity in CRM' THEN 2
WHEN 'Request NDA' THEN 1
WHEN 'Receive Formula' THEN 1
WHEN 'Receive Product Specs-general lbl claim & pkg spec' THEN 1
WHEN 'Issue Ballpark Quote' THEN 1
WHEN 'Credit Check' THEN 1
ELSE 1
END;
--Sends an email.
EXEC msdb.dbo.sp_send_dbmail @recipients = @recipient_emails, @body = @msg, @body_format = 'HTML', @subject = @subject_line, @profile_name = 'SQLProfile';
NOTES: The only case I'm worried about getting to work right now is the one for 'Create Opportunity in CRM'. Also, several variables used here are declared and/or set earlier in the code, like @opty_id and @description.
The most likely cause for failure is that there is no match to the case expressions. You should have an else clause in all the CASE expressions to be sure they are processed as you intend.
A very close second is that one or more of the columns used for constructing @msg are NULL. This will result in the entire value being NULL.
For instance, if there are no comments, then @Notes might be NULL and the whole @msg will end up being NULL.
You should add:
SELECT @creator, @account_id, @opty_type, @opty_desc, @date_created, @notes
To be sure none of these values are NULL.
You can protect against NULL values when @msg is defined by using COALESCE().