Email using CDOSYS

It is really easy to email using sql. I once searching on the internet found a very good article reagrding it but it was using cdo for this pupose and since from Windows 2000 cdosys is provided it didnt work. I modified it slightly and now I want to discuss it with you.

It is really easy to create a mail using the COM object of CDO.SYS. An instance of the COM object can be created in Sql in the similar fashion as in C++ using the sp_OACreate stored procedure. This procedure takes 2 parameters, one is a string containing the name of the object to be created and second is the reference to the object to be created and returned. After calling this stored procedure, its second parameter contains the reference to that COM object. This procedure returns the status that can be used for error handling.

The second step is to setup the properties for this COM object which can be setup using the schema from microsoft server such as smtp server to be used and the store procedure sp_OASetProperty. Sendusing field specifies whether to use the port 25 or some other method. The value 2 represents port 25. Other fields can also be set here such as smtpconnectiontimeout etc. After setting up all these fields one should also call the update method of the cdosys COM object which can be called using the sp_OAMethod store procedure.

After setting up all these fields, one should set the headers of the mail also namely To, From, Date, and Subject. Other headers can also be set similarly.

Finally set the Body of the mail. It can be either Text or HTML. Separate properties are available for setting up either body type but only one can be used at one time. Finally call the Send method of the COM object to send the mail. This method can be called in the similar fashion as Update method for configuring fields.

It is better to clear the COM object after use. This can be done using the store procedure
sp_OADestroy and passing the COM object to be destroyed.

I think C++ developers will find it quite similir to what they usually do in C++. I have provide the SQL version of the CDOSYS emailer.

SQL Version

CREATE PROCEDURE [dbo].[xp_cdosendmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) ="

AS

Declare @Msg int
Declare @status int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @status = sp_OACreate 'CDO.Message', @Msg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.

EXEC @status = sp_OASetProperty @Msg, 'Configuration.fields"http://schemas.microsoft.com/cdo/configuration/
sendusing").Value','2'

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @status = sp_OASetProperty @Msg, 'Configuration.fields("http://www.blogger.com/, 'luckymail'

-- Save the configurations to the message object.
EXEC @status = sp_OAMethod @Msg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @status = sp_OASetProperty @Msg, 'To', @To
EXEC @status = sp_OASetProperty @Msg, 'From', @From
EXEC @status = sp_OASetProperty @Msg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @status = sp_OASetProperty @Msg, 'TextBody', @Body
EXEC @status = sp_OAMethod @Msg, 'Send', NULL

-- Sample error handling.
IF @status <>0
select @status
BEGIN
EXEC @status = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @status = 0
BEGIN
SELECT @output = ' Source: ' + @source
RaisError( @output , 16, 1)
SELECT @output = ' Description: ' + @description
RaisError( @output , 16, 1)
END
ELSE
BEGIN
RaisError(' sp_OAGetErrorInfo failed.', 16, 1)
Return
END
END


-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @status = sp_OADestroy @Msg