VBA - Outlook to Excel: method rows of object _global failed

Question

I am running a script on every email that hits my outlook. The script needs to open a specified excel document and save the senders name & address, subject and date. I am getting Run-time error 1004:method rows of object _global failed for some of the mails I receive, not all. I have specified the line in the code where the error occurs. Is it a referencing issue?

Public Sub CoupaQueries(MItem As Outlook.MailItem)

Dim objOutlook As Outlook.Application

PersonName = MItem.SenderName
PersonAddress = MItem.SendUsingAccount
PersonSubject = MItem.Subject
PersonDate = MItem.ReceivedTime

Dim objExcel As Excel.Application
Dim wks As Excel.Worksheet
Dim wkb As Excel.Workbook

Set objExcel = New Excel.Application

objExcel.Workbooks.Open ("C:\Users\a222012\Desktop\CoupaQueries.xlsx")
objExcel.Visible = True
Set wkb = objExcel.ActiveWorkbook
Set wks = wkb.Sheets("Sheet1")

'Error occurs on the next line

wks.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = PersonName
wks.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = PersonAddress
wks.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = PersonSubject
wks.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = PersonDate

objExcel.ActiveWorkbook.Save
objExcel.Quit

End Sub

Answer 1

since your client application is Outlook, you don't have Excel application object model implicitly referenced so you have to explicitly reference every Excel object (say, Worksheet object) to access its members (say, its Rows property)

the With object - End With syntax can come in help to both avoid such errors and give you more grasp (and consciousness) over object model handling, where once you reference whatever object then you have all its members (properties, methods, enumerations) a simple dot (.) away, like for instance:

Public Sub CoupaQueries(MItem As Outlook.MailItem)
    Dim objExcel As Excel.Application
    Dim objOutlook As Outlook.Application '<--| not needed, since you' are in Outlook its object model is implicitly referenced
    Dim PersonName As String, PersonAddress As String, PersonSubject As String, PersonDate As Date

    With MItem '<--| reference passed MItem object
        PersonName = .SenderName
        PersonAddress = .SendUsingAccount
        PersonSubject = .Subject
        PersonDate = .ReceivedTime
    End With        

    Set objExcel = New Excel.Application '<--| get a new instance of Excel
    objExcel.Visible = True '<--| not necessary
    With objExcel.Workbooks.Open("C:\Users\a222012\Desktop\CoupaQueries.xlsx").Sheets("Sheet1") '<--| get and reference an instance of "Sheet1" sheet of wanted workbook
        With .Cells(.Rows.Count, 1).End(xlUp) '<--| reference referenced sheet column A first empty cell after last not empty one
            .Offset(1, 0).Value = PersonName
            .Offset(1, 1).Value = PersonAddress
            .Offset(1, 2).Value = PersonSubject
            .Offset(1, 3).Value = PersonDate
        End With
        .Parent.Save '<--| save parent object of currently referenced object: being this latter a worksheet, its parent object is the workbook it belongs to
    End With
    objExcel.Quit
    Set objExcel = Nothing '<--| release application variable
End Sub

---

Furthermore, should you have to run this macro inside a loop over many mail items, then you could get one Excel reference before starting the loop, use it throughout it and close once you're done:

Sub main()
    Dim iMail As Long, nMails As Long
    Dim MItem As Outlook.MailItem
    Dim objExcel As Excel.Application '<--| declare an Excel Application object in the main sub

    Set objExcel = New Excel.Application '<--| get a new Excel application instance before starting the loop

    For iMail = 1 To nMails

        ...
        ... code to get ith mail
        ...

        CoupaQueries MItem, objExcel '<--| pass your routine the current mail item and the already gotten Excel application

    Next

    objExcel.Quit '<--| quit Excel once the loop has finished
    Set objExcel = Nothing '<--| release application variable
End Sub


Public Sub CoupaQueries(MItem As Outlook.MailItem, objExcel As Excel.Application)
    Dim PersonName As String, PersonAddress As String, PersonSubject As String, PersonDate As Date

    PersonName = MItem.SenderName
    PersonAddress = MItem.SendUsingAccount
    PersonSubject = MItem.Subject
    PersonDate = MItem.ReceivedTime

    With objExcel.Workbooks.Open("C:\Users\a222012\Desktop\CoupaQueries.xlsx").Sheets("Sheet1") '<--| get and reference an instance of "Sheet1" sheet of wanted workbook
        With .Cells(.Rows.Count, 1).End(xlUp) '<--| reference referenced sheet column A first empty cell after last not empty one
            .Offset(1, 0).Value = "PersonName"
            .Offset(1, 1).Value = PersonAddress
            .Offset(1, 2).Value = PersonSubject
            .Offset(1, 3).Value = PersonDate
        End With
        .Parent.Save '<--| save parent object of currently referenced object: being this latter a worksheet, its parent object is the workbook it belongs to
    End With
End Sub

Answer 2

Simplify the code to let the error show where it occurs, for example,

Dim R As Long

R = Wks.Cells(Rows.Count, 1).End(xlUp) + 1
Wks.Cells(R, 1).Value = PersonName
Etc

I suspect your formula for setting R. This looks more reasonable:-

R = Wks.Cells(Rows.Count, 1).End(xlUp).Row + 1