VBS: polling a print server for low toner

Hey!

so we have alot of printer in remote sites. (this will be Ireland and GB) so to keep a stock of toners is not possible and we have people who run out now and again so i made a toner rodering system to automate it but they still didnt use it. so next logical step remove the User Error. I made a vbs script that would run once a day at 4pm and send a email to our ordering company with the printer details and the depot address. Great! only i have hit some snaggs. It works as it but you get the printer name (which the DB uses as a link to get the depot address) and the Address. you dont know what make or model or toner is needed. Also i was thinking to stop repeat emails for the same toner to add a counter so once 1 email is sent it waits x days till send another. But if i could get the serial of the toner I would know when it was changed which would be more accurate.

Anyway here is my code and any ideas or info would be good. excuse the mess its in as i ahve been playing with it alot.

Option Explicit

'On Error Resume Next

Set objshell = Wscript.CreateObject ("Wscript.Shell")

Dim strBody
Dim colErrorPrinters
Dim WshShell
Dim objPrinter
Dim strProdList
Dim OsType
Dim ADSysinfo
Dim objWSHShell
Set WshShell = CreateObject("WScript.Shell")
Dim retCode
Dim strComputer, objWMI, colItems, objItem, objName, InstalledOS, objEmail, objNetwork, objShell
Dim connectionString
Dim sql
Dim cn
Dim cmd
Dim rs
Dim Name
Dim testinfo


Set objNetwork = WScript.CreateObject("WScript.Network")
Set objWSHShell = Wscript.CreateObject("Wscript.Shell")
Set ADSysInfo = CreateObject("ADSystemInfo")

set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")


strComputer = "print server"
Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colErrorPrinters = objWMI.ExecQuery ("Select * From Win32_Printer WHERE DetectedErrorState = 5")
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Intranet.mdb;"
cn.open connectionString
' Associate connection object with command object
cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql


If colErrorPrinters.Count > 0 Then
	strBody = "The following printers are indicating low toner:" & vbCrLf




For Each objPrinter in colErrorPrinters
		strBody = strBody & objPrinter.Name & vbCrLf
'testinfo = "sc-la-p"
testinfo = Left(objPrinter.name, Len(objPrinter.name) - 3)

testinfo = testinfo & "%"

'wscript.echo testinfo


sql = "select * from Depots Where Printer1 LIKE '" & testinfo & "' "
	
	' Open connection

cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql
set rs = cmd.execute

' Enumerate each row in the result set
while rs.EOF <> true and rs.BOF <> True
	' Using ordinal
	'wscript.echo "Result " & rs("Name") &vbCrLf & rs("Address1") &vbCrLf & rs("Address2") &vbCrLf & rs("Town") &vbCrLf & rs("Postcode")
		strBody = strBody & vbCrLf & rs("Name") &vbCrLf & rs("Address1") &vbCrLf & rs("Address2") &vbCrLf & rs("Town") &vbCrLf & rs("Postcode") & vbCrLf & " " & vbCrLf & " "

	rs.movenext
wend
Next
' Close Connection
cn.Close	
	

	

	strBody = strBody & vbCrLf & "Lots of love Jonny"


Set objEmail = CreateObject("CDO.Message")
		objEmail.Configuration.Fields.Item _
   			("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
		objEmail.Configuration.Fields.Item _
   			("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
       			"email ip" 
		objEmail.Configuration.Fields.Item _
   				("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
		objEmail.Configuration.Fields.Update
		objEmail.From = "helpdesk"
		objEmail.To = "helpdesk"
		objEmail.Subject = "Printers Toner Needed" 
		objEmail.Textbody = strBody
		objEmail.Send

						'wscript.echo "Email Sent"
						'wscript.echo (strBody)
	Else
					'wscript.echo "Email Not Sent"
		End If


'-----------------------------