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