Extract Zipped file from Outlook mail

Question

I'm trying to extract an zip file from my Outlook mail.

Below is my script but its throwing an error object variable or with block variable not set.

On

oApp.NameSpace((FileNameFolder)).CopyHere oApp.NameSpace((Atchmt.FileName)).Items

How I fix it.

Sub Unzip()
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim SubFolder As MAPIFolder
Dim Atchmt As Attachment
Dim FileName As String
Dim msg As Outlook.MailItem
Dim FileNameFolder As String
Dim FSO As Object               'variables for unzipping
Dim oApp As Object

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubFolder = Inbox.Folders("TEST")

For Each msg In SubFolder.Items
   If msg.UnRead = True Then
        If LCase(msg.Subject) Like "*motor*" Then
            For Each Atchmt In msg.Attachments
                If (Right(Atchmt.FileName, 3) = "zip") Then
                MsgBox "1"
                        FileNameFolder = "C:\Users\xxxx\Documents\test\"
                        Set oApp = CreateObject("Shell.Application")
                        oApp.NameSpace((FileNameFolder)).CopyHere oApp.NameSpace((Atchmt.FileName)).Items

                End If
            Next
        End If
    End If
Next
End Sub

oApp.NameSpace((FileNameFolder)).CopyHere oApp.NameSpace((Atchmt.FileName)).Items — kf dhivya, Feb 27 '17 at 06:01
Yes its working in the way you said but i want only the csv file — kf dhivya, Feb 27 '17 at 06:36
Once you extract the file, Kill the zip file. Let me know if you need example on how to kill the zip. — 0m3r, Feb 27 '17 at 06:39

0m3r · Accepted Answer · 2017-02-27T08:36:52.443

Try saving the zip file first then extract it, if you want to delete the zip file then try Kill (zippath & zipname )

    Dim TempFile As String

    For Each msg In SubFolder.Items
       If msg.UnRead = True Then
            If LCase(msg.Subject) Like "*motor*" Then
                For Each Atchmt In msg.Attachments
                    If (Right(Atchmt.FileName, 3) = "zip") Then
'                    MsgBox "1"
                        FileNameFolder = "C:\Temp\Folders\"

                        Debug.Print FileNameFolder ' Immediate Window
                        Debug.Print Atchmt.FileName ' Immediate Window

                        TempFile = FileNameFolder & Atchmt.FileName

                        Atchmt.SaveAsFile TempFile

                        Set oApp = CreateObject("Shell.Application")
                        oApp.NameSpace((FileNameFolder)).CopyHere oApp.NameSpace((Atchmt.FileName)).Items

                        Kill (TempFile)

                    End If
                Next
            End If
        End If
    Next

This Worked like a charm! Thanks for your help. – kf dhivya Feb 27 '17 at 08:50 — kf dhivya, Feb 27 '17 at 08:50

score 0 · Answer 2 · answered Feb 27 '17 at 05:42

0

Declare msg as a generic Object - you can have objects other than MailItem in the Inbox, such as ReportItem or MeetingItem.

answered Feb 27 '17 at 05:42

Dmitry Streblechenko

62,942
4
53
78

What is the new error? What is the exact line of your code that raises that exception? – Dmitry Streblechenko Feb 27 '17 at 06:24
oApp.NameSpace((FileNameFolder)).CopyHere oApp.NameSpace((Atchmt.FileName)).Items - With block variable not set - this is the error – kf dhivya Feb 27 '17 at 06:38
You never check if oApp.NameSpace (first or second) actually returns a valid object. – Dmitry Streblechenko Feb 27 '17 at 13:45

Extract Zipped file from Outlook mail

2 Answers2

Linked