CREATE PROCEDURE sys_sendmail @To varchar(100) , @Bcc varchar(500), @Subject varchar(400)=" ",
@Body varchar(4000) =" "
AS
Declare @smterver varchar(50) --SMTP服务器地址
Declare @smtpusername varchar(50) --SMTP服务器用户名
Declare @smtpuserpassword varchar(50) --SMTP服务器密码
set @smtpserver = 'smtp.163.com'
set @smtpusername = 'yourname@163.com' --这里设置成你的163邮箱用户名
set @smtpuserpassword = 'password' --这里设置成你的163邮箱密码
Declare @object int
Declare @hr int
EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver
--下面三条语句是smtp验证,如果服务器需要验证,则必须要这三句,你需要修改用户名和密码
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@smtpusername
EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@smtpuserpassword
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
EXEC @hr = sp_OASetProperty @object, 'From', @smtpusername
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @object, 'TextBody', @Body
EXEC @hr = sp_OAMethod @object, 'Send', NULL
--判断出错
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
print 'failed'
return @object
END
PRINT 'success'
EXEC @hr = sp_OADestroy @object
GO
|