Hi,<\/p>\n
I had a VB script in Excel 2010 working but for some reason it has become curropt and i’m getting the run-time 13 error message.<\/p>\n
I have pasted the script below.<\/p>\n
Please can you help?<\/p>\n
Option Explicit<\/p>\n
Private Sub Workbook_Open()
\nDim objExcel
\nDim objWorkbook
\nDim rngStart
\nDim rngEnd
\nDim rngCell
\nDim strHtmlHead
\nDim strHtmlFoot
\nDim strMsgBody
\nDim strMsg
\nDim sFilename
\nDim sPath
\nDim oApp As Object
\nDim oMail As Object
\nDim Mailid As String<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)<\/p>\n
‘open Workbook
\nobjExcel.Workbooks.Open (“C:\\Tracking.xlsx”)<\/p>\n
‘setup basic HTML message header and footer
\nstrHtmlHead = “<html><body>”
\nstrHtmlFoot = “<\/body><\/html>”<\/p>\n
‘setup start of body of message
\nstrMsgBody = “The following Item(s) are recently overdue or are due to be returned in less than 3 days<p \/>”<\/p>\n
‘Worksheet name
\nWith objExcel.Workbooks(“Tracking.xlsx”).Worksheets(“Inventory”)
\n‘set start of date range
\nSet rngStart = .Range(“E2”)
\n‘find end of date range
\nSet rngEnd = .Range(“E” & CStr(objExcel.Application.Rows.Count)).End(-4162)<\/p>\n
‘loop through all used cells in column E
\nFor Each rngCell In .Range(rngStart, rngEnd)
\n‘if date is less than 1 day from today and not more than 3 days in the future then add data to message
\n‘THE BELOW LINE IS THE HIGHLIGHTED LINE WITH THE ERROR
\nIf (rngCell.Value – Int(Date) >= -1 And Not rngCell.Value – Int(Date) >= 3) Then
\n‘add to message – use Item name from column B (offset -3)
\n‘add to message – use Loaned To name from column D (offset -1)
\n‘add to message – use Ststus name from column F (offset 1)
\nstrMsgBody = strMsgBody & “Item: ” & rngCell.Offset(0, -3).Text & ” Loaned To: ” & rngCell.Offset(0, -1).Text & ” is due on: ” & rngCell.Text & ” — Status ” & rngCell.Offset(0, 1).Text & “<br \/>”
\nEnd If
\nNext<\/p>\n
‘Note last reminder time\/date in column A
\nrngEnd.Offset(1, -4) = Now
\nrngEnd.Offset(1, -4).NumberFormat = “dd\/mm\/yy \\a\\t hh:mm”
\nEnd With<\/p>\n
‘put message together
\nstrMsg = strHtmlHead & strMsgBody & strHtmlFoot<\/p>\n
‘Define email id here
\nMailid = “helpdesk@hotmail.co.uk”<\/p>\n
‘create the e-mail object
\nSet oApp = CreateObject(“Outlook.Application”)
\nSet oMail = oApp.CreateItem(0)
\nWith oMail
\n.To = Mailid
\n.Subject = “Loan Equipment”
\n.HtmlBody = strMsg
\n.send
\nEnd With<\/p>\n
‘remove the e-mail object
\nSet oMail = Nothing
\nSet oApp = Nothing<\/p>\n
‘close workbook
\nobjExcel.Workbooks(“Tracking.xlsx”).Close (True)<\/p>\n
‘remove the Excel object
\nobjExcel.Quit
\nEnd Sub<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":2},"question-category":[53],"question_tags":[],"class_list":["post-8140","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/8140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=8140"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=8140"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=8140"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=8140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}