Send mail with aspSmartMail object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Send mail with aspSmartMail object

Author

Topic

torrisi
Starting Member

3 Posts

Posted - 2003-12-11 : 04:53:02

Hello,

I want to create a Storage Procedure to send a mail. I also want to use the aspSmartMail component.

I dit the following:

CREATE PROCEDURE [dbo].[sp_send_aspmail]
@FromName varchar(100),
@FromAdress varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@To varchar(100)
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'aspSmartMail.SmartMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'Server', 'mail.mca.nl'
EXEC @hr = sp_OASetProperty @MailID, 'SenderName', @FromName
EXEC @hr = sp_OASetProperty @MailID, 'SenderAdress', @FromAdress
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'Recipients.Add', @To
EXEC @hr = sp_OAMethod @MailID, 'SendMail', NULL
EXEC @hr = sp_OADestroy @MailID

To use the Storage procedure, I use

EXEC sp_send_aspmail 'SQl','sql@mca.nl','Subject','Body','at@mca.nl'

I don't get an error, but I don't get the mail. What is wrong? I gues it is the part :

EXEC @hr = sp_OASetProperty @MailID, 'Recipients.Add', @To

Please help me

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-12-11 : 04:59:54

Hi torrisi!

I suggest you start by implementing some error handling after each call to the sp_OA* procs.

for example:
declare @eSrc varchar(50), @eDesc varchar(50)

EXEC @hr = sp_OASetProperty @MailID, 'Server', 'mail.mca.nl'
if @hr <> 0
begin
exec sp_OAGetErrorInfo @MailID, @eSrc OUT, @eDesc OUT
print 'Error occured. Src= ' + @eSrc + ', Desc= ' + @eDesc
end

torrisi
Starting Member

3 Posts

Posted - 2003-12-11 : 05:28:29

Thanx Andraax,

now I get an error :

Error occured. Src= aspSmartMail.SendMail : Error 80, Desc= There is no Recipient. (verify the address list)

This error occured at this point :

EXEC @hr = sp_OASetProperty @MailID, 'Recipients.Add', @To

I understand the problem (I guess), I can't use 'Recipients.Add' because Add is an Property of Recipients.

Any Idea to solve this?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-11 : 06:57:35

Isn't 'Recepients.Add' a function call? You should try using sp_OAMethod for that line.

Owais

torrisi
Starting Member

3 Posts

Posted - 2003-12-12 : 03:10:06

sp_OAMethod doesn't work.

The storage procedure I wrote was originally a VBS script :

SendSMTPMessage "Subject", "Body", "e.mail@mca.nl"

Sub SendSMTPMessage(Subject, MessageText, Recipients)
Const SMTPServer = "mailserver"
Const SMTPSenderName = "SQL Test"
Const SMTPSenderAddress = "sql@mca.nl"
Set mySmartMail = CreateObject("aspSmartMail.SmartMail")
mySmartMail.Server = SMTPServer
mySmartMail.SenderName = SMTPSenderName
mySmartMail.SenderAddress = SMTPSenderAddress
mySmartMail.Subject = Subject
mySmartMail.Body = MessageText
For Each MyRecipient In Recipients
mySmartMail.Recipients.Add MyRecipient
Next
mySmartMail.SendMail
Set mySmartMail = Nothing
End Sub

I trieed to transelate this script to a storage procedure

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-12 : 06:43:04

Check the SMTP server logs.
Chances are good that your message is getting stalled there.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources