SQL Stored Procedure to Send Emails ..

[techyspecy]

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

[HackerNeo]

If you use SQL Mail Config you can use the inbuild SP's to send mail

i.e

SET sMail = SERVER.CREATEOBJECT("ADODB.Connection")
sMail.Connectionstring = "Your Connection String"
sMail.Open
sMail.Execute("xp_startmail 'SEND ACCOUNT NAME'" )
sMail.Execute("xp_sendmail 'TO CLIENT ADDRESS', @subject ='ANYTHING', @message ='SOMETHING' , @width=5000")
sMail.Execute("xp_stopmail")
sMail.Close

This is easier

Thanks

[techyspecy]

Originally posted by HackerNeo
If you use SQL Mail Config you can use the inbuild SP's to send mail

i.e

SET sMail = SERVER.CREATEOBJECT("ADODB.Connection")
sMail.Connectionstring = "Your Connection String"
sMail.Open
sMail.Execute("xp_startmail 'SEND ACCOUNT NAME'" )
sMail.Execute("xp_sendmail 'TO CLIENT ADDRESS', @subject ='ANYTHING', @message ='SOMETHING' , @width=5000")
sMail.Execute("xp_stopmail")
sMail.Close

This is easier

Thanks

xp_starmail and procedures like this require exchange server account and logon information. which is more cumbersome and problematic. While my SP does not require such things, only SMTP services which runs on most of the servers.