SQL Stored Procedure to Send Email using OLE
You Just need to change SMTP Server address
CREATE PROCEDURE [dbo].[WSA_sp_SendEmail]@From varchar(100),@To varchar(1000),@Subject varchar(250),@Body varchar(max),@CC varchar(1000) = nullASBEGINDeclare @iMsg intDeclare @hr intDeclare @source varchar(255)Declare @description varchar(500)Declare @output varchar(1000)EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUTPrint @FromEXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.0.0.100'EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null--Print @ToEXEC @hr = sp_OASetProperty @iMsg, 'To', @ToEXEC @hr = sp_OASetProperty @iMsg, 'From', @Fromif (@Cc <> '')EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @CcEXEC @hr = sp_OASetProperty @iMsg, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @iMsg, 'HtmlBody', @BodyEXEC @hr = sp_OAMethod @iMsg, 'Send', NULLIF @hr <>0select @hr--print @hrBEGINEXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUTPrint @hrIF @hr = 0BEGINSELECT @output = ' Source: ' + @sourcePRINT 'hi'PRINT @outputSELECT @output = ' Description: ' + @descriptionPRINT @outputENDELSEBEGINPRINT ' sp_OAGetErrorInfo failed.'RETURNENDENDEXEC @hr = sp_OADestroy @iMsgEND
Comments