bmsande
asked on
Execute vbscript code only if results of query exist
I have a vbscript that currently emails a list of accounts that are past due. I want to schedule the report to run every day, however, i don't want to receive the email if no results are found. Can someone help me build in some logic to skip the email processing if no results are returned?
Thanks in advance
Thanks in advance
'Declare Constants
Const CDO_SCHEMA = "http://schemas.microsoft.com/cdo/configuration/"
Const CDO_MAIL_HEADER = "urn:schemas:mailheader:"
'Method used to send mail
Const CDO_SEND_USING_REMOTE = 2 'Send using Remote SMTP Server
'Security method used on remote SMTP server
Const CDO_ANONYMOUS = 0 'Use no authentication
Const CDO_BASIC = 1 'Use the basic (clear text) authentication
Const CDO_NTLM = 2 'Use the NTLM authentication
'Delivery Status Notifications
Const cdoDSNDefault = 0 'No DSN commands are issued
Const cdoDSNNever = 1 'No DSN commands are issued
Const cdoDSNFailure = 2 'Return a DSN if delivery fails
Const cdoDSNSuccess = 4 'Return a DSN if delivery succeeds
Const cdoDSNDelay = 8 'Return a DSN if delivery is delayed
Const cdoDSNSuccessFailOrDelay = 14 'Return a DSN if delivery succeeds, fails, or is delayed
'Set method of sending
strSendMethod = CDO_SEND_USING_REMOTE
'Remote SMTP Server Settings
strSmtpServer = "10.30.1.10" 'Name or IP of SMTP Server
intSmtpPort = 25 'SMTP Server Port; typically 25
intSmtpTimeout = 60 'Number of seconds to try establishing a connection to the SMTP Server
strAuthenticationMethod = CDO_ANONYMOUS
'SMTP Server Authentication - IF BASIC or NTLM; NOT needed for ANONYMOUS
strUserName = ""
strPassword = ""
strUserSSL = False 'True if SMTP Server uses SSL; False if Not
'Message Settings
strTo = "test@acme.com" 'Separate multiple addresses with a semi-colon (;)
strCC = ""
strBCC = ""
strFrom = "events@acme.com"
strSubject = "The following events are past due"
strBodyType = "TEXT" 'Message Body Format = HTML or TEXT -Text is Plain Text
strAttachment = "" 'Attachment Path i.e. C:\Temp\File.txt
strDSNotification = cdoDSNDefault 'Delivery Status Option Change as needed
'WScript.Echo "Connecting to database..."
'Connect to database & select all from Table
Set objDB = DBConnect()
Set oRS = objDB.Execute("SELECT DATE_FORMAT(eventdate, '%m.%d.%Y') as eventdate, eventtype, DATE_FORMAT(setuptime, '%r') as SETUPTIME, DATE_FORMAT(starttime, '%r') as STARTTIME, DATE_FORMAT(stoptime, '%r') as STOPTIME, downpaid, paidinfull from event where eventdate < date_add(curdate(), INTERVAL 14 DAY) and paidinfull='false' order by eventdate" )
'Dump Records from Table
strOutput = "EVENT DATE" & vbtab & "EVENT TYPE" & vbtab & vbtab & "SETUP TIME" & vbtab & "START TIME" & vbtab & "STOP TIME" & vbcrlf
nRec = 0
ors.MoveFirst
Do Until ors.EOF
strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab & ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
oRS.MoveNext
Loop
ors.close
'WScript.Echo "Sending Email..."
SendEmail strOutput
'WScript.Echo "Script Finished"
'This function sets up DB Connection using specified DSN
Function DBConnect
Set objDB = CreateObject("ADODB.Connection")
objDB.Open "vbrs"
Set DBConnect = objDB
End Function
Sub SendEmail(strBody)
'Create Objects
Set objConfig = CreateObject("CDO.Configuration")
Set objEmail = CreateObject("CDO.Message")
'Prepare email configuration
With objConfig.Fields
.Item(CDO_SCHEMA & "sendusing") = strSendMethod
.Item(CDO_SCHEMA & "smtpserver") = strSmtpServer
.Item(CDO_SCHEMA & "smtpserverport") = intSmtpPort
.Item(CDO_SCHEMA & "smtpconnectiontimeout") = intSmtpTimeout
.Item(CDO_SCHEMA & "smtpauthenticate") = strAuthenticationMethod
If.Item(CDO_SCHEMA & "smtpauthenticate") <> 0 Then
.Item(CDO_SCHEMA & "sendusername") = strUsername
.Item(CDO_SCHEMA & "sendpassword") = strPassword
.Item(CDO_SCHEMA & "smtpusessl") = strUserSSL
End If
.Update
End With
'Create email and send
With objEmail
Set.Configuration = objConfig
.To = strTo
If strCC <> "" Then
.CC = strCC
End If
If strBCC <> "" Then
.BCC = strBCC
End If
.From = strFrom
.Subject = strSubject
If strBodyType = "HTML" Then
.HTMLBody = strBody
ElseIf strBodyType = "TEXT" Then
.TextBody = strBody
End If
If strAttachment <> "" Then
.AddAttachment strAttachment
End If
If strDSNotification <> 0 And strDSNotification <> 1 Then
.Fields(CDO_MAIL_HEADER & "disposition-notification-to") = strFrom
.Fields(CDO_MAIL_HEADER & "return-receipt-to") = strFrom
.DSNOptions = strDSNotification
.Fields.update
End If
.Send
End With
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, it seems to be an issue with my original code as well. If no records are selected, then it throws that error. any ideas?
One thing you might try (not the cleanest solution mind you):
-saige-
'Dump Records from Table
strOutput = "EVENT DATE" & vbtab & "EVENT TYPE" & vbtab & vbtab & "SETUP TIME" & vbtab & "START TIME" & vbtab & "STOP TIME" & vbcrlf
' Did not see this variable being used, assuming it is a record count.
nRec = 0
ors.MoveFirst
Do Until ors.EOF
strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab & ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
nRec = nRec + 1
oRS.MoveNext
Loop
ors.close
'WScript.Echo "Sending Email..."
' Assuming that nRec is 1 simply because the loob above may run through once before failing out. 2 would
' be true in the event of 1 record returned since nRec would be equal to 2 as the loop would fail out on the
' second loop through.
If nRed >= 2 Then
SendEmail strOutput
End If
HTH,-saige-
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dismiss my last, try this instead:
-saige-
If Not ors.BOS Then ors.MoveFirst
If Not ors.EOF Then
Do Until ors.EOF
strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab & ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
oRS.MoveNext
Loop
ors.close
'WScript.Echo "Sending Email..."
SendEmail strOutput
End If
HTH,-saige-
Typo:
-saige-
If Not ors.BOF Then ors.MoveFirst
If Not ors.EOF Then
Do Until ors.EOF
strOutput = strOutput & ors.fields.item("eventdate") & vbtab & ors.fields.item("eventtype")&vbtab &vbtab & ors.fields.item("SETUPTIME")& vbtab &ors.fields.item("STARTTIME") & vbtab &ors.fields.item("STOPTIME")& vbcrlf
oRS.MoveNext
Loop
ors.close
'WScript.Echo "Sending Email..."
SendEmail strOutput
End If
HTH,-saige-
ASKER
"Either BOF or EOF is true, or the current record has been deleted. Requested Operation requires a current record."