Microsoft VBScript runtime error: Type mismatch: 'cells'

Question

i am trying to modify a VBS Script in order for it to send emails when dates from an excel file reach their expiration date.

Long story short, we have a document with our service contracts that contains expiration dates, designated account managers and email addresses.

What i need to do is to create a script that periodically reads the file row by row and send emails to the corresponding addresses when contracts are near their expiration date.

I have found a script that could do this via CDO but i cannot make it to send the lines with the expired dates.

My current issue is that when i try to run the vbs i get: script.vbs(19, 9) Microsoft VBScript runtime error: Type mismatch: 'cells'.

Please help :)

    Set objExcel = CreateObject("Excel.Application")
Function getEmail()
    Dim iCol, iRow
    Dim sEmailBody
    Dim sEmailTo ' the recipient
    iCol = 1 ' column A
    iRow = 1 ' row 2
    Do
        sEmailTo = cells(irow, 1).text
        sEmailBody = sendData(iRow)
        iRow = iRow + 1
    Loop While Not Len(Trim(Cells(iRow, iCol))) = 0
End Function

Function sendData(ByVal iRow) 

    Dim iCol
    For iCol = 1 To 3 ' B=2, K=11
        sendData = sendData & vbCrLf & Cells(iRow, iCol).Text 
    Next
    MsgBox sendData
End Function

Set objExcel = CreateObject("Excel.Application")
Set objEmail = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")
Set objWorkbook = objExcel.Workbooks.Open _
   ("H:\CS\Contracte_suport_2014-06-13.xls")
Set objFlds = objConf.Fields
With objFlds
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "91.195.144.206"
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
  '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoAnonymous
  .Update
End With

Set objEmail.Configuration = objConf
x = 2
Do Until objExcel.Cells(x, 2).Value = ""
    Set objEmail.Configuration = objConf

        objEmail.From = "[email protected]"
    objEmail.To = objExcel.Cells(x, 2)
    objEmail.Subject = "Contracte Support - Notificare Expirare"
    objEmail.Textbody = sendData(2)
    objEmail.Send
    x = x + 1
    If Err Then
         WScript.Echo "SendMail Failed:" & Err.Description
    End If
Loop

objExcel.Quit
objExcel.DisplayAlerts = False

Answer 1

This is your culprit:

Function sendData(ByVal iRow) 

    Dim iCol
    For iCol = 1 To 3 ' B=2, K=11
        sendData = sendData & vbCrLf & Cells(iRow, iCol).Text
    Next
    MsgBox sendData
End Function

In VBScript you cannot access Excel/VBA objects like the Cells collection without having a handle to them. Change this:

sendData = sendData & vbCrLf & Cells(iRow, iCol).Text

into this:

sendData = sendData & vbCrLf & objExcel.Cells(iRow, iCol).Text

and the error should go away.