Object variable or with block variable not set error occurs in second iteration of for loop

Question

I've the below code that I'm using in outlook to download an attachment from a list of emails.

The code works fine for the first iteration of the loop, but on the second iteration it errors with Run-time error '91' Object variable or With block variable not set at the step where it is attempting to save the file to a temporary folder on the desktop (i.e. the line wb.SaveAs FileFormat:=51, FileName:=xlNameAndPath).

From reading the documentation here and some testing, it seems that the issue is actually being caused in the first iteration of the loop by wb.close, this sets wb to nothing, which then causes the error in the second iteration.

If i'm right then my question is how to "Respecify a reference for the object variable"?

Sub SaveExcels()

Dim objNS As Outlook.NameSpace: Set objNS = GetNamespace("MAPI")
Dim olFolder As Outlook.MAPIFolder
Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
Dim Item As Object
Dim objAttachments As Outlook.Attachments

For Each Item In olFolder.Items

    If TypeOf Item Is Outlook.MailItem Then

        Dim oMail As Outlook.MailItem: Set oMail = Item

        ' Check it contains an attachment
        Set objAttachments = oMail.Attachments
        lngCount = objAttachments.Count

        ' Check its from the right company
        senderCheck = InStr(oMail.SenderEmailAddress, "company.com")

        ' Check that it is the right email type
        subjectCheck = InStr(oMail.Subject, "TYPE")

        ' Check whether its the latest weeks data
        receivedDate = DateValue(oMail.ReceivedTime)
        todaysDate = DateValue(Now())
        dateDifference = todaysDate - receivedDate

        If lngCount > 0 And senderCheck > 0 And subjectCheck > 0 And dateDifference <= 7 Then

            ' Get the file name
            strFile = objAttachments.Item(1).FileName
            ' Debug.Print strFile

            strFolderpath = "D:\Users\" & Environ("Username") & "\Desktop\temp\"

            ' Combine with the path to the Temp folder.
            strFileIncPath = strFolderpath & strFile
            ' Debug.Print strFile

            ' Save the attachment as a file.
            objAttachments.Item(1).SaveAsFile strFileIncPath

            ' Extract the files into the newly created folder
            Set oApp = CreateObject("Shell.Application")
            oApp.NameSpace(strFolderpath).CopyHere oApp.NameSpace(strFileIncPath).Items

            ' Delete the zip file
            Kill strFileIncPath

            ' Open the excel file
            Dim xlApp As Object
            Set xlApp = CreateObject("Excel.Application")

            xlApp.Application.Visible = True
            xlName = Replace(strFile, ".ZIP", "")
            xlNameTemp = xlName & "_00000.xls"
            xlNameAndPath = strFolderpath & xlName
            Debug.Print xlNameAndPath

            xlApp.Workbooks.Open strFolderpath & xlNameTemp

            Dim wb As Workbook
            Set wb = ActiveWorkbook

            ' Save as unique name and close
            wb.SaveAs FileFormat:=51, FileName:=xlNameAndPath << ERROR

            ' Get rid of the old excel
            Kill strFolderpath & xlNameTemp

            ' Close the workbook
            wb.Close

        End If

    End If

Next

End Sub

Answer 1

I believe

Dim wb As Workbook
Set wb = xlApp.Workbooks.Open(strFolderpath & xlNameTemp)

will do the job, per the docs. (Not tested -YMMV!)