[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

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

I have an email that I get a couple times a week that has a zip attachment.  The subject always has some of the same key words.  I would like to setup a rule either within Outlook or an add-in that would see that message come in, open the zip file, extract the 1 file I need (although extrating all the files would be fine) to a network drive (overwritting the current file(s)) and then delete (or move) the email.  While this is not hard to do manually, there are times when that file comes in but I can't get to it for hours or even a couple days and that could cause some business issues.
Does anyone have any ideas?
0
bruceleroy
Asked:
bruceleroy
  • 21
  • 20
1 Solution
 
David LeeCommented:
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.
0
 
bruceleroyAuthor Commented:
My PC has winrar 3.71.  I'm on a windows XP Pro machine if that matters
TIA
0
 
David LeeCommented:
I'm not familiar with winrar.  Can you give me the command line required to unzip a file?
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
I don't see an attachment.
0
 
bruceleroyAuthor Commented:
Sorry, I apparently forgot to put in a description after I attached the file.
unrar.txt
0
 
David LeeCommented:
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

Open in new window

0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
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.
0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
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.
0
 
bruceleroyAuthor Commented:
It's set to No Security Check for Macros.  My understanding is that means all Macros will run.
0
 
David LeeCommented:
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

Open in new window

0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
Can you take a screenshot of what you see when you open the code editor in Outlook and post it here?
0
 
bruceleroyAuthor Commented:
Here you go.
blank.JPG
0
 
David LeeCommented:
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.
0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
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.

0
 
bruceleroyAuthor Commented:
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
0
 
David LeeCommented:
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?
0
 
bruceleroyAuthor Commented:
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

Open in new window

code.JPG
0
 
David LeeCommented:
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
0
 
bruceleroyAuthor Commented:
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

Open in new window

0
 
David LeeCommented:
"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.  
0
 
bruceleroyAuthor Commented:
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

Open in new window

0
 
David LeeCommented:
"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
0
 
bruceleroyAuthor Commented:
That didn't make any difference.  It still copies the zip to the correct folder, but does not unzip.
0
 
David LeeCommented:
Is unzip.exe in the folder t:\facline?
0
 
bruceleroyAuthor Commented:
yes
0
 
David LeeCommented:
Try changing line 11 to this
objShell.Run "cmd /C t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP", 7, False

Open in new window

0
 
bruceleroyAuthor Commented:
No change.  I did not see a CMD prompt open up if I should have.
0
 
David LeeCommented:
Ok.  Change line 11 to the line below.  Does Notepad open?
objShell.Run "notepad.exe", 7, False

Open in new window

0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
Try changing False on line 11 to True.  Does that make any difference?
0
 
bruceleroyAuthor Commented:
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

Open in new window

0
 
David LeeCommented:
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.
0
 
bruceleroyAuthor Commented:
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.

0
 
David LeeCommented:
The command line was missing a parameter to tell it where to unzip the files to.  Change line 11 to

objShell.Run "t:\facline\unzip.exe -o t:\facline\FFPLUS.ZIP -d t:\facline", 7, False
0
 
bruceleroyAuthor Commented:
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.
0
 
David LeeCommented:
You're welcome.  Glad I could help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 21
  • 20
Tackle projects and never again get stuck behind a technical roadblock.
Join Now