• 0

VB Script to send emails based off of a SQL table


Question

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&amp;";"&amp;email2&amp;";"&amp;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.

1 answer to this question

Recommended Posts

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • About that Trump's mobile 🤣🤣🤣  
    • AliExpress faces EU crackdown, makes promises to fight illegal products by Paul Hill The European Commission has taken two significant actions against the Chinese online marketplace AliExpress under the Digital Services Act (DSA) in a bid to enhance user and consumer safety online. The first action was to get AliExpress to commit to several legally binding commitments to address issues related to advertising and recommender systems. The second action was the publication of preliminary findings which found that AliExpress had breached obligations regarding the spread of illegal products. AliExpress can now respond to the Commission but if the broken rules are confirmed then AliExpress can expect to be fined. The Digital Services Act is a new tool that the EU has to regulate large online platforms. It aims to level the business playing field, protect fundamental rights of users, create a safer digital space, and improve transparency from businesses. AliExpress's pledges: More transparency, safer shopping As part of the pledges made by AliExpress, it will do more to monitor and detect illegal products such as medicines, food supplements, and adult material propagated through hidden links and affiliate programs. To help flag illegal items, AliExpress has promised to improve its notice and action mechanism. Other pledges include enhancements to the internal complaint handling system; more transparency for advertising and recommender systems; better traceability of traders on the platform; and improved data access for researchers. By implementing these rules, the European Commission hopes it can make AliExpress safer for registered and non-registered users by limiting the exposure to illegal content. Deep dive into AliExpress's alleged failures With regards to the preliminary findings, the Commission found that AliExpress had underestimated the risks because it had not allocated enough resources to moderation systems for illegal products. It also found that the company had failed to consistently enforce its penalty policy against those publishing illegal content. The Commission also discovered systemic failures in AliExpress’s proactive content moderation systems that allowed malicious traders to continue to operate or start operating on the platform. AliExpress is designated as a Very Large Online Platform (VLOP) which means it has to meet certain standards set out by the EU. The aforementioned violations are against the quality of operation that the EU expects from VLOPs. The company now has the right to defend itself against the EC’s findings, it can examine the documents and reply in writing, but if the findings are confirmed, AliExpress could face fines and be required to submit an action plan.
    • Author/Neowin... The title is incorrect and misleading... By the official blog post, it's not "indefinitely". There's a clear statement that development continues with a few specific target areas, and a new release date TBD and announced later. If it is later announced to be cancelled or delayed indefinitely, that's another story.
    • I hate Microsoft. My parents almost lost all drive content. As senior citizens they are unable to follow constant "improvements" Microsoft is dropping on its users. My mother's laptop is normally unlocked with a PIN. It is unexplicable for non-It person, that there is some cloud mictosoft account, that has a different password than the Pin and user ID is e-mail, but not necessarily normal Gmail addree, and even if the person knows this email address the password is not the password used for this email account. Just too much of twists. Suddenly her laptop ordered entering "decryption key" before booting. It was miracle we managed to guess email address associated with the PC that was used for Microsoft account. She would had lost everything on the drive. Why are they doing this? Privacy may be important for some people, but data loss is much more important for most of the people.
  • Recent Achievements

    • First Post
      xuxlix earned a badge
      First Post
    • First Post
      Tomek Święcicki earned a badge
      First Post
    • One Year In
      carlitin86 earned a badge
      One Year In
    • Reacting Well
      Peterlll06 earned a badge
      Reacting Well
    • Week One Done
      Peterlll06 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      674
    2. 2
      ATLien_0
      283
    3. 3
      Michael Scrip
      226
    4. 4
      +FloatingFatMan
      192
    5. 5
      Steven P.
      145
  • Tell a friend

    Love Neowin? Tell a friend!