dbWriter.com SQL stored procedure for sending mail
A SQL stored procedure to send
email using a CDONTS NewMail object (Microsoft Collaboration Data
Objects for Windows NT Server).
CREATE PROCEDURE [dbo].[zp_sendMAIL]
@From varchar(100),
@To varchar(100),
@CC varchar(100) = null,
@Subject varchar(100),
@Body varchar(4000),
@BCC varchar(100) = null,
@Attachment varchar(150) = null
AS
Declare @MailID int
Declare @theMail int
EXEC @theMail = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @theMail = sp_OASetProperty @MailID, 'From',@From
EXEC @theMail = sp_OASetProperty @MailID, 'To', @To
EXEC @theMail = sp_OASetProperty @MailID, 'CC', @CC
EXEC @theMail = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @theMail = sp_OASetProperty @MailID, 'Body', @Body
EXEC @theMail = sp_OASetProperty @MailID, 'BCC',@BCC
-- MIME format to ensure attachments are sent correctly via internet
EXEC @theMail = sp_OASetProperty @MailID, 'MailFormat', 0
EXEC @theMail = sp_OAMethod @MailID, 'AttachFile',NULL, @Attachment
EXEC @theMail = sp_OAMethod @MailID, 'Send', NULL
EXEC @theMail = sp_OADestroy @MailID
GO
|
|
