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

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

1.

Extract the three files to the desktop

2.

Zip, password protect and email the files as an attachment
Nigel Keith-WalkerContractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher Jay WolffWiggle My Legs, OwnerCommented:
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.

http://www.sevenforums.com/general-discussion/209452-how-do-i-password-protect-folder-file.html

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

http://windows.microsoft.com/en-us/windows/password-protect-files-folders#1TC=windows-7

Useful at all?
0
Nigel Keith-WalkerContractorAuthor Commented:
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.
0
David LeeCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nigel Keith-WalkerContractorAuthor Commented:
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?
0
David LeeCommented:
That I don't know.
0
Christopher Jay WolffWiggle My Legs, OwnerCommented:
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
http://joshcodev.wordpress.com/2014/02/10/powershell-extract-data-from-a-database/



zip em up, see David's answer and follow-up comment about David's answer, don't miss other links
https://social.technet.microsoft.com/Forums/scriptcenter/en-US/8f98f06e-79fe-401c-92f0-a548d4304dcc/how-to-zip-files-and-folders-using-powershell



two links about encrypting in Powershell
http://infoworks.tv/encrypt-files-and-folders-using-powershell-and-efs/

http://www.amandhally.net/2013/03/18/powershell-script-create-an-encrypted-folder-using-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.
0
Nigel Keith-WalkerContractorAuthor Commented:
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.
0
Nigel Keith-WalkerContractorAuthor Commented:
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.
0
Christopher Jay WolffWiggle My Legs, OwnerCommented:
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...
http://technet.microsoft.com/en-us/library/hh857337.aspx


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...
Get-Help

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...
Update-Help

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...
http://technet.microsoft.com/en-us/library/hh847748.aspx

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...
http://technet.microsoft.com/en-us/library/cc771346.aspx

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.

$c=Get-Credential
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher Jay WolffWiggle My Legs, OwnerCommented:
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.

http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28407343.html

http://mow001.blogspot.com/2006/01/msh-out-zip-function.html

and several approaches here...
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28407343.html
0
Nigel Keith-WalkerContractorAuthor Commented:
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
0
Christopher Jay WolffWiggle My Legs, OwnerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.