Solved

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

Posted on 2009-05-20
41
495 Views
Last Modified: 2012-05-07
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
Comment
Question by:bruceleroy
  • 21
  • 20
41 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 24433931
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
 

Author Comment

by:bruceleroy
ID: 24434161
My PC has winrar 3.71.  I'm on a windows XP Pro machine if that matters
TIA
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24434995
I'm not familiar with winrar.  Can you give me the command line required to unzip a file?
0
 

Author Comment

by:bruceleroy
ID: 24435248
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24436846
I don't see an attachment.
0
 

Author Comment

by:bruceleroy
ID: 24437725
Sorry, I apparently forgot to put in a description after I attached the file.
unrar.txt
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24456312
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
 

Author Comment

by:bruceleroy
ID: 24475680
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24475746
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
 

Author Comment

by:bruceleroy
ID: 24475780
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24475848
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
 

Author Comment

by:bruceleroy
ID: 24475969
It's set to No Security Check for Macros.  My understanding is that means all Macros will run.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24476132
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
 

Author Comment

by:bruceleroy
ID: 24476190
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24476314
Can you take a screenshot of what you see when you open the code editor in Outlook and post it here?
0
 

Author Comment

by:bruceleroy
ID: 24476368
Here you go.
blank.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24476433
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
 

Author Comment

by:bruceleroy
ID: 24476488
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24476573
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
 

Author Comment

by:bruceleroy
ID: 24476604
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 76

Expert Comment

by:David Lee
ID: 24476683
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
 

Author Comment

by:bruceleroy
ID: 24476885
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24476968
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
 

Author Comment

by:bruceleroy
ID: 24477063
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24477281
"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
 

Author Comment

by:bruceleroy
ID: 24477376
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24488852
"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
 

Author Comment

by:bruceleroy
ID: 24492047
That didn't make any difference.  It still copies the zip to the correct folder, but does not unzip.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24492171
Is unzip.exe in the folder t:\facline?
0
 

Author Comment

by:bruceleroy
ID: 24492176
yes
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24499358
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
 

Author Comment

by:bruceleroy
ID: 24501975
No change.  I did not see a CMD prompt open up if I should have.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 24502782
Ok.  Change line 11 to the line below.  Does Notepad open?
objShell.Run "notepad.exe", 7, False

Open in new window

0
 

Author Comment

by:bruceleroy
ID: 24502811
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24504545
Try changing False on line 11 to True.  Does that make any difference?
0
 

Author Comment

by:bruceleroy
ID: 24504568
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24504595
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
 

Author Comment

by:bruceleroy
ID: 24504715
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
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 24504886
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
 

Author Comment

by:bruceleroy
ID: 24504911
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
 
LVL 76

Expert Comment

by:David Lee
ID: 24505073
You're welcome.  Glad I could help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now