Sending SMTP from SQL stored Procedure

Discussion for developers using MailEnable.
Post Reply
pum4
Posts: 1
Joined: Mon Sep 23, 2019 1:30 pm

Sending SMTP from SQL stored Procedure

Post by pum4 » Mon Sep 23, 2019 1:33 pm

It took me a while to figure out how to send an SMTP mail from SQL stored procedure through MEMail com so I thought I would Share...

CREATE PROCEDURE dbo.SQLMail

@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'

AS

SET nocount on
declare @oMail int --Object reference
declare @resultcode int

EXEC @resultcode = sp_OACreate 'MEMail.Message', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'MailFromDisplayName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'MailFrom', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'MailTo', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'MessageBody', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMessage', NULL
EXEC sp_OADestroy @oMail
END


SET nocount off
GO



Hope someone can put this to good use,
Thank You,
https://www.nuomajums.lt/

Post Reply