How can I send plain text email (with line breaks) using sp_send_dbmail?

Question

I have a SQL Server 2008 procedure that sends email via sp_send_dbmail.

I'm using the following code:

  set @bodyText = ( select 
                      N'Here is one line of text ' +
                      N'It would be nice to have this on a 2nd line ' +
                      N'Below is some data: ' +
                      N' ' +
                      N' ' +
                      field1 +
                      N' ' +
                      field2 +
                      N' ' +
                      N'This is the last line'
                    from myTable )

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'myProfile',
        @recipients = @to,
        @body = @bodyText,
        @body_format = 'TEXT',
        @subject = 'Testing Email' ;

My myProfile is set to use the local smtp server, which results in a .EML file in c:\inetpub\mailroot\queue

When I open one of those .eml files (ug - the only thing that can open them is outlook express, looking at them in anything else just shows the body as a base64 encoded blob.) it looks like it's rendering the result as HTML - so I'm not sure if the problem is in the client, or

I've tried putting \n into the message, but that didn't work. How can I send plain text with line breaks, and verify that the end result looks correct?

BTW, I can't actually send the email to test it with real email clients - corp. network is locked down.

@KM - yes. I'm trying to decide which to accept - yours is more elegant, and reads better, but Martins also works. — chris, Aug 19 '10 at 13:22

score 22 · Answer 1 · answered Aug 18 '10 at 17:20

I've always used CHAR(13)+CHAR(10) to create line breaks (which seems to work mixed in with nvarchar values) in TSQL, so try something like this:

DECLARE @CRLF char(2)
       ,@bodyText nvarchar(max)
       ,@field1  nvarchar(10)
       ,@field2  nvarchar(10)
SELECT @CRLF=CHAR(13)+CHAR(10)
      ,@field1='your data'
      ,@field2='and more'

set @bodyText =
                N'Here is one line of text ' 
                +@CRLF+ N'It would be nice to have this on a 2nd line ' 
                +@CRLF+ N'Below is some data: ' + N' ' + N' ' + ISNULL(@field1,'') + N' ' + ISNULL(@field2 + N' ' ,'')
                +@CRLF+ N'This is the last line' 


PRINT @bodyText

OUTPUT:

Here is one line of text 
It would be nice to have this on a 2nd line 
Below is some data:   your data and more 
This is the last line

this CHAR(13)+CHAR(10) will work with msdb.dbo.sp_send_dbmail, I send formatted e-mails using that all the time.

Not if you're sending the email with body as HTML – Fandango68 Jul 16 '15 at 01:46 — Fandango68, Jul 16 '15 at 01:46

Martin Smith · Accepted Answer · 2015-07-27T20:44:17.117

9

You aren't actually inserting any line breaks. You can embed them directly in a string literal in SQL Server as below.

SET @bodyText = (SELECT N'Here is one line of text 
It would be nice to have this on a 2nd line 
Below is some data: 


' + field1 + N' 

' + field2 + N' 

' + N'This is the last line'
                 FROM   myTable);

Or a tidier approach might be

DECLARE @Template NVARCHAR(max) = 
N'Here is one line of text 
It would be nice to have this on a 2nd line 
Below is some data: 

##field1##

##field2##

This is the last line';

SET @bodyText = (SELECT REPLACE(
                    REPLACE(@Template, 
                       '##field1##', field1), 
                       '##field2##', field2)
                 FROM   myTable);

Both will raise an error if myTable contains more than one row as you are assigning the result to a scalar variable.

edited Jul 27 '15 at 20:44

answered Aug 18 '10 at 17:20

Martin Smith

438,706
87
741
845

Surprisingly, it does! In fact, I can put whole blocks into one pair of quotes. – chris Aug 18 '10 at 17:42
really makes the code look funky, I used to do it that way. however, I found the `CHAR(13)+CHAR(10)` works better with indenting queries and other code. – KM. Aug 18 '10 at 17:54
Yes it doesn't really work very well with indenting as the `'` needs to go right at the start of the line to avoid injecting a whole load of surplus spaces but it can look a bit more WYSIWYG where that isn't a concern. – Martin Smith Aug 18 '10 at 18:23

score 7 · Answer 3 · edited Sep 23 '20 at 18:54

As Fernando68 mentions above, if you're allowed HTML emails, set @body_format = 'HTML', then you can use <br/> for line-breaks and make it as fancy as you want using all the tags that you get from HTML like line-breaks, img, strong, and so on...

set @bodyText = ( select 
                  '<h1>My Data</h1><p>Here is one line of text<br/>
                   It would be nice to have this on a 2nd line <br/>
                   Below is some data: <br/>'
                   + field1 + '<br/>'
                   + field2 + '<br/>'
                   + 'This is the last line</p>'
                   from myTable )

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'myProfile',
    @recipients = @to,
    @body = @bodyText,
    @body_format = 'HTML',
    @subject = 'Testing Email' ;

This doesn't really answer the original question but it maybe the best and simplest solution. I have used this approach to solve this problem *reliably* in the past (I found a text solution that usually worked but not always!). — Zeek2, Jul 27 '21 at 12:40

score 1 · Answer 4 · answered Jan 06 '21 at 16:03

In my case, the plain text email with CHAR(13)+CHAR(10) was getting formatted by Outlook that "helpfully" removes extra line breaks (unless it is unchecked in Outlook Options).

In addition, my list of entries would be dynamic, so I put together the STUFF FOR XML PATH that pulled in a concatenated string and simply added a literal empty string with a new line break and then assigned sp_send_dbmail's @body = @emailBody:

DECLARE @emailBody VARCHAR(MAX)

SET @emailBody = CONCAT('files: '
    ,(SELECT STUFF((SELECT
    ' 

' + CONCAT([FileName],'.csv')
    FROM localdb.dbo.tableName
    for xml path(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')))

How can I send plain text email (with line breaks) using sp_send_dbmail?

4 Answers4