Activex Mailer - SQL Server Forums

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)

Activex Mailer

Author

Topic

sp_wiz
Yak Posting Veteran

55 Posts

Posted - 2002-02-11 : 17:25:39

I have this script that works fine in a ASP page, apart from it times out if there are too many records.

I read the article about using a com objects in sql and thought i could apply the same principle to the code below

However it reports it ran with no problems but i get no mails

Any ideas cheers

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Component = "aspmail"

MM_cellnet_STRING = Connection String

Set objRecipientRS = CreateObject("ADODB.Recordset")
objRecipientRS.ActiveConnection = MM_cellnet_STRING
' Need to add, to the line below, any fields for merging, like Password
objRecipientRS.Source = "SELECT FirstName, LastName, UserEmail, UserPassword FROM Users2"
objRecipientRS.CursorType = 0
objRecipientRS.CursorLocation = 2
objRecipientRS.LockType = 3
objRecipientRS.Open

Set objTemplateRS = CreateObject("ADODB.Recordset")
objTemplateRS.ActiveConnection = MM_cellnet_STRING
objTemplateRS.Source = "SELECT * FROM tblEmailTemplates WHERE TemplateID = 3"
objTemplateRS.CursorType = 0
objTemplateRS.CursorLocation = 2
objTemplateRS.LockType = 3
objTemplateRS.Open

set objMergeRS = CreateObject("ADODB.Recordset")
objMergeRS.ActiveConnection = MM_cellnet_STRING
objMergeRS.Source = "SELECT FieldName FROM tblMergeFields WHERE TemplateID = 3"
objMergeRS.CursorType = 0
objMergeRS.CursorLocation = 2
objMergeRS.LockType = 3
objMergeRS.Open

arrMergeFields = objMergeRS.GetRows()

objMergeRS.Close

strFromName = objTemplateRS("FromName")
strFromAddress = objTemplateRS("FromAddress")
strMailServer = objTemplateRS("MailServer")
strMailSubject = objTemplateRS("MailSubject")
strMailBody = objTemplateRS("MailBody")
strServerPort = CInt(objTemplateRS("ServerPort"))
strReplyTo = objTemplateRS("ReplyTo")

objTemplateRS.Close

Do Until objRecipientRS.EOF

' Create Mail Object
Set Mailer = CreateObject("SMTPsvg.Mailer")

' Set basic details
Mailer.FromAddress= strFromAddress

If Not IsNull(strFromName) AND strFromName <> "" Then

Mailer.FromName = strFromName

End If

Mailer.RemoteHost = strMailServer
Mailer.Subject = strMailSubject

' Create body text
strMyBody = strMailBody

For i = 0 To UBound(arrMergeFields, 2)

strField = arrMergeFields(0, i)

strMyBody = Replace(strMyBody, "{{" & strField & "}}", objRecipientRS(strField))

Next

Mailer.BodyText = strMyBody

' Create and append recipient
strName = objRecipientRS("FirstName") & " " & objRecipientRS("LastName")
Mailer.AddRecipient strName, objRecipientRS("UserEmail")

' Send it
Mailer.SendMail

objRecipientRS.MoveNext

Loop

Main = DTSTaskExecResult_Success
End Function

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-11 : 18:39:33

The code as is looks ok. I would say your SMTP server isn't running properly, or you have not set it properly.

Some debugging tips :

Copy the script into a .VBS file and run it on that machine. Also, try stipping a lot of the record set code out and just see if you can get a basic test mail send happening.

Just some thoughts, let us know how you go.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources