HOWTO: Using AspEmail in a Transact-SQL environment (SQL Server)

Summary

Many database applications require email-sending functionality. For example, every time a table record is updated, an email notification needs to be sent to the system administrator. In the Microsoft SQL Server environment, AspEmail can be invoked directly inside a trigger or stored procedure using Transact-SQL.

Solution

The following code snippet creates a SQL Server stored procedure which sends an email message using AspEmail. You may use a similar approach for triggers, etc.

CREATE procedure sp_SendMail
   @To varchar(255),
   @From varchar(255),
   @FromName varchar(255)
AS

DECLARE @Mail int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'Persits.MailSender', @Mail OUT

IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @Mail, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
   RETURN
END

EXEC @hr = sp_OASetProperty @Mail, 'Host', 'rome'
EXEC @hr = sp_OASetProperty @Mail, 'From', @From
EXEC @hr = sp_OASetProperty @Mail, 'FromName', @FromName
EXEC @hr = sp_OASetProperty @Mail, 'Body', 'your message body here.'

EXEC @hr = sp_OAMethod @Mail, 'AddAddress', NULL, @To
EXEC @hr = sp_OAMethod @Mail, 'Send'

IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @Mail, @src OUT, @desc OUT
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
   EXEC sp_OADestroy @Mail
   RETURN
END

-- Important! All objects must be destroyed explicitly!
EXEC sp_OADestroy @Mail