Introduction
When you create a report in SSRS 2008 and then want to create a Data Driven Subscription to this report and want to deliver it by email you can come to a problems with the message in the email body.
Let’s say we have a test report and create Data Driven Subscription to this report. The query in the subscription will return fields SubscriberEmail and EmailBody. In the SubscriberEmail we will have email address to which the report should be delivered and in the EmailBody we will have a message we want to send in the email (e.g..explanation of the report, how to use it etc.)
SELECT
'somebody@domain.com' AS SubscriberEmail,
N'First Article of Email Body.
Second Article of Email Body.
Third Article of Email Body.' AS EmailBody
The setting could be like below (Comment is email body). Also notice the edit box for the Comment static value.. It’s a small single line TextBox.
A problem occurs when you want the email body to contain several articles separated by new lines. All the new lines in the delivered email will be removed and all the articles will be delivered as a single article.
First Article of Email Body. Second Article of Email Body. Third Article of Email Body.
In contrast, when you are creating a normal subscription the Comment text box is multi line and you will receive a correct multi line email.
There is no way to send a simple text as multi line email with several articles.
Solution
As I mentioned above, you are not able to send a simple test as multi line email with several articles. Although, there is workaround and if the email client is accepting HTML, you can put as the email body a simple HTML source code. Then the email body will be correctly rendered in the email client as multi line text.
SELECT
'somebody@domain.com' AS SubscriberEmail,
N'
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
</head>
<body>
<p>First Article of Email Body.</p>
<p>Second Article of Email Body.<p>
<p>Third Article of Email Body.<p>
</body>
</html>' AS EmailBody
As I was searching over the Internet I found, that people have the same problem also with SSRS 2005. The issue is solved in SSRS 2008 R2 and in the R2 it works correctly even without the HTML.
Pavel's SQL Pages 