Justin,
I attached a stored proc someone else had posted to this list originally (sorry I
forgot who did it). I probably made some mods. Below is an example of calling it:
exec usp_send_imsMail '[EMAIL PROTECTED]', 'email subject', 'email message text'
Hope that helps!
-Max
At 10/24/2003 12:45 PM +0100, webguy wrote:
>Does anyone have a example of using the COM object from SQL Server.
>
>Say I have a trigger like below (I know its not exactly right)
>
>SELECT EmailAddress,FullName
>FROM Users
>WHERE ProductID =(
> SELECT ProductID
> FROM INSERTED
> )
>
>SELECT @Product_Price = Price,
> @Product_Desc = Description
>FROM Products
>WHERE ProductID = (
> SELECT ProductID
> FROM INSERTED
> )
>
>How do I send the Price and Desciption to the users ?
>I'll start searching the archives too.
>
>Thanks Justin
==^=======================================================
This list server is Powered by iMS "The Swiss Army Knife of Mail Servers"
--------------------------------------------------------------------------------------
This list is provided as a free service. Although we will try to address issues
in a timely manner, support via this list is not guaranteed. If you require expedited
support then a support contract is required. Support may be purchased from
http://www.coolfusion.com/commerce. Details regarding support options may be reviewed
at: http://www.coolfusion.com/SupportOptions.cfm
--------------------------------------------------------------------------------------
To leave this list please complete the form at http://www.coolfusion.com/Support/
Need an iMS Developer license? Sign up for a free license here:
http://www.coolfusion.com/Developers/
List archives: http://www.mail-archive.com/infusion-email%40eoscape.com/
Note: You are subscribed as [EMAIL PROTECTED]
==^=======================================================
USE master
go
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'usp_send_imsMail' AND type = 'P')
DROP PROCEDURE usp_send_imsMail
GO
CREATE PROCEDURE usp_send_imsMail
@txtTo varchar(100),
@txtSubject varchar(100),
@txtBody text,
@txtFrom varchar(100) = '[EMAIL PROTECTED]',
@spoolDir varchar(150) = '\\nas1\MailSpool\'
-- USAGE: exec usp_send_imsMail '[EMAIL PROTECTED]', 'Subject', 'Body' [, @txtFrom =
'[EMAIL PROTECTED]'] [, @spoolDir='path to iMS Spool Folder']
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', @spoolDir
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
-- SET THE PRIORITY PROPERTY
EXEC @resSP = sp_OASetProperty @object, 'Priority', 1
IF (@resSP <> 0)
BEGIN
SET @resFN = @resSP + @resFN
SET @resError = @resError + ',Error setting PRIORITY'
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
GO
GRANT EXEC ON sp_send_imsmail TO public
GO
