Encrypting Reports

[Agnelo Fernandes]

I have an access database which exports out 'reports' in the form of 'excel'
and attaches it to a mail. Since the data in excel is confidential it needs
to be encrypted.
Is there anyway that we can encrypt the exported report automatically so
that when it attaches to the mail its already encrypted

 

[Clifford Bass]

Hi Agnelo,

You could use automation. I am not able to test this completely so
good luck.

Dim appExcel As New Excel.Application
Dim wkbExported As Excel.Workbook
Dim strFile As String

strFile = Environ("USERPROFILE") & "\Desktop\C Table.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C", strFile

Set wkbExported = appExcel.Workbooks.Open(strFile)
With wkbExported
.Password = "MyPassword"
.Save
.SendMail "(e-mail address removed)", "Encrypted Spreadsheet"
.Close
End With
Set wkbExported = Nothing
appExcel.Quit
Set appExcel = Nothing

Note that there are lots of Office password cracking programs out there
so this may not be very secure. It may be better to invest in a number of
digital certificates for the sender(s) and the recipients. Then with the
recipients public keys you can have your e-mail program encrypt the entire
contents of the messages, including the attachments.

Hope this helps,

Clifford Bass

 

[Philip Herlihy]

I have an access database which exports out 'reports' in the form of 'excel'
and attaches it to a mail. Since the data in excel is confidential it needs
to be encrypted.
Is there anyway that we can encrypt the exported report automatically so
that when it attaches to the mail its already encrypted

It's a long time since I've done any of this, but Outlook has
sophisticated programming facilities and the capacity to use digital
signatures for encryption. You can use VBA in Access to open an
instance of Outlook, create and send a message and presumably you could
encrypt the message in the process if you have a certificate available.
You could alternatively program Outlook to open Access to obtain the
report. The only downside is that the last time I tried this Outlook
insisted on introducing a 5 second delay and mandatory gui confirmation
as a way of preventing it being used for spam. There's a toolkit called
Outlook Redemption which gets around this. May be different in later
versions - you can use .net in recent Office applications.

Phil, London