This VBS code is for a scheduled job, it will check a the location of an excel file, check the contents of the file for a value and if the value is found to send an email out.
Code below – Change Bold values:
Const xlValues = -4163
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“Excelfilelocation.xls”)
Set objWorksheet = objWorkbook.Worksheets(“Worksheetname“)
Set objRange = objWorksheet.UsedRange
Set objTarget = objRange.Find(“Valuetofind“)
objExcel.Visible = False
If Not objTarget Is Nothing Then
‘Wscript.Echo objTarget.AddressLocal(False,False)
strFirstAddress = objTarget.AddressLocal(False,False)
End If
Do Until (objTarget Is Nothing)
Set objTarget = objRange.FindNext(objTarget)
strHolder = objTarget.AddressLocal(False,False)
If strHolder = strFirstAddress Then
Exit Do
End If
‘Wscript.Echo objTarget.AddressLocal(False,False)
Loop
If objTarget = “Valuetofind” Then
Const sTO = “Emailaddresstosendto“
Const sSUBJ = “Emailsubject“
Const sMAIL_BODY = “EmailBodytext“
Const sATTACHMENT_1 = “Filelocationtoattachtoemail.xls”
Call vSendMail(sTO, sSUBJ, sMAIL_BODY, sATTACHMENT_1, sATTACHMENT_2)
‘=============================================================================================
Sub vSendMail(sSEND_TO, sSUBJECT, sBODY, sATTACHMENT1, sATTACHMENT2)
On Error Resume Next
‘Declare variables
Dim sMail
Dim oObject
Set oObject = CreateObject(“Outlook.Application”)
Set sMail = oObject.CreateItem(0)
sMail.to = sTO
sMail.Subject = sSUBJECT
sMail.Body = sBODY
sMail.Attachments.Add(sATTACHMENT1)
sMail.Send
‘oObject.Quit
Set sMail = Nothing
Set oObject = Nothing
End Sub
End If
objExcel.Quit
‘########End of file