Jan 31 2020 01:37 AM
Dear members,
We had working VBA code copying some data from Outlook mail to Excel file. It worked without problems until Excel version was upgraded to Excel 365/2019.
Error is: Compile error: Method or data member not found
Any idea what could be causing the error?
Part of the code, compiler stops at part of the code in RED.
Sub CopyData()
Dim excelWorkbookPath As String
excelWorkbookPath = "C:\Path\data.xlsx"
Dim rpl As Outlook.MailItem
Dim itm As Object
Dim objApp As Outlook.Application
Set objApp = Application
On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set itm = objApp.ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set itm = objApp.ActiveInspector.CurrentItem
End Select
Set objApp = Nothing
If Not InStr(1, itm.Subject, "Some text ", vbTextCompare) > 0 Then
MsgBox ("Some text .")
Exit Sub
End If
If Not FileExists(excelWorkbookPath) Then
MsgBox ("Some text : " & excelWorkbookPath)
Exit Sub
End If
If IsFileOpen(excelWorkbookPath) Then
MsgBox ("Some text ")
Exit Sub
End If
Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")
Dim excelWorkbook As Excel.Workbook
Set excelWorkbook = excelApp.Workbooks.Open(excelWorkbookPath)
Dim ws As Worksheet
Set ws = excelWorkbook.Sheets("Data")
Dim newRowIndex As Integer
newRowIndex = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
CONTINUED....
Jan 31 2020 02:16 AM
Try this and see if that works for you...
newRowIndex = ws.Range("A" & ws.Rows.Count).End(3)(2).Row