Trying to unzip an attachment with VBA - object not set

[PipBoy808]

Hello,
I have the following code to unzip a .zip file that is attached to an email. My goal is to save this down as the .xls file within and delete the email. The portion of the code dealing with unzipping the attachment is below.

Dim oApp As Object
Dim FSO As Object
Dim FileNameFolder As Variant
Dim StockReportLocation as String
Dim MyDate as String
Dim Filename as string
 
StockReportLocation=\\filepathgoeshere
 
MyDate="YYMMDD"
 
Set oApp = CreateObject("Shell.Application") 'unzip file
 
FileName = Location & MyDate & " NameGoesHere.xls"
 
oApp.Namespace(FileNameFolder).copyhere oApp.Namespace(Atmt.FileName).Items

The final line keeps returning on 'object or with variable not set' error, even though as you can see, I set oApp.
Can anyone spot where I've gone wrong? Thanks!

 

[Deepak]

Loop here !!!

http://www.rondebruin.nl/win/s7/win002.htm

 

[Deepak]

Loop here !!!

http://www.rondebruin.nl/win/s7/win002.htm

Pls choose the appropriate link to unzip as your unzipping software installed.

http://www.rondebruin.nl/win/section7.htm

 

[PipBoy808]

So I took this code right out of that handy link:

Sub UnzipFiles(MyFile As String, MyFolder As Variant)
 
  Dim FSO As Object
  Dim oApp As Object
  Dim Fname As Variant
  Dim FileNameFolder As Variant
  Dim DefPath As String
 
'  Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
'  MultiSelect:=False)
 
  Fname = MyFile
 
  If Fname = False Then
  'Do nothing
  Else
 
  'Destination folder
'  DefPath = "C:\Users\Ron\test\"  '<<< Change path
 
  If Right(MyFolder, 1) <> "\" Then
  MyFolder = MyFolder & "\"
  End If
 
  FileNameFolder = MyFolder
 
  '  'Delete all the files in the folder DefPath first if you want
  '  On Error Resume Next
  '  Kill DefPath & "*.*"
  '  On Error GoTo 0
 
  'Extract the files into the Destination folder
 
  Set oApp = CreateObject("Shell.Application")
  oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).Items 'ERROR: OBJECT OR WITH BLOCK VARIABLE NOT SET
 
 
  On Error Resume Next
 
  Set FSO = CreateObject("scripting.filesystemobject")
  FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
 
  End If
 
End Sub

However I'm still getting the same error on the same line. I'm passing through a valid filename (the .zip file, which at this point has been saved) and the folder in which that file is contained.

Can anyone spot anything I can't?

 

[Deepak]

Hi,

Change this Dim DefPath As String to Dim DefPath As Variant
&
Dim Filename as string in your earlier code to Dim Filename as Variant

Do the changes & try them!!!