- In the extension bar, click the AdBlock Plus icon
- Click the large blue toggle for this website
- Click refresh
- In the extension bar, click the AdBlock icon
- Under "Pause on this site" click "Always"
- In the extension bar, click on the Adguard icon
- Click on the large green toggle for this website
- In the extension bar, click on the Ad Remover icon
- Click "Disable on This Website"
- In the extension bar, click on the orange lion icon
- Click the toggle on the top right, shifting from "Up" to "Down"
- In the extension bar, click on the Ghostery icon
- Click the "Anti-Tracking" shield so it says "Off"
- Click the "Ad-Blocking" stop sign so it says "Off"
- Refresh the page
- In the extension bar, click on the uBlock Origin icon
- Click on the big, blue power button
- Refresh the page
- In the extension bar, click on the uBlock icon
- Click on the big, blue power button
- Refresh the page
- In the extension bar, click on the UltraBlock icon
- Check the "Disable UltraBlock" checkbox
- Please disable your Ad Blocker
- Disable any DNS blocking tools such as AdGuardDNS or NextDNS
- Disable any privacy or tracking protection extensions such as Firefox Enhanced Tracking Protection or DuckDuckGo Privacy.
If the prompt is still appearing, please disable any tools or services you are using that block internet ads (e.g. DNS Servers, tracking protection or privacy extensions).
Question
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...
...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 = "<HTML><BODY><p>Dear "&email1&",<br/><br/>An account has been created.<br/><br/><b>ID:</b><br/>Your account ID is: <b>"&sAMAccountName&"</b><br/>Your Email Address is: <b>"&sAMAccountName&"@domain.com</b><br/>Account Creation Date is <b>"&created&"</BODY></HTML>" 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"" <myself@example.com>;" ' .Item(cdoSendUserReplyEmailAddress) = """Another"" <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), "<HTML>") 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 "</table>" objRS.Close set objRS = Nothing objConn.Close set objConn = NothingAny help is greatly appreciated.
Link to comment
https://www.neowin.net/forum/topic/993630-vb-script-to-send-emails-based-off-of-a-sql-table/Share on other sites
1 answer to this question
Recommended Posts