Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How can I send password protected files by email using VBA from MS Access

Posted on 2014-10-27
Medium Priority
Last Modified: 2014-11-01
There is a business requirement to extract data to be loaded into MYOB at another, remote  office.  I have coded the extract (from an SQL 2008 database) which creates three files on a local computer.  

I would like to Zip these flies, password protect them and send them to the remote office.  The data is in the form of a csv file.  It is not very large, so Zipping is not essential.  The data is not hypersensitive, but commercial in confidence.  The office staff need to perform this at close of business, so I would like to automate the process.  But if anybody has a suggestion for using zip, I could make this a two button operation


Extract the three files to the desktop


Zip, password protect and email the files as an attachment
Question by:Nigel Keith-Walker
  • 5
  • 5
  • 2

Expert Comment

by:Christopher Jay Wolff
ID: 40408510
Is it the specific VBA you're looking for, or just the right strategy to put into VBA and you're quite adept at VBA?  I figure if I can do it script, you can do it in VBA.

I wonder what version of Windows you're using.  I'm on 8.1 so cannot verify 7 or XP usage for passwords.  In 8.1 there is no built-in password usage so after ZIP, I select zipped folder in File Manager and I have a pull-down available for Select People.  This incorporates the users individual passwords and permissions for access to the zip folder.  Obviously, can use this method with other OS accessing permissions differently.  I read password protection is available in XP and 7 under the file pull-down in File Manager.  Although having it in 7 is circumspect since the user quoted in the excerpt below lists himself as a 7 user, but does not specifically say so in the thread.   I also understand if you use the password protection available in XP and 7 and forget the password, you're out of luck.  This make the permissions method more appealing, since admin can recover.

Do you use third party password protection?

Here is a link with a thread that pretty much sums up this password usage, and mentions most of the popular third party password protection products.  An excerpt follows.


Using Passwords in Windows
And here is a blurb from Microsoft on Windows 7 which counters my investigations above.


Useful at all?

Author Comment

by:Nigel Keith-Walker
ID: 40409646
I had not thought of using permissions.  The sending office uses win7, I develop in Win 8.1.  But I do use WinZip, which has replaced the MS standard option in Windows explorer, so I have problems following through the standard Zip instructions.

I had been contemplating using the WinZip processes to zip, encrypt and email the three files.  Training the staff would require a one page cheat sheet.  

Alternatively, I am competent with VBA, and it would be neat to provide a one button process to extract, zip, encrypt and send.
LVL 76

Expert Comment

by:David Lee
ID: 40409851
Do you have WinZip's command-line utility installed?  If so, then you can call it from VBA, zip the files, password protect the zip file, then create an email attach the zip file, and send it off.
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

by:Nigel Keith-Walker
ID: 40409934
No.  It will require an update from ver 14 to ver 17+ which is not an issue.  Do you know if I have to have the Pro version or can I just update the standard version?
LVL 76

Expert Comment

by:David Lee
ID: 40410284
That I don't know.

Expert Comment

by:Christopher Jay Wolff
ID: 40411059
After I get caught up with work, I may have time to look into this, but it sounds like your one button solution might be nice in powershell.  Threw some quick links in for you to review.  If you haven't used powershell it can be very helpful for this type of thing.  Please let us know what you think.

extract data

zip em up, see David's answer and follow-up comment about David's answer, don't miss other links

two links about encrypting in Powershell


email em.
Launch powershell window, type...
Get-Help Send-MailMessage -full

and you will see...
Example sending an attachment in email with a Powershell command.

Author Comment

by:Nigel Keith-Walker
ID: 40411765
I have not used powershell yet.  The extract has been written and tested in VBA, and is within the app that the backend user spends most of their day.  So the start point will be that three files, which could be in the same folder, are to be encrypted and sent.

I am in Australian time, so have just got up and quickly viewed the links.  I will need to think through the two options after breakfast.

Author Comment

by:Nigel Keith-Walker
ID: 40412627
I have taken advantage of an upgrade offer which arrived from WinZip yesterday to upgrade my version 15 std WinZip to WinZip 19 Pro for $AUD29-95.  This has allowed me to download the add-in for command Line support.  I am reading up the doco on using the command window.

Accepted Solution

Christopher Jay Wolff earned 2000 total points
ID: 40415957
Hello again.  I hope your WinZip upgrade is helpful.  Maybe that's the best solution for now.  Always seems good to get the latest stuff.  In case you still have interest in finding out what Powershell can do to assist you with admin tasks, I put together some more detailed info below.  The idea being to do it all with Powershell and then just double-click a .bat file to launch it or some way that you prefer.  Even if you choose not to use it for this task, you might want to gain some familiarity with it.

An introduction to Powershell from Microsoft...

First time users should probably do the following.  In my 8.1 RT Windows, I open windows folder, then open System32 folder (are you on 64 bit?), then open WindowsPowerShell folder, then open v1.0 folder, locate the Powershell.exe file.  Right-click on it and select pin to taskbar.  Once on the taskbar, right-click on it and select "Run as administrator."  When window opens it looks very much like the DOS command prompt window.

At the prompt type...

There you can read about updating the help files.  These are very useful.  To update, so that your Get-Help cmdlet has the latest info type...

Powershell ships on new systems with ExecutionPolicy set to "restricted" so scripts don't run for security purposes.  If you haven't used it, this will have to be changed to enable the running of scripts that you write.  
If you want to read about it, this information is explained here...

First see where you ExecutionPolicy is set by typing...
Get-ExecutionPolicy -list

If it is not set to RemoteSigned then you need to do one of the following which is excerpted from the link above...

    To change your execution policy, type:
        Set-ExecutionPolicy -ExecutionPolicy <PolicyName>

    For example:

        Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

    To set the execution policy in a particular scope, type:

        Set-ExecutionPolicy --ExecutionPolicy <PolicyName> -Scope <scope>

    For example:

        Set-ExecutionPolicy RemoteSigned -Scope CurrentUser

    A command to change an execution policy can succeed but
    still not change the effective execution policy.

    For example, a command that sets the execution policy for
    the local computer can succeed but be overridden by the
    execution policy for the current user.

So I typed...
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Another application is "cipher.exe" which is located in your System32 folder.  While it is limited on some versions of Windows and therefore won't allow encryption, you're probably not running a "Home Edition" or the like, and can encrypt with it. I am not able to test it on my RT 8.1.  It's usage and syntax is found here...

Lastly, sending the email with attachment.  This two line script below works for me.  It's hard-coded just to see if it works.  It does, although the names have been changed to protect the innocent.

Send-MailMessage -SmtpServer smtp.mail.yahoo.com -port 587 -credential $c -from "Chris@yahoo.com" -to "Chris@hotmail.com" -subject "Testing a script." -body "This is a simple test message to check things out." -attachment C:\users\ChristopherJay\desktop\shelf_export.csv -UseSSL

Open in new window

Whichever way you go, I hope you like it.  Cheers.

Expert Comment

by:Christopher Jay Wolff
ID: 40416097
Also, I read somewhere that if you use cipher.exe to encrypt a .zip file, Windows automatically unzips it first, then encrypts it.  So if that info is accurate, you may have to encrypt first then zip, but it may not apply at all if using WinZip.

Here are a couple links with zip scripts in Powershell.  The second link to zip approach looks pretty good but from 2006.  The EE link is this year.



and several approaches here...

Author Closing Comment

by:Nigel Keith-Walker
ID: 40416962
Hi Christopher

This morning I had a discussion with the two business partners involved with the two offices involved.  We have decided that WinZip will be the application used to transfer the data.  Initially we will use a cheat sheet to use WinZip the files to the other office.  

Your links provide the opportunity to enhance the transfer process.  Initially we need to determine that the data will allow for a quicker reconciliation in MYOB.  Once we have established that this is an essential part of the COB routine, then I can justify extra enhancement.

I will be out of the office for the next two weeks.  I do not want you to be frustrated with a lack of response, so I am allocating the points now.   As a developer, I want to investigate how to use the powershell to send a command line to WinZip.  Perhaps I will do this in my own time.  I am sure that any other businesses have a similar scenario, and there are multiple possible solutions.

Regards from Australia

Expert Comment

by:Christopher Jay Wolff
ID: 40417083
Thank you NIgel.

I see I pasted the same EE link in twice above by mistake.  Oh well, easy to search again.

Sounds like a great plan.  Yes, so many ways to do things.  May it go smoothly for you.

Cheers from where we had snow last night, Michigan, USA.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question