• Login
• Become a member
• RSS
• Part of the TechTarget network

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

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

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.