Solved

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

Posted on 2014-10-27
12
440 Views
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

1.

Extract the three files to the desktop

2.

Zip, password protect and email the files as an attachment
0
Comment
Question by:Nigel Keith-Walker
  • 5
  • 5
  • 2
12 Comments
 
LVL 9

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.

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
 

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

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?
0
 
LVL 76

Expert Comment

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

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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

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.
0
 
LVL 9

Accepted Solution

by:
Christopher Jay Wolff earned 500 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...
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
 
LVL 9

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.

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
 

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
0
 
LVL 9

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

947 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now