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

In your "Set wb = ActiveWorkbook," can you use the debugger to see if ActiveWorkbook is valid? (BTW, where is ActiveWorkbook defined in the first place?) — Sandy Gettings
– Sandy Gettings, Commented Jan 29, 2017 at 16:54
@SandyGettings I assume Sam added a reference to the type library, "Microsoft Excel <version> Object Library" (VBA Editor, Tools | References). That makes Workbook and similar types visible in Dim statements. It also gives you the ActiveWorkbook global, along with ActiveSheet and the xl* constants. — cxw
– cxw, Commented Jan 30, 2017 at 13:06

cxw · Accepted Answer · 2017-01-29 17:00:32Z

3

I believe

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

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

answered Jan 29, 2017 at 17:00

cxw

17.2k2 gold badges50 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related