Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Macro to attach all zip files

    It would be appreciated if someone can assist me in witing code to attach all zip files in a folder C:\downloads as well as the sub-folders in this folder

    I will save the code in a template

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    Here is sample code to create an email and attach all the zip files in a folder. Replace the blue lines with values that pertain to your situation.

    HTH,
    Maud

    Code:
    Sub CreateEmail()
    '---------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim outApp As Object
        Dim OutMail As Object
        Dim strbody As String
        Dim Filename As String, strbody As String
        Set outApp = CreateObject("Outlook.Application")
        Set OutMail = outApp.CreateItem(0)
    '---------------------------------------------------
    'CREATE EMAIL BODY
        strbody = "EMAIL BODY GOES HERE"
    '---------------------------------------------------
    'BUILD EMAIL
        On Error Resume Next
        With OutMail
            .To = "JohnDoe@gmail.com"
            .CC = ""
            .BCC = ""
            .Subject = "SUBJECT GOES HERE"
            .Body = strbody
            Path = "C:\Users\Maudibe\Desktop\"
    '--------------------------------------------
    'GET FILENAMES
            Filename = Dir(Path & "*.zip")
            Do While Len(Filename) > 0
                .Attachments.Add Filename
                Filename = Dir
            Loop
            .Display
        End With
    '---------------------------------------------------
    'CLEANUP
        On Error GoTo 0
        Set OutMail = Nothing
        Set outApp = Nothing
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    I certainly forgot about this post. Thanks very much for providing me with the code. Must this be set up in an Outlook Template or can I set this up rather in Excel ?

    Where there is a PM, I am receiving these via email, but not replies to other posts. How do I set this up so I receive emails where someone has replied to one of my posts?

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    I have tested your code. I have a few zip files in a directory called C:\pull as well several zip files in the subdirectory of C:\pull for e,g. C:\pull\BR1TB C:\Pull\Br7TB etc

    Outlook is activated but no zip files are being attached

    I have attached my template. Please test & advise
    Attached Files Attached Files

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    Try changing the path from Path = "C:\pull" to Path = "C:\pull\"

    Maud

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    I had tried changing the path from Path = "C:\pull" to Path = "C:\pull\" when I was testing the macro, but still no files were being attached

    See sample of zip file in C:\pull directory. Not sure why this is not being attached

    It would be appreciated if you could test and advise
    Attached Files Attached Files

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    I think you zipped and uploaded to wrong file.

    I have tested the code and it attaches the zipped files flawlessly. Make sure you have the correct path and it ends with a "\". Also make sure you have zipped files in the directory with the extension .zip
    Maud

    Howard.png

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    I think that although my file is zipped and has a ,zip extension , it looks like it was created in notepad

    This may be the reason, it may not be attaching

    See screen print attached of what the zip file looks like

    I only have an evaluation copy of winzip

    Do you have any idea how I can resolve this?

    It would be appreciated if you can please shed some light on this
    Attached Images Attached Images

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    It should not matter on the format. As long as the extension is .zip (all small), it should attach it. I am suspecting that the file is actually named Book6.zip.txt and not a zipped file at all.

    HTH,
    Maud


    Exactly what is the file Book6.xlsx in the uploaded zipped file of post #6 supposed to be?

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for your input

    Book6.xlsx was nothing more that some sample data. I am not using this for anything. I just wanted to zip the file and attach using your code

    I will install a full version of WinZip & Re-test

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Might want to take a look at 7-Zip. Fully function free version

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    I have amended the code from
    Code:
     .Attachments.Add Filename
    to

    Code:
     .Attachments.Add Path & Filename
    The .zip files in folder "C:\pull now attach in Outlook.

    However the .zip files in the sub-directory of C:\pull for eg C:\pull\BR1TB C:\pull\CPETB etc are not attaching


    It would be appreciated if you would kindly amend the code to attach the .zip files in the sub-folders as well
    Last edited by HowardC; 2015-07-22 at 00:47.

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    You recently helped me to email zip files. It would be appreciated if you would kindly amend the code to exclude zip files containing "backup" in the name of the zip file


    Code:
     Sub CreateEmail()  
    '---------------------------------------------------
    'DECLARE AND SET VARIABLES
        Dim outApp As Object
        Dim OutMail As Object
        Dim strbody As String
        Dim Filename As String
        Set outApp = CreateObject("Outlook.Application")
        Set OutMail = outApp.CreateItem(0)
    '---------------------------------------------------
    'CREATE EMAIL BODY
          strbody = "Hi " & Join(Application.Transpose(Range("D1:D5").Value)) & vbNewLine & vbNewLine
                strbody = strbody & "Attached Please find latest Management Account" & vbNewLine & vbNewLine
                strbody = strbody & "Regards" & vbNewLine & vbNewLine
    '---------------------------------------------------
    'BUILD EMAIL
        On Error Resume Next
        With OutMail
               .to = Join(Application.Transpose(Range("E1:E5").Value), ";")
            .CC = ""
            .BCC = ""
            .Subject = "Accounts"
            .Body = strbody
            Path = "C:\test1l\"
    '--------------------------------------------
    'GET FILENAMES
    '        Filename = Dir(Path & "*.zip")
    '        Do While Len(Filename) > 0
    '            .Attachments.Add Filename
    '            Filename = Dir
    '        Loop
            
        Dim fso, oFolder, oSubfolder, oFile, col As Collection
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set col = New Collection
        col.Add fso.GetFolder(Path)
    
        Do While col.Count > 0
            Set oFolder = col(1)
            col.Remove 1
            For Each oSubfolder In oFolder.SubFolders
                col.Add oSubfolder
            Next oSubfolder
            For Each oFile In oFolder.Files
                           If CStr(oFile) Like "*.zip" Then
                    .Attachments.Add CStr(oFile)
                End If
    
            Next oFile
        Loop
            .Display
        End With
    '---------------------------------------------------
    'CLEANUP
        On Error GoTo 0
        Set OutMail = Nothing
        Set outApp = Nothing
        Set fso = Nothing
    End Sub
    Last edited by HowardC; 2015-08-11 at 11:01.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    You have modified my code with a fair amount of complexity. You have proved yourself worthy to solve this. Look into something like:

    =instr(1,filename,"backup",1)>0

    as a test to see if the filename contains the word "backup"

    HTH,
    Maud

  15. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud


    Thanks for the positive feedback and for your input

    I have modified the code to

    Code:
     If InStr(1, oFile, "Backup", 1) > 0 Then
            
            Else
    
                           If CStr(oFile) Like "*.zip" Then
                    .Attachments.Add CStr(oFile)
                End If

Page 1 of 2 12 LastLast

Posting Permissions

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