Link to home
Start Free TrialLog in
Avatar of Lambel
Lambel

asked on

Use VBA to Zip and password protect files

I need to automate a process that generates a report for a each customer in a list, saves it as a pdf file, then in order to provide security, it zips the file and password protects it.  Finally, it emails it out, with a second email containing the password getting sent right after the first one.
I've got the program generating the pdf, zipping it up and emailing it out.  I haven't been able to figure out how to password protect it.
If it would be easier to password protect a pdf instead, that could be an option.

Thanks for any help,
Lynn
Avatar of Bill Prew
Bill Prew

What tool or procedure are you using to ZIP the file?

~bp
Avatar of Lambel

ASKER

I'm using the shell program. I've attached the code.  Let me know if you need more info.


Function ZipAddFileToZip(ZipFilePath As Variant, FilePath As Variant) As Boolean
    Dim objApp As New Shell32.Shell
    Dim cnt As Long
    Dim killt As Double
    
    Set objApp = CreateObject("Shell.Application")
    cnt = objApp.NameSpace(ZipFilePath).Items.Count + 1
    
    objApp.NameSpace(ZipFilePath).CopyHere GetShortFileName(FilePath)
    DoEvents
    killt = Timer + 120 'If this thing can't finish in 2 minutes kill it
    Sleep 5000
    
    
    Do Until objApp.NameSpace(ZipFilePath).Items.Count >= cnt
        Debug.Print objApp.NameSpace(ZipFilePath).Items.Count
        If killt < Timer Then Exit Do
        Sleep 1000
    Loop
    


End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of shaydie
shaydie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As far as I know there is no way to set a password when creating a ZIP file in VBscript using the built in Windows ZIP capabilities as you are.  You will either need to leverage a .net ZIP library of some sort, or an external program like 7Zip, or WinZip.

~bp
Avatar of Lambel

ASKER

After searching for a way to password protect with the shell program and finding nothing, it looks like I'll have to revert back to 7zip. I wish I had known yesterday that the shell program doesn't have password functionality.  Live and learn, I guess.   I am glad to have your example and appreciate it.  Thanks much!
Lynn