Consulting

Results 1 to 7 of 7

Thread: Automatically download, unzip, and rename attachment

  1. #1

    Automatically download, unzip, and rename attachment

    I have seen a few code snippets around about downloading attachments and saving them but I am still confused by the outlook objects as I mostly work in Access and am fairly new to VBA. I want to make a macro that can download a zipped attachment received from a specific user (probably set that up in the rules right?) and then unzip and rename attachment.
    OK, the general setup should be something like this?
    Declare outlook objects and strings
    open the email inbox where the attachment is located
    set the path to down load
    Use Windows API? to run the unzip program using a command line for extraction
    set a rename for the file

    This will be for an excel file that I want the name to always be the same as I will have another excel file linked to it which will then be linked to an access database.
    Thanks for any help!!

  2. #2
    you can use a windows shell object to unzip
    you can use the application_newmail event for it to run automatically

    try like
    Private Sub Application_NewMail()
    Dim i As MailItem, f As Items, a As Attachment
    Set f = GetNamespace("Mapi").Folders("Personal Folders").Folders("Inbox").Items
    Set i = f.item(f.Count)  'newest arrival
    If Not i.SenderName = "mymate" Then Exit Sub  ' ignore from everyone else
    dest = "c:\temp\xxx\"
    xlfile = "myxl.xls"  ' change to suit
    For Each a In i.Attachments
        If LCase(Right(a.FileName, 4)) = ".zip" Then
            a.SaveAsFile dest & a.FileName
            Set sh = CreateObject("shell.application")
            Set n = sh.NameSpace(dest & a.FileName)
            For Each fil In n.Items
                If LCase(Right(fil.Name, 4) = ".xls") Then
                    sh.NameSpace(dest).copyhere fil, 4
                    Kill dest & xlfile   ' required as name will not overwrite
                    Name dest & fil.Name As dest & xlfile
                    Exit For
                End If
            Next
        End If
    Next
    
    End Sub
    this is as a whole untested and may contain typos or code errors, change all filenames, extentions and folders to suit, if for any reason the filename of the zipped xls file still exists within the dest folder an overwrite dialog will be raised by the shell object, as coded only the first .xls in the zip file will be extracted (others will be ignored), this can obviously be changed if required, if the xl file is open (or referrenced) in excel an error may occur at trying to kill or rename
    if multiple emails arrive at the same time i am not sure if every email fires the newmail event individually or as a batch, if not it would be easy to work around that

  3. #3
    Cool!!! I'm going to work on this later today and will post how it goes. I didn't realize you could use a shell object like that so easily. I guess I was thinking more in terms of recognizing a window. I'm really only up to writing small pieces of code for events in my database and even then I usually need some sort of starter piece so this really helps a lot. Thanks so much!!!

  4. #4
    I did get this to work and it does download the attachment but I can't add it to the rules and It doesn't actually unzip the attachment in the directory. Do you have any ideas how to make that work. I'm trying to find information about shell objects so I can try and figure this out (because I really want to learn this) but a nudge in the right direction would help too!

  5. #5
    and It doesn't actually unzip the attachment in the directory
    i fully tested the unzipping part, so what does happen? wrong result? error? nothing?
    show the full code as you have modified it, did you change the destination folder path to a valid folder on your system?

    but I can't add it to the rules
    if the code is in the newmail event as above, it should work whenever new mail arrives, why do you need in rules?

  6. #6
    I did change the path and I walked through it to see how it worked on the file (which was a zip) with one file inside that was an .xls. It did copy the Zip file to the specified folder and killed/updated the file but I never saw any excel files extracted.
    Also, when it unzips the file is there a way to specify a name for the file no matter what the name is in the zip file? There must be a rename object for the shell object right? (I'm really just guessing!). Also, the company who will be sending this email may be sending it to more then one person and they may have this running so I wonder if I need a check to make sure something else is not using the file. Plus, my database will update daily when opened and when requested by the user (just in case the email is late) and I am using DAO so it would only be connected when updating.
    Let me know what you think. Thanks so much for the help so far!!NewMailMacro.zip
    I also couldn't get the code to display here for some reason so I sent it to a text file and zipped it (said I had a forbidden word!).

  7. #7
    Also, when it unzips the file is there a way to specify a name for the file no matter what the name is in the zip file?
    No

    There must be a rename object for the shell object right?
    yes doverb for any method in the right click menu

    xlfile = "*.xls" ' change to suit
    wildcard will not work in the code here, needs to be the name you want the the xl file to be

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •