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 :)
PerMagnusStromAsked:
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

        'Declarations
    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\omgang.zip"  '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)
    objZip.Close
 
    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
            DoEvents
        Loop

    Next

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

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

Thank you so much!

 - PM
0
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.