RS: DataDriven Subs: How to insert line feed into email body
-
Hi,
I have created a datadriven subscription to a report that I have and everything works fine...except one thing!!!!
When I create the email body string that I am passing in, I include CHAR(13) and CHAR(10) to 'format' the email body. However, when I receive the email, the body of the email arrives as one big block of text.. no formatting.
In Mgmt Studio if I PRINT the result, it is formatted correctly... however if I SELECT the result, again it returns one string...frustrating!
This is roughly what I have in my proc...
declare @email_body nvarchar(4000)
set @email_body = @email_body + N' Test String Line 1' + CHAR(13);
set
@email_body = @email_body + N' Test String Line 2';
Resultset is:
Test String Line 1Test String Line 2
when what I require is
Test String Line 1
Test String Line 2
Would appreciate any help/suggestions anybody could give.
Thanks
Gary.
-
I am using MS Outlook 2003, SP1.
Do you know what settings I should check? Outlook isnt my strong point.
Thanks Again
Gary.
-
You could try putting <br> in the string instead. The email may be being sent as HTML.
-
Almost certain it is not being sent by html... tried adding the <br> but the proc didnt like it!!
-
What error did you get with the <br>? Maybe you just have to use well-formed HTML (ie <br/>
.Is the report being delivered as embedded HTML or as an attachment to the e-mail (the rendering method for the report may determine the rendering for the rest of the body as well)?
>L<
-
Below is roughly the script I have... I dont replacing the CHAR(13) function with </br> is going to do the trick???
Any suggestions???
declare
test_cur cursor for
select
a.testname,a.reportemail from test_db a
inner
join testb_db b
on
a.testid = b.testid
where
b.isactive = 1
open
test_cur
declare
@testname varchar(100)
declare
@reportemail varchar(100)
declare
@email_body nvarchar(4000)
declare
@subject varchar(4000)
fetch
next from test_cur into @testname,@reportemail
while
@@fetch_status=0
begin
set
@email_body = 'Please find attached your script for ' + datename(mm,dateadd(mm,-1,getdate())) + ' - ' + datename(yy,dateadd(mm,-1,getdate())) + '.'+ CHAR(13);
set
@email_body = @email_body + N'If you have any queries in relation to this, please contact me.' + CHAR(13);
set
@email_body = @email_body + N'Kind regards, ' + CHAR(13) + CHAR(10);
set
@email_body = @email_body + N'Joe Bloggs' + CHAR(13);
set
@email_body = @email_body + N'Direct Line: + 11111111' + CHAR(13);
set
@email_body = @email_body + N'joe.bloggs@test.ie';
select
@subject = @testname + ': Test Report ' + datename(mm,dateadd(mm,-1,getdate())) + '-' + datename(yy,dateadd(mm,-1,getdate()))
WAITFOR
DELAY '00:0:30'
exec data_driven_subscription
@scheduleName = 'Test Subscription',
@emailTO = @reportemail,
@emailbody
= @email_body,
@emailReplyTO
@parameterName
= 'Tester',
@parameterValue
= @testname,
@sub
= @subject,
@renderFormat
= 'EXCEL',
@exitCode
= '1',
@exitMessage
= '1';
fetch next from test_cur into @testname,@reportemail
end
close
test_cur
deallocate
test_cur
-
Try viewing the email in Text and HTML format and see there is any differece (in Outlook). When you open the email go to Format and the options are there. If the options are greyed-out go to File and select Edit.
If not, try this in your script
set @email_body = N'Kind regards,
Joe Blogg
Direct Line: + 11111111
format the text in your script without the char(13)+10
-
I am still convinced the problem is not with your sql but with outlook, try sending an email to one of your yahoo or hotmail accounts and see if there is any diffence.
when you used the
did you not forget to set the message format to html.
replace the Char(13) with the br thing and try the code below
EXEC sp_send_dbmail @recipients='joe.bloggs@test.ie',
@subject = 'Test Email',
@body = @email_body,
@body_format = 'HTML' ;
-
First of all, I didn't say </br>. I said <br/>.
Second, I see that you have used CHR(13) and CHR(13)+CHR(10) inconsistently, which can be an issue in another RS situations -- although I suspect it is not involved here.
Third, I see that you are using Excel as your report format -- which means (I believe) attached, rather than embedded.
As a test, it might be a good idea to try using HTML to embed the report content if it is not huge, which would definitely mean the report body was HTML, right? Then see what happens when you use <br/>.
Then, what does your configuration file about RenderFormat? or what else (ExtensionSettings class, whatever) is being used to set the render format or rendering handler?
Fourth, you didn't answer my question: what the error was. Is that data_driven_subscription you're executing a custom procedure? It would be setting the renderformat, or have the capability to do so.
Addtl note: if it does turn out that you need to use the literal html elements and you want them out of other output (reporting on the e-mails you sent, or whatever), you can pull them out with a regular expression... just pointing this out for later when this gets resolved.
>L<
-
I “was” having the same problem, so did a search and found this thread. After reading it, it made me think about Outlook. Yes, I too am creating a plain text email with the same format issue.
Here is the answer (well it works for me) : Open the email in Outlook. Look at the Format sub-menu. Is “Unwrap Text” check? If so, try un-checking it. 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply