Is there a way to automate zip extractions in Outlook 2007?

Hi, bruceleroy.

This is possible with a simple bit of scripting and a rule.  I can post code for doing it.  All I need to know is what unzipping software you are using.

My PC has winrar 3.71.  I'm on a windows XP Pro machine if that matters
TIA

I'm not familiar with winrar.  Can you give me the command line required to unzip a file?

I've never used command line with winrar.  I've attached the unrar.exe commands and switches.

I have no problem installing winzip or pkunzip if those help, I'd just prefer to not have to make them my default.

I don't see an attachment.

Sorry, I apparently forgot to put in a description after I attached the file.
unrar.txt

Here's the code.  Follow these instructions to use it.

1.  Start Outlook
2.  Click Tools > Macro > Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes
9.  Close the VB Editor
10. Create a rule that fires for these messages
11. Set the rule's action to "run a script" and select this script as the one to run

Sub UnzipAttachment(Item As Outlook.MailItem)
    Dim olkFile As Outlook.Attachment, objShell As Object, strFilename As String
    For Each olkFile In Item.Attachments
        'Change the file name on the following line'
        If Right(LCase(olkFile.FILENAME)) = "zip" Then
            Set objShell = CreateObject("WScript.Shell")
            'Change the path the file will be saved to on the following line'
            strFilename = "C:\" & olkFile.FILENAME
            olkFile.SaveAsFile strFilename
            'You may need to adjust the command line'
            objShell.Run "unrar " & strFilename & " C:\SomeFolder", 7, False
        End If
    Next
    Set olkFile = Nothing
    Set objShell = Nothing
End Sub

Select allOpen in new window

Thanks for writting this up.  It isn't working, but that could be because I did something wrong.  When I go to step 3, I didn't have anything to expand.  I was able to go to "insert | Module" and then pasted that code.  I changed the name of the zip file to reflect the file we get.  I also change the "c:\somefolder" to the UNC path of where it is to go.  Should UNC work or do I need to map a drive?  I didn't change the "unrar " command as I don't know if that is needed or not.

Having no experience with unrar I'm not sure if the command is right either.  My first recommendation is to try the command line from a command prompt and make sure it works.  Also, make sure that the path to the unrar executable is in the PATH variable.

If I'm reading the code correctly, does line 8 as written save the attachment to the root of C?  Assuming so, that part is not working.

Yes, unless you changed the path it should be saving to the root of the C: drive.  Are macros enabled in Outlook?  Here's how to check

1.  Click Tools > Trust Center
2.  Click Macro Security
3.  Set Macro Security to "Warnings for all macros"
4.  Click OK
5.  Close Outlook
6.  Start Outlook.

It's set to No Security Check for Macros.  My understanding is that means all Macros will run.

They should, but that's not always the case.  Add the code below to the ThisOutlookSession module, then close and restart Outlook.  You should get a popup dialog-box that says "Macros are working".  Please let me know if that happens.

Private Sub Application_Startup()
    MsgBox "Macros are working"
End Sub

Select allOpen in new window

I don't have a "ThisOutlookSession module".  When I open the VB editor, I get the menu and tool bars, but nothing else.  Everything else is just gray.  So I did a insert New Module and put that code in it.  I save it as vbaproject.OTM (module 5).  The left drop down says General.  The right drop down says application_startup.  Then I have your 3 lines of code.  I save, exit Outlook, make sure outlook is closed via task manager and restart Outlook.  I don't get the msgbox.

Can you take a screenshot of what you see when you open the code editor in Outlook and post it here?

Here you go.
blank.JPG

Something is seriously wrong with the that file.  I recommedn that you close Outlook, delete the file (Vbaproject.OTM) and let Outlook create a new one.

I closed outlook and did a search for vbaproject.otm and none were found.

Thanks for the help, but this is taking up way too much of your time for something that I can do manually.

The file is in a hidden folder.  You'll find it at C:\Documents and Settings\<username>\Application Data\Microsoft\Outlook.  Be sure to configure Windows Explorer so it can see hidden files and folders.

No worries on the time.  I wouldn't be here doing this if I didn't enjoy it and have the time to spare.

ok, I found the file (I don't hide files/folders so I don't know why search didn't find it).  I closed outlook, deleted the file and restarted outlook.  It shows the same as the screenshot as above.
Thanks

Maybe all the panes have been turned off.  If you click View and select Project Explorer then click View and select Code, does anything show up?

That was it.  I did your 3 line code and I got the popup saying macros are working now.  I retried the original code you sent and that's still not saving the attachment to the root of C.  I've posted a screenshot of what the VB Editor looks like now.  My outlook rule is listed here as code.  The script I tell it to run is the only script it lets me pick.

Apply this rule after message arrives
with Facline in the subject
and which has an attachment
and on this machine only
run Project1.UnzipAttachment

Select allOpen in new window

code.JPG

The code isn't saving the file because of the filename on line #5.  The code is only looking at the right most three characters of the filename.  The idea was to see if the file ends with zip.  Comparing those three characters to "fflus.zip" is never going to produce a match.  If that's the only filename you ever want this to work for, then change line #5 to

        If LCase(olkFile.FILENAME) = "ffplus.zip" Then

Ah.  Line 4 says to change the file name on the following line.  What file name is that?
It is now saving the attachment to the root of C, but not unzipping it to the UNC.  I'll play around with that part a bit.

Sub UnzipAttachment(Item As Outlook.MailItem)
    Dim olkFile As Outlook.Attachment, objShell As Object, strFilename As String
    For Each olkFile In Item.Attachments
        'Change the file name on the following line'
        If LCase(olkFile.FileName) = "ffplus.zip" Then
            Set objShell = CreateObject("WScript.Shell")
            'Change the path the file will be saved to on the following line'
            strFilename = "C:\" & olkFile.FileName
            olkFile.SaveAsFile strFilename
            'You may need to adjust the command line'
            objShell.Run "c:\program files\winrar\unrar " & strFilename & " \\bbc-apps01\Apps\facline", 7, False
        End If
    Next
    Set olkFile = Nothing
    Set objShell = Nothing
End Sub

Select allOpen in new window

"Line 4 says to change the file name on the following line"
That may have been something left over from a previous question.  I cannibalized the basic code from another question that did something similar.

"I'll play around with that part a bit."
I'm not familiar with winrar and the documentation for the command-line interface wasn't very helpful.  I searched online for examples, but couldn't find anything useful.  Cobbled the command together as best I could.  

Apparently winrar is not going to work as the command line only does RAR and not zip.  I found a free unzip command line utility.  I can run the Outlook rule and it now saves to the network location (mapped as my T drive) but it does not unzip.  Here is the code I now have.
If I navigate to T:\facline via command prompt and type "unzip.exe -o FFPLUS.ZIP" {Enter} it works.  The -o is the overwrite switch for this program.
Additionally, when running the script, Windows would ask if I wanted to open or save unzip.exe.  I told it to open and it didn't work.  I ran it again and told it to never ask again, to always open and it still didn't work.  I then verified that if I run the command myself that it does work.  Do you think if I created a .bat file of that 1 line then had your code run that .bat file that it might make a difference?

Sub UnzipAttachment(Item As Outlook.MailItem)
    Dim olkFile As Outlook.Attachment, objShell As Object, strFilename As String
    For Each olkFile In Item.Attachments
        'Change the file name on the following line'
        If LCase(olkFile.FileName) = "ffplus.zip" Then
            Set objShell = CreateObject("WScript.Shell")
            'Change the path the file will be saved to on the following line'
            strFilename = "t:\facline\" & olkFile.FileName
            olkFile.SaveAsFile strFilename
            'You may need to adjust the command line'
            objShell.Run "t:\facline\unzip.exe -o FFPLUS.ZIP", 7, False
        End If
    Next
    Set olkFile = Nothing
    Set objShell = Nothing
End Sub

Select allOpen in new window

"Do you think if I created a .bat file of that 1 line then had your code run that .bat file that it might make a difference?"
I doubt if that's it.  Try putting a full path on the file name.  For example

objShell.Run "t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP", 7, False

That didn't make any difference.  It still copies the zip to the correct folder, but does not unzip.

Is unzip.exe in the folder t:\facline?

yes

Try changing line 11 to this

objShell.Run "cmd /C t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP", 7, False

Select allOpen in new window

No change.  I did not see a CMD prompt open up if I should have.

Ok.  Change line 11 to the line below.  Does Notepad open?

objShell.Run "notepad.exe", 7, False

Select allOpen in new window

Yes, notepad opened.  I guess that means the unzip.exe does not like being run in a script?  Because if I use a cmd prompt and enter that part of line 11 manually it works.

Try changing False on line 11 to True.  Does that make any difference?

I tried both of these as line 11 with no change.

objShell.Run "cmd /C t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP", 7, True
 
objShell.Run "t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP", 7, True

Select allOpen in new window

Can you send me a link to the site you downloaded unzip from?  If I had a copy of the same program, then I could troubleshoot this more effectively.

http://stahlforce.com/dev/index.php?tool=zipunzip
you can use unzip -z to get a list of all the modifires
In doing some more testing, I can put the zip file there, then go to the command prompt and do the unzip fine.  If I then in the CMD prompt I hit UP ARROW {ENTER} it tells me the archive name, but does not unzip.  It will not unzip it a second time.  If I delete the zip and replace it with another zip file, it works 1 time and then stops.  Maybe this isn't a good unzip program to use.

And again, I don't have a problem using a different program such as pkzip or winzip if those help.

That certainly did it.  I truely thank you for all your assistance with this.  This just goes to show why I'm not a programmer and have no desire to be one.

You're welcome.  Glad I could help.