Technical Article

Email using CDONTS (via smtp)

,

Here is a very basic example script for sending emails via CDONTS. This comes from the sqlmag web-site forum. Although fine, remember that it will use the local SMTP server to forward emails onto the destination user. If your servers smtp process can not reach an exchange/group-wise/other mail box on your network, then this code will not work. Look at coding your own VB COM object under the similar vein to easily change the destination smtp server.

CREATE PROCEDURE SendMail_sp (@FROM NVARCHAR(255), @TO NVARCHAR(255), @SUBJECT NVARCHAR(255), @BODY NVARCHAR(4000)) AS

DECLARE @Object int
DECLARE @Hresult int
DECLARE @ErrorSource varchar (255)
DECLARE @ErrorDesc varchar (255)
DECLARE @V_BODY NVARCHAR(4000)

DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @Hresult = sp_OACreate 'CDONTS.NewMail', @Object OUT

IF @Hresult = 0 begin

--SET SOME PROPERTIES

SET @V_BODY =  '' + @BODY

EXEC @Hresult = sp_OASetProperty @Object, 'From', @FROM
EXEC @Hresult = sp_OASetProperty @Object, 'To', @TO
EXEC @Hresult = sp_OASetProperty @Object, 'Subject', @SUBJECT
EXEC @Hresult = sp_OASetProperty @Object, 'Body', @V_BODY

--CALL SEND METHOD
EXEC @Hresult = sp_OAMethod @Object, 'Send', NULL

--DESTROY THE OBJECT
EXEC @Hresult = sp_OADestroy @Object
end
else begin
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
end

GO

Rate

Share

Share

Rate