SMTP Mail

Question

SMTP Mail

yogaanand.me

SSCrazy

Points: 2777
More actions
August 19, 2008 at 10:36 pm

#382308

Dear Friends
i used sp for sending smtp mail through sql server 2000
Its coming perfectly…but I don’t how to insert query result in html format in the mail
Please help me ..it’s urgent
This is sp
CREATE PROCEDURE dbo.sendMail_With_CDOMessage1
AS
BEGIN
SET NOCOUNT ON
DECLARE
@handle INT,
@return INT,
@s-2 VARCHAR(64),
@sc VARCHAR(1024),
@up CHAR(27),
@from VARCHAR(64),
@server VARCHAR(255),
@filename VARCHAR(255),
@to VARCHAR(64),
@subject VARCHAR(255),
@body VARCHAR(8000)
SET @s-2 = '"http://schemas.microsoft.com/cdo/configuration/'
SELECT
@s-2 = 'Configuration.Fields(' + @s-2,
@up = 'Configuration.Fields.Update',
@from = 'xxxx@abc.net',
@to = yyyy@abc.net',
@subject = 'Test',
@server = ‘127.0.0.1’
-- or IP address, e.g. '127.0.0.1'
-- if you want an attachment:
,@filename = 'C:\1.txt'
EXEC @return = sp_OACreate 'CDO.Message', @handle OUT
SET @sc = @s-2 + 'sendusing").Value'
EXEC @return = sp_OASetProperty @handle, @sc, '2'
SET @sc = @s-2 + 'smtpserver").Value'
EXEC @return = sp_OASetProperty @handle, @sc, @server
EXEC @return = sp_OAMethod @handle, @up, NULL
EXEC @return = sp_OASetProperty @handle, 'To', @to
EXEC @return = sp_OASetProperty @handle, 'From', @from
EXEC @return = sp_OASetProperty @handle, 'Subject', @subject
EXEC @return = sp_OASetProperty @handle, 'htmlBody', @body
IF @filename IS NOT NULL
EXEC @return = sp_OAMethod @handle, 'AddAttachment', NULL, @filename
EXEC @return = sp_OAMethod @handle, 'Send', NULL
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Syed Jahanzeb Bukhari Grasshopper Points: 17 More actions · Answer 1

Hi,

i want to send email from sql server stored procedure using smpt server(smtp.google.com) kindly guide me how i can do it.

I m new in SQL.. Kindly tell me in detail

Thanks in advance

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Here is the best way I have found to use smtp in SQL Server 7, 2000. Download, install, and use xp_smtp_sendmail. Here's the link:

http://sqldev.net/xp/xpsmtp.htm

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question