VBS script help

reefoid · 25 Jun 2013 at 12:27

I'm trying to create a script that will email the results of a mysql query using VBS. I don't have any experience of VBS but have found a script online that will do what I want. However, I only want the script to email results if rows are actually returned. If the query returns zero rows then I want the script to end before emailing anything. Here is the script I'm using (some details redacted for security), can anyone advise on how I can stop the mail sending if zero rows are returned.

Thanks.

Code:

option explicit
dim dbHost, dbName, dbUser, dbPass, outputFile, email, emailFrom, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbHost = "localhost"
dbName = "xxxx"
dbUser = "xxxx"
dbPass = "xxxx"
outputFile = "c:\inetpub\wwwroot\dev\bs\batch\Scheduled Works.csv"
email = "xxxx"
emailFrom = "xxxx"
  subj = "Scheduled Works"
  body = "Body text"
  smtp = "xxxx"
  smtpPort = 25
sqlStr = "SELECT tasks.task_ref AS REF, clients.client_name AS CLIENT, tasks.task_address AS ADDRESS, tasks.task_description AS DESCRIPTION, DATE_FORMAT(tasks.task_next_date,'%d/%m/%Y') AS DUE_DATE FROM tasks, clients WHERE tasks.task_client = clients.client_ref AND DATEDIFF (tasks.task_next_date, CURRENT_DATE) <= 14;"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = subj
	objMessage.From = emailFrom
	objMessage.To = email
	objMessage.TextBody = "There are scheduled works due, please see the attached spreadsheet for details."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

VBS script help

reefoid

reefoid