SQL Stored Procedure to Send Emails ..
You will need Execute Access on Some SPs on your Master database on SQL server. But its pretty slick.
CREATE PROCEDURE [dbo].[USP_Send_CDOSysmail]
(
@From varchar(100),
@To varchar(100),
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@Cc varchar(100)=null,
@BCc varchar(100)=null,
@Attachments varchar(1000)=null
)
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Exec @hr = sp_OACreate 'CDO.Message', @iMsg OUT
Exec @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
Exec @hr = sp_OASetProperty @iMsg, 'To', @To
If(@Cc Is Not Null)
Begin
Exec @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
End
If(@BCc Is Not Null)
Begin
Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc
End
Exec @hr = sp_OASetProperty @iMsg, 'From', @From
Exec @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
Exec @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
If (@Attachments Is Not null)
Begin
Declare @charPos int
Declare @Attachment varchar(1000)
Set @CharPos = 1
While (@CharPos > 0)
Begin
Set @charPos = CHARINDEX(';',@Attachments)
If(@charPos = 0)
Begin
Set @Attachment = SUBSTRING(@Attachments,1,len(@Attachments))
Exec @hr = sp_OAMethod @iMsg, 'Addattachment', NULL, @Attachment
break
End
Set @Attachment = SUBSTRING(@Attachments,1,@Charpos - 1)
Set @Attachments = SUBSTRING(@Attachments,@Charpos + 1,len(@Attachments))
Exec @hr = sp_OAMethod @iMsg, 'Addattachment', NULL, @Attachment
End
End
Exec @hr = sp_OAMethod @iMsg, '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
Exec @hr = sp_OADestroy @iMsg
GO
