Hey everyone..
Sorry I haven't sent over that sp for sql (I doubt anyone was waiting for
it, hehe).. Anyways, here's a good working stored procedure to send email
via the imsmail com object, Howie, this code works so I guess you can put
that up with your code examples...
It could have alot more pizazz, but I created this sp for a current client
of mine, and this is all we need. By the way, the code can be shrunk down
big time, but I'm doing alot of error checking along the way.
Tom Langer
************* code below (copy and paste into a new stored procedure)
CREATE PROCEDURE [send_email]
@txtTo varchar(100),
@txtFrom varchar(100),
@txtSubject varchar(100),
@txtBody varchar(1000)
-- Stored Procedure: Send_email
-- Author: Tom Langer - [EMAIL PROTECTED]
-- This sp sends an email message using the IMSMail COM Object - it places
the email message directly in the email message queue used
-- by the inFusion Mail Server. I am using this in a real application,
however, my program doesn't need so many headers/variables to be support,
-- so I didn't create a super delux procedure here.
-- INPUTS:
-- If you can't figure them out, email me :-)
-- OUTPUTS:
-- The stored procedure returns a single record recordset, containing 1
field called Result, which will have only these values:
-- The value is either FAILURE or SUCCESSFUL
-- If successful, it may indicate whether or not there was an error or not
-- "FAILURE - Object could not be created'
-- "Send mail successful"
-- "Send mail successful with Error (..." - this result would pass what
parameters encountered an error..
AS
DECLARE @object int
DECLARE @resSP int
DECLARE @resSM int
DECLARE @resFN int
DECLARE @resError varchar(8000)
DECLARE @hr int
declare @tmpresult int
declare @tmpResultString varchar(5000)
EXEC @hr = sp_OACreate 'iMSMail.SendMail', @object OUT
-- IF THE OBJECT WAS NOT CREATED, THEN STOP THE PROCESS WITH AN ERROR
MESSAGE
IF @hr <> 0
BEGIN
SELECT 'FAILURE - Object could not be created' as Result
RETURN
END
SET @resFN = 0
SET @resError = ''
-- SET THE SPOOL DIRECTORY
EXEC @resSP = sp_OASetProperty @object, 'SpoolDir', 'f:\ims\out\'
IF (@resSP <> 0)
BEGIN
SET @resFN = @resSP + @resFN
SET @resError = @resError + ',Error setting Spool Directory'
END
-- SET THE SMTP TO PROPERTY.. This is who the message is really sent too
EXEC @resSP = sp_OASetProperty @object, 'smtpto', @txtTo
IF (@resSP <> 0)
BEGIN
SET @resFN = @resSP + @resFN
SET @resError = @resError + ',Error setting SMTPTO'
END
-- SET THE SMTP FROM PROPERTY
EXEC @resSP = sp_OASetProperty @object, 'smtpfrom', @txtFrom
IF (@resSP <> 0)
BEGIN
SET @resFN = @resSP + @resFN
SET @resError = @resError + ',Error setting SMTPFROM'
END
EXEC @resSP = sp_OASetProperty @object, 'body', @txtBody
SET @resFN = @resSP + @resFN
SET @txtTo = 'SetHeader("To", ' + char(34) + @txtTo + char(34) + ')'
SET @txtFrom = 'SetHeader("From", ' + char(34) + @txtFrom + char(34) + ')'
SET @txtSubject = 'SetHeader("Subject", ' + char(34) + @txtSubject +
char(34) + ')'
EXEC sp_OAMethod @object, @txtTo
EXEC sp_OAMethod @object, @txtFrom
EXEC sp_OAMethod @object, @txtSubject
EXEC @resSM = sp_OAMethod @object, 'SendMail'
EXEC @resSP = sp_OAGetProperty @object, 'Result', @tmpResult OUT
EXEC @resSP = sp_OAGetProperty @object, 'ResultString', @tmpResultString OUT
-- RETURN
--print 'sending mail is complete'
IF (@resSM <> 0)
BEGIN
SELECT 'send mail process failed' as Result
RETURN
END
IF (@resSM = 0)
BEGIN
IF (@resFN = 0)
BEGIN
select 'Send mail successful' as Result
END
IF (@resFN <> 0)
BEGIN
select 'Send mail successful with Error (' + @resError + ')' as Result
END
END
========================================================================
This list server is Powered by iMS
'The Swiss Army Knife of Mail Servers'
--------------------------------------
To leave this list please complete the form at
http://www.coolfusion.com/iMSSupport.cfm
Need an iMS Developer license? Sign up for a free license here:
http://www.coolfusion.com/iMSDevelopers.cfm
List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
========================================================================
