Clicky

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

asked 10/20/2011 09:42

PerMagnusStrom's gravatar image

PerMagnusStrom ♦♦


2 Answers:
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
link

answered

omgang's gravatar image

omgang

Simply outstanding :)

Thank you so much!

 - PM
link

answered 2011-10-20 at 06:48:45

PerMagnusStrom's gravatar image

PerMagnusStrom

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×60
×83

Asked: 10/20/2011 09:42

Seen: 273 times

Last updated: 10/20/2011 03:13