use master; if OBJECT_ID('sp_SendDatabaseMail') is not null drop proc sp_SendDatabaseMail go CREATE PROCEDURE sp_SendDatabaseMail @Subject varchar(400) = '', @HtmlBody varchar(8000) = '' as Declare @From varchar(100) Declare @To varchar(100) Declare @Bcc varchar(500) Declare @AddAttachment varchar(100) Declare @object int Declare @hr int Declare @source varchar(255) Declare @description varchar(500) Declare @output varchar(1000) set @To = @recipients set @Bcc = '' set @AddAttachment = '' --set @HtmlBody= '<body><h1><font color=Red>' +@HtmlBody+'</font></h1></body>' 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', 'xxx.xxx.xxx.xxx' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25' 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',' [email protected]' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','*****************' 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', @From EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject EXEC @hr = sp_OASetProperty @object, 'HtmlBody', @HtmlBody --add attachment if @AddAttachment<>'' EXEC @hr = sp_OAMethod @object, 'AddAttachment',NULL,@AddAttachment IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END --send mail EXEC @hr = sp_OAMethod @object, 'Send', NULL IF @hr <>0 select @hr BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END end PRINT 'Send Success!!!' --destroy object EXEC @hr = sp_OADestroy @object |