Stored Prcedure: HTML email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Prcedure: HTML email

Author

Topic

Adelyra
Starting Member

14 Posts

Posted - 2011-03-17 : 13:10:07

Hello!

I'm attempting to create a stored procedure that will email when executed. So far I have a plain text version working however I'm wondering if it's possible to send it in HTML format. Here's what I have so far:

CREATE PROCEDURE [dbo].[usp_email]
	@To varchar(255),
	@Body varchar(500)
AS
	declare @rc int
EXEC 	@rc = master.dbo.xp_smtp_sendmail
	@FROM = N'randomemail@address.com',
	@FROM_NAME = N'Some Person',
	@TO = @To,
	@subject = N'This is the subject',
	@message = N'<html><body> <h2>Title</h2> <p>Paragraph 1</p> </body></html>',
	@type =  'HTML',
        --I just put HTML in there to see if it would work. The email sends but still in plain text.
	@server = 'localhost'
SELECT RC = @rc
GO

Note that I copied this from an existing stored procedure that a 'moved-on' coworker wrote. I have no idea what the @rc stuff means...

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-18 : 02:35:16

DECLARE one more variable for storing your message in HTML format.
Use the same variable for @message parameter while excuting master.dbo.xp_smtp_sendmail.

Adelyra
Starting Member

14 Posts

Posted - 2011-03-18 : 07:32:33

quote:
Originally posted by ahmeds08

DECLARE one more variable for storing your message in HTML format.
Use the same variable for @message parameter while excuting master.dbo.xp_smtp_sendmail.

Hi ahmeds08.

Thanks so much for your reply!

You'll have to forgive me as I'm still quite new to stored procedures (and T-SQL for that matter). I understand the concept of what you're saying however I'm not positive about how to write it. Would you be able to show me how to do that using the code snippet I provided?

Thanks again!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-18 : 08:19:07

CREATE PROCEDURE [dbo].[usp_email]
@To varchar(255),
@Body varchar(500)
AS
SET @Body='Your message body will go here'
declare @rc int
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'randomemail@address.com',
@FROM_NAME = N'Some Person',
@TO = @To,
@subject = N'This is the subject',
@message =@body,
@type = 'HTML',
--I just put HTML in there to see if it would work. The email sends but still in plain text.
@server = 'localhost'
SELECT RC = @rc
GO

saralstalin
Starting Member

11 Posts

Posted - 2011-03-18 : 11:40:14

use the sproc sp_send_dbmail to send the email. Check out http://msdn.microsoft.com/en-us/library/ms190307.aspx for more details. If you set @body_format = 'HTML' you can send emails formatted as html.

Saral S Stalin

Adelyra
Starting Member

14 Posts

Posted - 2011-03-18 : 13:02:24

@ahmeds08: Thanks again for your reply however it's still spitting everything out in plain text.

@Saral: The information you sent seems as though it would work, only, the problem is that I can't find the sp_send_dbmail procedure anywhere within the database (I checked msdb as well as master).

Perhaps SQL Server isn't configured proplerly to allow HTML emails -- be that the case I'm affraid I'm not familiar enough with the software to adjust the settings and may have to settle with plain text emails (how drab!)

Thanks so much for all your help. I'll let you know if I happen upon another solution.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-19 : 10:37:28

whats the edition you are working on?

Adelyra
Starting Member

14 Posts

Posted - 2011-03-19 : 11:31:54

quote:
Originally posted by ahmeds08

whats the edition you are working on?

SQL Server 2005

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-19 : 15:41:56

sql server 2005 is a version...iam asking is it a standard or developer or express or.....

Adelyra
Starting Member

14 Posts

Posted - 2011-03-20 : 09:01:21

quote:
Originally posted by ahmeds08

sql server 2005 is a version...iam asking is it a standard or developer or express or.....

OH! Sorry, I guess my inexperience is showing through. I'm fairly certain it's standard however I'll have to double check when I get back to work on Monday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources