• 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

    • Time to find another tech news site I think. This is beyond ridiculous.
    • Microsoft Store is getting improved recommendations, deeper Windows integration, and more by Taras Buria Microsoft announced several new features coming to the Microsoft Store on Windows 11. The company revealed that 250 million customers use the Microsoft Store each month. With the latest updates, Microsoft is improving the experience with better recommendations, search improvements, deeper Windows integration, Copilot, and more. The store's Home Page is getting personalized recommendations with suggestions based on your recent activities, what is trending in your region, and the recent deals. Microsoft says this change will bring more meaningful and relevant content. Search is getting smarter, and it now uses additional information when ranking apps. It is "intent-aware," and considers additional aspects like app updates, ratings, language-specific nuances, and more. In the United States, the Microsoft Store now has a Copilot button at the bottom of the screen. Clicking it lets you ask questions about an app or game, or compare two products. Speaking of Copilot, apps with AI-powered experiences now have a badge indicating that certain apps work better on Copilot+ PCs. Other changes to the Microsoft Store include a new Discover More section with related apps and deeper Windows integration. The latter lets you find apps in the Microsoft Store using Windows Search, and the "Open With" dialog now includes additional recommendations from the Microsoft Store. Finally, Microsoft made multiple under-the-hood improvements to boost performance (the app launches twice as fast as it did six months ago) and improve installation reliability. In addition to new features coming to the Microsoft Store, the company reminded users that some popular productivity apps are now available in the Store. They include Notion, Perplexity, Docker, and Day One. You can read more about all those changes in a post on the official Windows Blogs website. Last month, at Build 2025, Microsoft announced more improvements for the Microsoft Store, so stay tuned for those.
    • OpenAI exposes secret propaganda campaigns tied to multiple countries by David Uzondu Back in February, OpenAI shut down accounts that were busy developing Chinese surveillance tools aimed at the West. These tools were designed to snoop on social media, look for anti-China sentiment and protests, and report back to Chinese authorities. Now, OpenAI has announced it has disrupted even more shady operations, and not just those tied to China. In a report released Thursday, the company detailed how it recently dismantled ten different operations that were misusing its artificial intelligence tools. One of the China-linked groups, which OpenAI called "Sneer Review," used ChatGPT to churn out short comments for sites like TikTok, X, and Facebook. The topics varied, from U.S. politics to criticism of a Taiwanese game, where players work against the Chinese Communist Party. This operation even generated posts and then replied to its own posts to fake real discussions. What is particularly interesting is that the group also used ChatGPT to write internal performance reviews, describing how well they were running their influence campaign. Another operation with ties to China involved individuals posing as journalists and geopolitical analysts. They used ChatGPT to write social media posts and biographies for their fake accounts on X, translate messages from Chinese to English, and analyze data. OpenAI mentioned that this group even analyzed correspondence addressed to a U.S. Senator. On top of that, these actors used OpenAI's models to create marketing materials, basically advertising their services for running fake social media campaigns and recruiting intelligence sources. OpenAI also disrupted operations, probably originating in Russia and Iran. There was also a spam operation from a marketing company in the Philippines, a recruitment scam linked to Cambodia, and a deceptive job campaign that looked like something North Korea might orchestrate. Ben Nimmo, from OpenAI's intelligence team, noted the wide range of tactics and platforms these groups are using. However, he also said these operations were mostly caught early and did not manage to fool large numbers of real people. According to Nimmo, "We didn't generally see these operations getting more engagement because of their use of AI. For these operations, better tools don't necessarily mean better outcomes."
    • Long ago, I was in a networking class on a lab computer. The guy next to sarcastically told me to SHIFT+DELETE the C:\Windows folder. I said that I was sure Windows wouldn't allow such a thing (Windows 2000), and would either totally block the action or give some kind of dire warning. I was so confident that I tried it...not only was I wrong, but it didn't even give the standard "are you sure" warning, just went to town. I pressed cancel as quick as I could, but it was too late, shortly after, the system blue-screened and never booted again. I had to stay late and reinstall Windows for the teacher, but that ended up being a good thing, had great repour with him for the rest of the year, even got to help him get Active Directory setup in his lab.
  • Recent Achievements

    • One Year In
      survivor303 earned a badge
      One Year In
    • Week One Done
      jbatch earned a badge
      Week One Done
    • First Post
      Yianis earned a badge
      First Post
    • Rookie
      GTRoberts went up a rank
      Rookie
    • First Post
      James courage Tabla earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      419
    2. 2
      snowy owl
      182
    3. 3
      +FloatingFatMan
      182
    4. 4
      ATLien_0
      176
    5. 5
      Xenon
      137
  • Tell a friend

    Love Neowin? Tell a friend!