Solved

format @body in sp_send_dbmail

Posted on 2009-04-07
4
1,481 Views
Last Modified: 2012-06-27
I am calling sp_send_dbmail from within a stored procedure.  I am getting the content of @body from a field in a database.  I would like the body of the message to be formatted with as though the email had been typed.  I need to have the proper line feeds, bolding etc.  Does anyone know how to do this?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLServerDatabaseMailProfile',
@recipients = @recipients,
@body = @mymsg,
@subject = 'my subject'
0
Comment
Question by:spmcmorrow
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
aaronakin earned 500 total points
ID: 24092196
You'll need to format the body as HTML and add @body_format = 'HTML' to the procedure.
0
 

Author Comment

by:spmcmorrow
ID: 24097678
Ok, I just need a few details cleared up. I need to store the body of the message in a field (ideally I would like the user to be able to edit that message body..but that is second)  I need to take that message and customize it based on my data.  I should be able to do that.  

But what type of field in MS SQL do I store the html in.  I tried entering it in a varchar field and it didn't like all the special characters.  
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 24098198
Varchar field should be fine.  The script below will give you an example.

CREATE TABLE #Test (HtmlText VARCHAR(5000))
 
INSERT INTO #Test VALUES ('<html><body><p>This is html text</p></body></html>')
 
SELECT HtmlText FROM #Test
 
DROP TABLE #Test

Open in new window

0
 

Author Closing Comment

by:spmcmorrow
ID: 31567742
Thank you.  You answered the question I asked.  Now I have a few more, but I can ask them separately.  Thanks.
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
database level memory cache..? 8 37
Parse this column 6 34
SQL Recursion 6 33
SQL Recursion schedule 13 34
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question