Solved

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

Posted on 2014-10-27
12
419 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That I don't know.
0
 
LVL 9

Expert Comment

by:Christopher Jay Wolff
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Nigel Keith-Walker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

728 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

11 Experts available now in Live!

Get 1:1 Help Now