# VBForums CodeBank > CodeBank - Other >  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'" )
> ...


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.

----------

