excel - Microsoft VBScript runtime error: Type mismatch: 'cells' -

excel - Microsoft VBScript runtime error: Type mismatch: 'cells' -

i trying modify vbs script in order send emails when dates excel file reach expiration date.

long story short, have document our service contracts contains expiration dates, designated business relationship managers , email addresses.

what need create script periodically reads file row row , send emails corresponding addresses when contracts near expiration date.

i have found script via cdo cannot create send lines expired dates.

my current issue when seek run vbs 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 ' recipient icol = 1 ' column irow = 1 ' row 2 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 icol = 1 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 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 set objemail.configuration = objconf x = 2 until objexcel.cells(x, 2).value = "" set objemail.configuration = objconf objemail.from = "in@datanets.ro" objemail.to = objexcel.cells(x, 2) objemail.subject = "contracte back upwards - notificare expirare" objemail.textbody = senddata(2) objemail.send x = x + 1 if err wscript.echo "sendmail failed:" & err.description end if loop objexcel.quit objexcel.displayalerts = false

this culprit:

function senddata(byval irow) dim icol icol = 1 3 ' b=2, k=11 senddata = senddata & vbcrlf & cells(irow, icol).text next msgbox senddata end function

in vbscript cannot access excel/vba objects cells collection without having handle them. alter this:

senddata = senddata & vbcrlf & cells(irow, icol).text

into this:

senddata = senddata & vbcrlf & objexcel.cells(irow, icol).text

and error should go away.

excel visual-studio vbscript cdo.message