Set Quoted_identifier On
Go
Set Ansi_nulls On
Go
Alter Procedure Usp_sendmail @ Varchar ( 100 ), @ Subject Varchar ( 400 ) = ' ' , @ Body Varchar ( 8000 ) = ' '
As
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 ' , ' SMTP ' -- SMTP address
-- The following three statements are SMTP verification. If the server needs verification, you must modify the user name and password.
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 ' , ' Your email address ' -- Your email address
Exec @ HR = Sp_oasetproperty @ Object , ' Configuration. Fields ("http://schemas.microsoft.com/cdo/configuration/sendpassword"). Value ' , ' Yourpassword ' -- Email Password
Exec @ HR = Sp_oamethod @ Object , ' Configuration. Fields. Update ' , Null
Exec @ HR = Sp_oasetproperty @ Object , ' To ' , @
Exec @ HR = Sp_oasetproperty @ Object , ' BCC ' , ' Goodspeedwang@yahoo.com.cn '
Exec @ HR = Sp_oasetproperty @ Object , ' From ' , ' Goodspeed <goodspeed@idg-rbi.com.cn> '
Exec @ HR = Sp_oasetproperty @ Object , ' Subject ' , @ Subject
Exec @ HR = Sp_oasetproperty @ Object , ' Bodyformat ' , ' Mailformat. Text '
Exec @ HR = Sp_oasetproperty @ Object , ' Textbody ' , @ Body -- Text Format
Set @ Body = Replace ( @ Body , Char ( 13 ), ' <Br/> ' )
Exec @ HR = Sp_oasetproperty @ Object , ' Htmlbody ' , @ Body -- Emails in HTML Format
Exec @ HR = Sp_oamethod @ Object , ' Send ' , Null
-- Error Identification
If @ HR <> 0
Begin
Exec Sp_oageterrorinfo @ Object
Return @ Object
End
Print ' Success '
Exec @ HR = Sp_oadestroy @ Object
Go
Set Quoted_identifier Off
Go
Set Ansi_nulls On
Go
For more information about CDO, visit
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/exchanchor/htms/msexchsvr_cdo_top.asp