How to send Mail with CDONTS from SQL Server

How to send Mail with CDONTS from SQL Server

CDONTS is short for Collaboration Data Objects for NT Server .When you are CDONTS will not be table to read your mail unless you get to the directory where your mail reside and open it with your mail reader. To use CDONTS instead of SQL Mail to send mail, try the following code

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]

@From varchar(100),

@To varchar(100),

@Subject varchar(100),

@Body varchar(4000),

@CC varchar(100) = null,

@BCC varchar(100) = null

AS

Declare @MailID int

Declare @hr int

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT

EXEC @hr = sp_OASetProperty @MailID, 'From',@From

EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body

EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC

EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC

EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @MailID, 'To', @To

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

EXEC @hr = sp_OADestroy @MailID

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]

@From varchar(100),

@To varchar(100),

@Subject varchar(100),

@Body varchar(4000),

@CC varchar(100) = null,

@BCC varchar(100) = null

AS

Declare @MailID int

Declare @hr int

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT

EXEC @hr = sp_OASetProperty @MailID, 'From',@From

EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body

EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC

EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC

EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @MailID, 'To', @To

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

EXEC @hr = sp_OADestroy @MailID

Step 1

To run it, all you need to do is provide the parameters to it :-

exec sp_send_cdontsmail 'info@webserver.com','someone@supercorridor.com','Test of CDONTS','Sample Testing testing '

Step 2

This will deliver the mail directly. If you have problem with delivery, try checking your SMTP server smart host configuration.

Step 3

Under the smart host, please make sure of your mail server. However this configuration is not necessary by most installation. In case you hit a problem then perhaps you can try providing your actual SMTP mail server name.