How can I compress a file using VB-code in MS Access

I have a file that is generated in my MS Access db. I want to be able to send the same file by email (also using VB-code); however, Outlook doesn't like the file format, so the file needs to be compressed to *.zip before sending it. The file is accessible from the same network drive as the db itself.

Can the compression be done using VB-code in MS Access?

Code samples deeply appreciated :)
Who is Participating?
omgangIT ManagerCommented:
Here's sample code to add files to a zip archive.  If Outlook doesn't like the file type now I believe it still won't like it even when compressed in a zip archive.  You may need to change the file extension.
OM Gang

Public Function AddFilesToZip()
On Error GoTo Err_AddFilesToZip

    Dim objFSO As Object, objZip As Object, objShell As Object
    Dim objFolder As Object, objFile As Object
    Dim sngStart As Single
    Dim strPath As String, strZip As String
    strPath = "C:\test\"   'Path to read files from
    strZip = "C:\test2\"  'Output zip file
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objZip = objFSO.CreateTextFile(strZip)
    objZip.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objFSO.GetFolder(strPath)
        'loop through files - adding them to the zip
    For Each objFile In objFolder.Files
        objShell.NameSpace("" & strZip).CopyHere objFile.PATH
        sngStart = Timer
        Do While Timer < sngStart + 2


        'destroy object variables
    Set objFile = Nothing
    Set objFolder = Nothing
    Set objShell = Nothing
    Set objZip = Nothing
    Set objFSO = Nothing
    Exit Function

    MsgBox Err.Number & ", " & Err.Description, , "Error in Function AddFilesToZip of Module Module7"
    Resume Exit_AddFilesToZip
End Function
PerMagnusStromAuthor Commented:
Simply outstanding :)

Thank you so much!

 - PM
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.