VB Script to send emails based off of a SQL table

[hoffmn]

I have created a table in SQL pulling a bunch of information together so I can email a client when a new account is created. I wanted to try to do this through SQL but I don't want to be using our DB to send emails out. That is why I am here. I want to create a VB script that I can set to run as a scheduled task every night which will read my SQL table and shoot an email out to anyone who matches the criteria. First, will this be easy to do using VB? Second, would anyone be willing to help me out? I am familiar with VB.Net but I am a noob at VB scripts. Basically this is what I want to do...

Look at the table and see if there were any accounts created in the previous day. I then want to pull out the new ACCOUNT NAME that was created for them. I want to pull any information stored in EMAIL1, EMAIL2, EMAIL3 and set those addresses as the RECIPIENTS. I then want to have a hard coded subject, and an HTML formatted body that can take fields out of the table and insert them into the body to make it a little more personal.

I appreciate any help I can get.

I originally had the following...

Do While Not objRS.EOF

strServer = "mail.domain.com"
strSender = "test@domain.com" 
strRecipient = email1&";"&email2&";"&email3
strSubject = "Your account has been created"
' strMessage = Taken from "Account Email.docx"
strMessage = "&lt;HTML&gt;&lt;BODY&gt;&lt;p&gt;Dear "&amp;email1&amp;",&lt;br/&gt;&lt;br/&gt;An account has been created.&lt;br/&gt;&lt;br/&gt;&lt;b&gt;ID:&lt;/b&gt;&lt;br/&gt;Your account ID is: &lt;b&gt;"&amp;sAMAccountName&amp;"&lt;/b&gt;&lt;br/&gt;Your Email Address is: &lt;b&gt;"&amp;sAMAccountName&amp;"@domain.com</b><br/>Account Creation Date is &lt;b&gt;"&amp;created&amp;"&lt;/BODY&gt;&lt;/HTML&gt;"

sendMail strServer, strSender, strRecipient, strSubject, strMessage

sub sendMail (server, sndr, rcpt, subj, msg)
    Dim iMsg, iConf, Flds 
    ' Set the visual basic constants as they do not exist within VBScript.
    ' Do not set your smtp server information here.
    Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
    Const cdoSendUsingPort = 2
    Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
    ' The following field names are not needed, but can be enabled
'     Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
'     Const cdoSendEmailAddress = "http://schemas.microsoft.com/cdo/configuration/sendemailaddress"
'     Const cdoSendUserReplyEmailAddress = "http://schemas.microsoft.com/cdo/configuration/senduserreplyemailaddress"
'     Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
'     Const cdoBasic = 1
'     Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
'     Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
'     Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"

    ' Create the CDO connections.
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields

     ' SMTP server configuration.
    With Flds
        .Item(cdoSendUsingMethod) = cdoSendUsingPort
        ' Set the SMTP server address here.
        .Item(cdoSMTPServer) = "mail.domain.com"
        ' Optional Fields
'         .Item(cdoSMTPServerPort) = 25
'         .Item(cdoSendEmailAddress) = """MySelf"" &lt;myself@example.com>;"
'         .Item(cdoSendUserReplyEmailAddress) = """Another"" &lt;another@example.com>;"
'         .Item(cdoSMTPAuthenticate) = cdoBasic
'         .Item(cdoSendUserName) = "domain\username"
'         .Item(cdoSendPassword) = "password"
'         .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With

    ' Set the message properties.
    With iMsg
        Set .Configuration = iConf
        .To = rcpt
'         .CC = rcpt
        .From = sndr
        .Subject = subj
    End With

    If InStr(UCase(msg), "&lt;HTML&gt;") Then
        iMsg.HTMLBody = msg
    Else
        iMsg.TextBody = msg
    End If

    ' An attachment can be included.
    'iMsg.AddAttachment Attachment

    ' Send the message.
    iMsg.Send

'move to the next record
    objRS.MoveNext
    Loop

...But when I had this I was getting a syntax error on the sub sendMail line.

I think my main problem is going to be that I want to do multiple passes through the table and send 1 email for each record. Each record is going to have a maximum of three email addresses (email1, email2 and email3). I need to populate the recipient list with those for each record or send 1 email per email1, 2, 3 per record.

I will put the full code below.

Dim strServer
Dim strSender
Dim strRecipient
Dim strSubject
Dim strMessage
Dim oSQLobject
Dim strSQL				'SQL string to access DB
Dim objRS				'Recordset object
Dim objConn				'Connection object
Dim strConn
Dim param1
Dim param2
Dim param3

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

'The following is the SQL connection string.
strConn.Open "Driver={SQL Server};server=server;database=db;uid=uname;pwd=pword;"

' -- Open the Connection
objConn.Open strConn

Set MyCommand = CreateObject("ADODB.Command")
Set MyCommand.ActiveConnection = MyConnection

'SQL query to get data
strSQL = "SELECT * FROM EmailTest"
'Populate our Recordset with data
set objRS = objConn.Execute (strSQL)

if (objRS.BOF and objRS.EOF) then
        response.write "No new accounts found."
        response.end
End if

'Now output the contents of the Recordset
        objRS.MoveFirst

Do While Not objRS.EOF

strServer = "mail.domain.com"
strSender = "test@domain.com" 
strRecipient = email1&";"&email2&";"&email3
strSubject = "Your account has been created"
' strMessage = Taken from "Account Email.docx"
strMessage = "&lt;HTML&gt;&lt;BODY&gt;&lt;p&gt;Dear "&amp;email1&amp;",&lt;br/&gt;&lt;br/&gt;An account has been created.&lt;br/&gt;&lt;br/&gt;&lt;b&gt;ID:&lt;/b&gt;&lt;br/&gt;Your account ID is: &lt;b&gt;"&amp;sAMAccountName&amp;"&lt;/b&gt;&lt;br/&gt;Your Email Address is: &lt;b&gt;"&amp;sAMAccountName&amp;"@domain.com</b><br/>Account Creation Date is &lt;b&gt;"&amp;created&amp;"&lt;/BODY&gt;&lt;/HTML&gt;"

sendMail strServer, strSender, strRecipient, strSubject, strMessage

sendMail server, sndr, rcpt, subj, msg
    Dim iMsg, iConf, Flds 
    ' Set the visual basic constants as they do not exist within VBScript.
    ' Do not set your smtp server information here.
    Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
    Const cdoSendUsingPort = 2
    Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
    ' The following field names are not needed, but can be enabled
'     Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
'     Const cdoSendEmailAddress = "http://schemas.microsoft.com/cdo/configuration/sendemailaddress"
'     Const cdoSendUserReplyEmailAddress = "http://schemas.microsoft.com/cdo/configuration/senduserreplyemailaddress"
'     Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
'     Const cdoBasic = 1
'     Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
'     Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
'     Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"

    ' Create the CDO connections.
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields

    ' SMTP server configuration.
    With Flds
        .Item(cdoSendUsingMethod) = cdoSendUsingPort
        ' Set the SMTP server address here.
        .Item(cdoSMTPServer) = "mail.domain.com"
        ' Optional Fields
'         .Item(cdoSMTPServerPort) = 25
'         .Item(cdoSendEmailAddress) = """MySelf"" &lt;myself@example.com>;"
'         .Item(cdoSendUserReplyEmailAddress) = """Another"" &lt;another@example.com>;"
'         .Item(cdoSMTPAuthenticate) = cdoBasic
'         .Item(cdoSendUserName) = "domain\username"
'         .Item(cdoSendPassword) = "password"
'         .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With

    ' Set the message properties.
    With iMsg
        Set .Configuration = iConf
        .To = rcpt
'         .CC = rcpt
        .From = sndr
        .Subject = subj
    End With

    If InStr(UCase(msg), "&lt;HTML&gt;") Then
        iMsg.HTMLBody = msg
    Else
        iMsg.TextBody = msg
    End If

    ' An attachment can be included.
    'iMsg.AddAttachment Attachment

    ' Send the message.
    iMsg.Send

'move to the next record
    objRS.MoveNext
    Loop

        response.write "&lt;/table&gt;"

        objRS.Close
        set objRS = Nothing
        objConn.Close
        set objConn = Nothing

Any help is greatly appreciated.

[Kami-]

Send me your DB schema (via pm, I don't check all posts), I'll sort you something out a little smoother.