Format string to send as email body

• Dominic Gagné

  SSCrazy

  Points: 2654

  More actions

  October 3, 2017 at 7:20 am

  #402561

  Hi,
  I have a StoredProc who sends an email.
  Here is the code that build the string:

  
DECLARE  @Message   NVARCHAR(MAX)
SET @Message = ''

       SET @Message = @Message + 'ContractID : ' + LTRIM(STR(@ContractID)) + CHAR(13)
     SET @Message = @Message + 'Client : '+ @client + CHAR(13)
     SET @Message = @Message + 'Date Début : '+ @StartDate + CHAR(13)
     SET @Message = @Message + 'Date Fin : '+ @EndDate + CHAR(13)
     SET @Message = @Message + ' --> VIENT À ÉCHÉANCE!! ' + CHAR(13) + CHAR(13)


  And the code that sends the email:
  EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'myemail@mail.com',
        @body_format = 'HTML',
        @body = @Message,
        @subject ='Test',
        @profile_name ='SAP';

  If I print @Message is SSMS, it looks fine, but when I receive the mail (in Outlook 2013), the body as no line feed nor carriage return, it looks like this:

  ContractID : 1 Client: Université Bishop Date Début : Jul 4 2014 12:00AM Date Fin : Jul 4 201712:00AM Description : Contrat de service sur DS457 --> VIENT À ÉCHÉANCE!!ContractID : 2 Client : Gestion Yves Guertin inc. Date Début : Jul 10 201412:00AM Date Fin : Jul 9 2017 12:00AM Description : 3

  I tried to add a CHAR(10), it won't change anything.

  How can I fix this??
  thanks a lot for your time and help
  
  

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 3, 2017 at 7:35 am

  #1962064

  That's because the format of your email is HTML, but you're expecting the mail client to treat the email like text. To insert a new line in HTML you need to use <br /> or use CHAR(10) and send your emails in text format.

  Edit: Personally, if you are sending your emails in html format, then send HTML. The email you're sending above doesn't contain any. really, instead of <br /> you should be using paragraph tags. For example, the value of @Message might look like.
  <p>This is a line</p>
<p>this is another line</p>
<br />
<p>This is yet another line, with a blank line above</p>

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Dominic Gagné

  SSCrazy

  Points: 2654

  More actions

  October 3, 2017 at 8:02 am

  #1962072

  Thom A - Tuesday, October 3, 2017 7:35 AM

  That's because the format of your email is HTML, but you're expecting the mail client to treat the email like text. To insert a new line in HTML you need to use <br /> or use CHAR(10) and send your emails in text format.

  Edit: Personally, if you are sending your emails in html format, then send HTML. The email you're sending above doesn't contain any. really, instead of <br /> you should be using paragraph tags. For example, the value of @Message might look like.
  <p>This is a line</p>
<p>this is another line</p>
<br />
<p>This is yet another line, with a blank line above</p>

  thanks a lot, it works perfectly now!!!

• madhugundapaneni

  Valued Member

  Points: 74

  More actions

  May 29, 2020 at 3:06 pm

  #3758701

  Thank you, thats perfect. I am getting my desired result.