asked on

Problem with email format from SQL DB

I am trying to send an email from an SQL Db.
The email looks fine in the query window when I test but formats incorrectly when its been sent.
The Comments need to line up in a list but are shown on one line.
The output is shown below.
This is the code I am using and the output from the Query window

-- *********************************************************************
--	Get Comments from RMAComments table 
-- *********************************************************************	
			SELECT @list=rtrim(Comment) +  char(13) + COALESCE(@list,'')
			FROM  RmaComment WHERE (RmaNumber = @rmaNumber) order by RmaLineNumber desc
	 		set @tab = char(9)		
			set @subject = 'RMA ' + @rmaNumber +'  Comment Added'
			set @query_value = (SELECT LEFT(@list,LEN(@list)-1))
			
-- *********************************************************************	
-- Setup Email layout in HTML ---------
-- *********************************************************************			
			set @body = 'RMA ' + @rmaNumber +' updated:' + char(13) + 
						'RMA Number RMA' + @tab + @rmaNumber + char(13) +
						'RMA Comments *****' + @tab  +  @tab + char(13) +
						'RMA Comments *****' + @query_value + char(13) +
						'RMA Comments *****' + @tab  +  @tab + char(13) 
						
						
-- *********************************************************************			
-- Send email -------------------------------------------------
--*************************************************************************** 
			exec  msdb.dbo.sp_send_dbmail 
			    @recipients = 'richard.cooper@xXXX'
				, @body = @body
				, @subject = @subject

Open in new window

Output:
Mail (Id: 1080) queued.
RMA 000000000005025 updated:
RMA Number RMA      000000000005025
RMA Comments *****
RMA Comments *****Problem Comment on part 2 06/03/17 ADMIN
Line 2
Line 3
Line 4
Line 5
Test to add comments

RMA Comments *****

Email :
RMA 000000000005025 updated:
RMA Number RMA      000000000005025
RMA Comments *****
RMA Comments *****Problem Comment on part 2 06/03/17 ADMIN Line 2 Line 3 Line 4 Line 5 Test to add comments

RMA Comments *****

Thanks in advance.

Kelvin Sparks

Is the Body in HTML format? I'd expect to see html tags if it was.

Without html, the layout will be all over the place.

I tried to find tags like <li>some text </li>

Kelvin

Matt Bowler

I agree, if you want to format an email you will need to use HTML.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Cooper

ASKER

I have changed the code to use CHAR(9) and CHAR(13) which works. After the comments regarding the email client.

-- *********************************************************************
--      Get Comments from RMAComments table
-- *********************************************************************

                  SELECT @list= char(9) + rtrim(Comment) + char(13) + COALESCE(@list,'')
                  FROM RmaComment WHERE (RmaNumber = @rmaNumber) order by RmaLineNumber desc
                   set @tab = char(9)
                  set @subject = 'RMA ' + @rmaNumber +' Comment Added'
                  set @query_value = (SELECT LEFT(@list,LEN(@list)-1))

-- *********************************************************************
-- Setup Email layout in HTML ---------
-- *********************************************************************
                  set @body = 'RMA ' + @rmaNumber +' updated:' + char(13) +
                                    'RMA Number RMA' + @tab + @rmaNumber + char(13) +
                                    'RMA Comments *****' + @tab + @tab + char(13) +
                                    char(13)+ @query_value + char(13) +
                                    'RMA Comments *****' + @tab + @tab