转贴:SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC SendHtmlMailTest(@vMailTo VARCHAR(255), @vMailFrom VARCHAR(255))
AS
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 = localhost --如本机是2000且启动了smtp服务
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.csdn.net/images/ad/ad-java.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 + '<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', 'MSSQL Notifications'
EXEC @Hresult = sp_OASetProperty @iMsg, 'Subject', 'HI! This is HMTL/SMTP mail.'
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 ANSI_NULLS ON
GO
CREATE PROC SendHtmlMailTest(@vMailTo VARCHAR(255), @vMailFrom VARCHAR(255))
AS
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 = localhost --如本机是2000且启动了smtp服务
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.csdn.net/images/ad/ad-java.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 + '<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', 'MSSQL Notifications'
EXEC @Hresult = sp_OASetProperty @iMsg, 'Subject', 'HI! This is HMTL/SMTP mail.'
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
怎么办呀?!!
------------------------------------------------------
Thanks pengdali
怎么办呀?!!你用ie打开就可以了。
即可