This set of stored procedures will 1) send e-mail to a comma delimited list of e-mails (7000 character maximum), 2) optionally carbon copy a comma delimited list of e-mails (7000 character maximum), and 3) provide support for a 7000 character body and 255 character subject using any SMTP server. It requires an external COM object, ASPEMail avaiable from http://www.aspemail.com/download.html.
I keep this in the model database, so each new database I create contains these procedures and the abilty to send e-mail.
There are three stored procedures in this set: sp_hexadecimal, sp_displayoaerrorinfo, and sp_sendmail. Usage:
EXEC sp_SendMail @SMTPServer, -- smtp server address
@From, -- From e-mail address
@FromName, -- From friendly name (uses e-mail address if null)
@AddAddress, -- Comma delimited Address list
@CCAddress, -- Comma delimited CC list
@Subject, -- EMail subject
@Body, -- EMail body
@Err -- Output Contains '0' if successful,
-- Actual error text if failure
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(255),@hexvalue varchar(255) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @charvalue varchar(255),
@i int,
@length int,
@hexstring char(16)
SET @charvalue = '0x'
SET @i = 1
SET @length = DATALENGTH(@binvalue)
SET @hexstring = '0123456789abcdef'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int,
@firstint int,
@secondint int
SET @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SET @firstint = FLOOR(@tempint/16)
SET @secondint = @tempint - (@firstint*16)
SET @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1)
SET @i = @i + 1
END
SET @hexvalue = @charvalue
END
GO
DROP PROCEDURE sp_displayoaerrorinfo
GO
CREATE PROCEDURE sp_displayoaerrorinfo @object int,@hresult int, @Err varchar(7000) OUTPUT
AS
BEGIN
DECLARE @hrhex char(10),
@hr int,
@source varchar(255),
@description varchar(255)
EXECUTE sp_hexadecimal @hresult, @hrhex OUT
SET @Err = ' HRESULT: ' + @hrhex
EXECUTE @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @Err = @Err + CHAR(13) + ' Source: ' + @source
SET @Err = @Err + CHAR(13) + ' Description: ' + @description
END
ELSE
BEGIN
SET @Err = 'Unable to get error information'
RETURN
END
END
GO
DROP PROC sp_SendMail
go
CREATE PROC sp_SendMail @SMTPServer varchar(50),
@From varchar(100),
@AddAddress varchar(7000),
@FromName varchar(100) = NULL,
@CCAddress varchar(7000)= NULL,
@Subject varchar(256) = NULL,
@Body varchar(7000) = NULL,
@Err varchar(7000) OUTPUT
AS
/**************************************************************************/
/* Name : sp_SendMail */
/* Purpose : This stored procedure will send e-mail to any smtp server */
/* Dependant */
/* Database : sp_hexadecimal */
/* : sp_displayoaerrorinfo */
/* External : ASPEmail Com Object http://www.aspemail.com/download.html */
/* */
/* Usage: : EXECUTE sp_SendMail , */
/* , */
/* , */
/* , */
/* , */
/* , */
/* */
/**************************************************************************/
BEGIN
DECLARE @object int,
@hr int,
@ErrInfo varchar(7000),
@tmpAddr varchar(100)
SELECT @AddAddress = RTRIM(@AddAddress)
SELECT @CCAddress = RTRIM(@CCAddress)
SELECT @Subject = RTRIM(@Subject)
SELECT @SMTPServer = RTRIM(@SMTPServer)
SELECT @From = RTRIM(@From)
SELECT @FromName = RTRIM(@FromName)
IF LEN(@AddAddress) > 0 OR LEN(@SMTPServer) > 0 OR LEN(@From) > 0
BEGIN /* Only do this if AddAddress, SMTPServer or From > 0 */
EXECUTE @hr = sp_OACreate 'Persits.MailSender', @object OUT
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
EXECUTE @hr = sp_OASetProperty @object, 'host', @SMTPServer
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
EXECUTE @hr = sp_OASetProperty @object, 'From',@From
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
IF @FromName IS NULL
BEGIN /* If FromName is NULL, set it to From Address */
EXECUTE @hr = sp_OASetProperty @object, 'FromName',@From
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
END /* If FromName is NULL, set it to From Address */
ELSE
BEGIN
EXECUTE @hr = sp_OASetProperty @object, 'FromName',@FromName
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
END
-- Add TO Address
WHILE LEN(@AddAddress) > 0
BEGIN
SELECT @tmpAddr = SUBSTRING(@AddAddress,1,CHARINDEX(',',@AddAddress))
IF LEN(@tmpAddr) = 0
BEGIN
SELECT @tmpAddr = @AddAddress
SELECT @AddAddress = ''
END
ELSE
BEGIN
SELECT @AddAddress = REPLACE(@tmpAddr,@AddAddress,'')
END
EXECUTE @hr = sp_OAMethod @object,'AddAddress',NULL, @tmpAddr
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
END
-- Add CC Address if any
WHILE LEN(@AddAddress) > 0
BEGIN
SELECT @tmpAddr = SUBSTRING(@CCAddress,1,CHARINDEX(',',@CCAddress))
IF LEN(@tmpAddr) = 0
BEGIN
SELECT @tmpAddr = @CCAddress
SELECT @CCAddress = ''
END
ELSE
BEGIN
SELECT @CCAddress = REPLACE(@tmpAddr,@CCAddress,'')
END
EXECUTE @hr = sp_OAMethod @object,'AddCC',NULL, @tmpAddr
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
END
EXECUTE @hr = sp_OASetProperty @object, 'Subject',@Subject
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
END
EXECUTE @hr = sp_OASetProperty @object, 'Body',@Body
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
EXECUTE @hr = sp_OAMethod @object, 'Send'
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
-- Destroy the object.
EXECUTE @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXECUTE sp_displayoaerrorinfo @object, @hr, @ErrInfo OUT
SELECT @Err = @ErrInfo
RETURN
END
END /* Only do this if AddAddress, SMTPServer or From > 0 */
ELSE
BEGIN /* If AddAddress, SMTPServer or From = 0 */
SELECT @Err = 'To Address, SMTP Server and From Address are required.'
END /* If AddAddress, SMTPServer or From = 0 */
IF @Err IS NULL
BEGIN
SELECT @Err = '0'
END
END
GO
