send mail thru job executing stored procedure via cdonts method in html format

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)

send mail thru job executing stored procedure via cdonts method in html format

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-02 : 10:26:40

sun writes "hi,

i have a job which executes stored procedure. this stored procedure has the cdonts mail method to send the mail when the time is scheduled. this time scheduled is taken from a field in the database. now, when i send the mail, the mail is going but i want it to be bful with cosmetic changes ie. in html format. But since i am using sp_OAMethod. the code used is as follows:

--------------------------
REATE PROCEDURE mailfired AS

Declare @tomail varchar(30), @nowd int, @nowm char, @nowy int, @nmd datetime, @mf int, @reocc int
Declare @sub varchar(50), @mess varchar(500)
Declare @thend int, @thenm char, @theny int
set @nowd=day(getdate())
set @nowm=month(getdate())
set @nowy=year(getdate())

DECLARE sor CURSOR FOR
select userinfo.email, reminderinfo.subject, message, newmaildate, mailfired, reoccurance from reminderinfo join userinfo
on userinfo.email=reminderinfo.email where @nowd=datepart(day, newmaildate)
and @nowm=datepart(month, newmaildate) and @nowy=datepart(year, newmaildate) and reoccurance != 0
or @nowd=datepart(day, newmaildate) and maildate = newmaildate and MailFired = 0

OPEN sor
FETCH NEXT FROM sor into @tomail, @sub, @mess, @nmd, @mf, @reocc
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @result INT
EXEC @result = SP_CDONTS_NewMail_Send 'info@4kismet.com',
@tomail,@sub,@mess
PRINT @result
WAITFOR DELAY '000:00:10'

set @mf = @mf + 1
set @nmd =(@nmd + @mf + (@reocc-@mf))

update reminderinfo set newmaildate = @nmd, mailfired = @mf where reminderinfo.email = @tomail and
@nowd=datepart(day, newmaildate) and @nowm=datepart(month, newmaildate) and @nowy=datepart(year, newmaildate)
and subject = @sub and message = @mess
FETCH NEXT FROM sor into @tomail, @sub, @mess, @nmd, @mf, @reocc

END
CLOSE sor
DEALLOCATE sor
---------------------------------------

and the next stored procedure is:

-------------------------------------------------------
CREATE PROCEDURE SP_CDONTS_NewMail_Send
@from VARCHAR(8000),
@to VARCHAR(8000),
@subject VARCHAR(8000),
@body VARCHAR(8000)

AS

DECLARE @result INT
DECLARE @object INT

PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END

PRINT 'Sending the message using the Send method'

EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END

PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END

RETURN 0

--------------------------------------------------------

Please suggest how to use html tags in @body variable.

thanx
sun"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-02 : 15:26:31

HTML tags are just literal text like "<b>" turns on Bold. These can be embedded into your character string that makes up @body. Whatever is creating the contents for @Body can insert these characters and they can be stored in the SQL Server without issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources