Send True SMTP Mail using CDOSys instead of CDONTS
SQL Server 2000 and Windows 2000 Only.
Microsoft suggest using CDOSys rather than CDONTS. CDOSys stands for Collaboration Data Objects for Windows 2000 Subsystems. Below is a script that allows you do do just that. NO EXCHANGE REQUIRED!! Sample sends the user HTML Mail!
Be sure to change @ServerIPAddr to your SMTP server.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC usp_CDOSys_MailTest(@vMailTo VARCHAR(255), @vMailFrom VARCHAR(255))
AS
/***
* Date:
* Author: <mailto:mikemcw@4segway.biz>
* Project: SMTP Mail using SQL
* Location: Master Database
* Permissions: PUBLIC EXECUTE
*
* Description: Sends SMTP mail using CDOSYS
* Example sends HTML Mail
***/BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE
@Hresult INT,
@iMsg INT,
@iConf INT,
@ErrorSource VARCHAR (255),
@ErrorDesc VARCHAR (255),
@ServerIPAddr VARCHAR(15)
SET @ServerIPAddr = '192.168.199.1'
BEGIN
EXEC @Hresult = sp_OACreate 'CDO.Message', @iMsg OUT
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
RAISERROR (@ErrorDesc, 16, 1, 1, 1, 2)
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END
DECLARE @strBodyHTML VARCHAR(8000)
SET @strBodyHTML = @strBodyHTML + '<HTML><HEAD><basefont face=tahoma style="font-size: 9px;">'
SET @strBodyHTML = @strBodyHTML + '<STYLE>'
SET @strBodyHTML = @strBodyHTML + 'td.small {'
SET @strBodyHTML = @strBodyHTML + 'font-family:Verdana, Trebuchet, Arial;'
SET @strBodyHTML = @strBodyHTML + 'font-size:10px;'
SET @strBodyHTML = @strBodyHTML + 'color:#000000;'
SET @strBodyHTML = @strBodyHTML + 'text-align:left;'
SET @strBodyHTML = @strBodyHTML + 'line-height:12px;'
SET @strBodyHTML = @strBodyHTML + 'margin-top:0px;'
SET @strBodyHTML = @strBodyHTML + 'margin-bottom:0px;'
SET @strBodyHTML = @strBodyHTML + 'text-decoration: none;'
SET @strBodyHTML = @strBodyHTML + '}'
SET @strBodyHTML = @strBodyHTML + 'p.detail {font-family: monospace;'
SET @strBodyHTML = @strBodyHTML + 'font-size: 11px.; margin-left: 8;'
SET @strBodyHTML = @strBodyHTML + 'margin-top: 1;'
SET @strBodyHTML = @strBodyHTML + '}'
SET @strBodyHTML = @strBodyHTML + '</style></HEAD>'
SET @strBodyHTML = @strBodyHTML + '<BODY><font color=green><h3>HI! This is HMTL/SMTP mail.</h3></font><img src="http://www.sqlservercentral.com/images/sqlservercentral_logo.gif" border=no><BR>'
SET @strBodyHTML = @strBodyHTML + ' <Font Face=Arial Size=5><B>This uses CDOSys and not outlook profiles!</B><BR>'
SET @strBodyHTML = @strBodyHTML + '<B>Set </B> <FONT COLOR=RED>Font Color</FONT></Font><BR><BR>'
SET @strBodyHTML = @strBodyHTML + '<A href="http://www.4segway.biz">Check this out!</a>'
SET @strBodyHTML = @strBodyHTML + '<HR /></BODY>'
SET @strBodyHTML = @strBodyHTML + '</HTML>'
/*Some info needed*/ DECLARE @cdoSendUsingPort INT
DECLARE @cdoAnonymous INT
SET @cdoSendUsingPort = 25
SET @cdoAnonymous = 0
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/sendusing")', @cdoSendUsingPort
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpserver")', @ServerIPAddr
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpserverport")', 25
EXEC @Hresult = sp_OASetProperty @iMsg, 'configuration("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")', @cdoAnonymous
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END
EXEC @Hresult = sp_OASetProperty @iMsg, 'To', @vMailTo
EXEC @Hresult = sp_OASetProperty @iMsg, 'From', 'DMSQL Production Notifications'
EXEC @Hresult = sp_OASetProperty @iMsg, 'Sender', @vMailFrom
set @strBodyHTML = replace(replace(@strBodyHTML, '''''', ''''), '''','''''')
EXEC @Hresult = sp_OASetProperty @iMsg, 'HTMLBody', @strBodyHTML
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END
/*Now send the message*/ EXEC @Hresult = sp_OAMethod @iMsg, 'Send'
IF @Hresult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @iMsg, @ErrorSource OUT, @ErrorDesc OUT
PRINT 'Error Occurred Calling Object: ' + @ErrorSource + ' ' + @ErrorDesc
RETURN
END
/*Cleanup*/ EXEC @Hresult = sp_OADestroy @iMsg
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
