June 25, 2002 at 2:53 am
Hi Guys
Could someone have a quick look through this code, it's driving me nuts. I want to set
this SP up to use CDONTS to send HTML formatted emails via SMTP. All I'm getting through though is the raw HTML. I've set both BodyFormat and MailFormat to 0, but to no avail.
Sp:
CREATE 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><BR>'
SET @strBody = @strBody + '<HR>'
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
Any help would be greatly appreciated.
Regards
Joe
June 25, 2002 at 10:02 am
Solved....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply