CDONTS HTML from Stored Procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

CDONTS HTML from Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-27 : 09:08:43

Joe writes "Hi Guys

I've search high and low to try and resolve this particular problem. There seem to be a fair few articles out there that 'skirt' around the topic, but none that nail it down.

I'm working on a stored procedure that sends mail using the CDONTS object installed by Microsoft SMTP service, but in HTML.

I need to use CDONTS as the server is NT and SQLMail is not an option.

I'm pretty sure that my stored procedure isn't conveying BodyFormat and MailFormat correctly.

Could you have a look over my procedure, and put me out of my misery?

SP:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE PrsendSMTPmail
(
@To varchar(8000),
@Subject varchar(255),
@Importance int = 2,
@Cc varchar(8000) = NULL,
@Bcc varchar(8000) = NULL,
@Attachments varchar(8000) = NULL,
@MailFormat int = 0,
@BodyFormat int = 0,
@From varchar(255) = NULL
)
AS

SET NOCOUNT ON

DECLARE @object int
DECLARE @hr int
DECLARE @StrEnd int
DECLARE @Attachment varchar(255)
DECLARE @return int
DECLARE @Msg varchar(255)
DECLARE @strBody varchar(8000)

SET @strBody = '<!DOCTYPE HTML PUBLIC""-//IETF//DTD HTML//EN"">'
SET @strBody = @strBody + '<HTML>'
SET @strBody = @strBody + '<HEAD>'
SET @strBody = @strBody + '<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; CHARSET=iso-8859-1"">'
SET @strBody = @strBody + '</HEAD>'
SET @strBody = @strBody + '<BODY>'
SET @strBody = @strBody + '<FONT FACE=""ARIAL"" COLOR=""#FF0000"">Test</FONT>
'
SET @strBody = @strBody + '

'
SET @strBody = @strBody + '</BODY>'
SET @strBody = @strBody + '</HTML>'

SET @strBody = REPLACE(@strBody, '""', '"')

SELECT @From = ISNULL(@From, @@SERVERNAME)

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError

IF @strBody IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @strBody
IF @hr <> 0 GOTO ObjectError
END

IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END

IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END

IF @MailFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', @MailFormat
IF @hr <> 0 GOTO ObjectError
END

IF @BodyFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'BodyFormat', @BodyFormat
IF @hr <> 0 GOTO ObjectError
END

CREATE TABLE #FileExists
(
FileExists int,
FileIsDir int,
ParentDirExists int
)

WHILE ISNULL(LEN(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE CHARINDEX(';', @Attachments)
WHEN 0 THEN LEN(@Attachments)
ELSE CHARINDEX(';', @Attachments) - 1
END
SELECT @Attachment = SUBSTRING(@Attachments, 1, @StrEnd)
SELECT @Attachments = SUBSTRING(@Attachments, @StrEnd+2, LEN(@Attachments))

DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment

IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END

EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError

SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END

EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance = @Importance
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError

PRINT 'Message Sent Successfully'
RETURN 0

ObjectError:
BEGIN
EXEC PrDisplayOAErrorInfo @object, @hr
RETURN 1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Tha

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-27 : 13:00:14

Save yourself some hassle

[url]http://sqldev.net/xp/xpsmtp.htm[/url]

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources