I have a very sensitive file that I would like it to send me an email if someone has opened it or has has made a change to it. Is this possible? and if it is, is it a resource killer?
I found this code online, can it be modified for workbook?
Please Login or Register to view this content.
Last edited by wherdzik; 04-06-2021 at 11:37 AM.
.
Paste this into a Regular Module :
In the ThisWorkbook module paste this :Please Login or Register to view this content.
When the workbook is opened, the email will be automatically sent to the address you specify in the macro.Please Login or Register to view this content.
Logit, first of all thank you so much.
Is it possible to report who logged in, and what changes were made or does that require significant resources for excel to track this?
Thanks!
.
The following macro should be pasted in the ThisWorkbook module.
You will need to create a sheet named "Tracker". It can be hidden.
The macro tracks changes on all sheets in the workbook.
If you add code to attach the Tracker sheet to the email it will provide all the information you are seeking.Please Login or Register to view this content.
You could also clear the Tracker sheet after the email is sent ... to keep the workbook size at a minimum.
Logit, thank you again. I will test this out. you rock!
You are welcome.
Logit, this works so beautifully so nice! One more annoying question - how do I add code so that it e-mails me, in the body of the email, what user has opened my workbook. I see the application.username but I do not know how to incorporate that in the body.
Thank you Thank you!
.
Ok .. paste this in a Routine Module :
NOTE: Don't forget to enter your email address and email subject in the SendLastRec sub as indicated.Please Login or Register to view this content.
In the TRACKER sheet module, add this macro :
Test it and let me know.Please Login or Register to view this content.
Of course, when you are ready to "go live" change the .DISPLAY to .SEND
Sorry to be a wet blanket, but a clever and unscrupulous user could disable event handlers then open your workbook, in which case Workbook_Open wouldn't run, and you wouldn't get an e-mail. Event handlers work with relatively honest, relatively ignorant users.
WHERDZIK :
Made a correction to my last post. In the TRACKER sheet module ... disregard the last posted code WORKSHEET_CHANGE.
Instead, utilize this :
This should eliminate some problems.Please Login or Register to view this content.
NOTE: If you believe there would be more than 100 changes to the workbook over time .... we'll need to include code in the large macro that
would erase any accumulated data in the TRACKER sheet.
Indeed. Simply denying the macro permission to run, then saving the workbook in the xlsx format, then deleting the original xlsm workbook then re-saving the xlsx workbook in the xlsm format will permanently disable the macro - by deleting it - as well as preventing you getting the email saying who opened it to that end...Originally Posted by hrlngrv
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Wow! Cool thank you guys so much - I will try this code out.
Thank you Thank you!
.
You are welcome. Let us know how things go.
Logit, does the code in post #8 replace everything or is this in addition to the code you have provided previously.
Yes, Post #8 replaces everything.
Take note of the replacement code (Post #10).
I was just thinking another workbook running a macro calling Application.EnableEvents = False just before opening the OP's workbook.
For that matter, disabling networking just before opening the workbook would queue but not send such e-mails. Delete those e-mails from the outbox before reenabling networking, and no e-mail gets sent with no macros needed.
hrlngrv :
Using the macro code I have provided to the OP .. what changes would you make to rectify your concerns ?
To the people who are being a 'wet blanket' (your words not mine), I reckon I could mount an argument that no matter what 'security' you put in, there would be someone, somewhere that could bypass it.
There are plenty of documented cases where the most sophisticated security in the world has been bypassed/hacked.
I don't think the OP is trying to stop international espionage, just trying to reduce the risk of a user inadvertently or even maliciously opening his workbook and changing it?
That being the case I think Logit has a very 'Legit' response.
No-one said Logit's response is not 'Legit'.
What Harlan and I have pointed out is the unreliability of what the OP is trying to achieve. And it's not just about "the risk of a user inadvertently or even maliciously opening his workbook and changing it". Consistent with post #11, even a novice user who innocently disables the macro because they're cautious about letting macros run in a workbook that doesn't apparently need them, or saves the workbook in the xlsx format, then works on the saved version, will easily defeat the OP's scheme.
It is impossible to do anything with VBA to overcome such issues. The kind of auditing the OP seeks can only reliably be achieved at the system level.
What I read was that the OP wanted 'an email if someone has opened it or has has made a change to it', and that is satisfied by the solution offered.
I don't see any better solutions offered here.
What you also don't see here is a reliable solution, just one that gives a false sense of security...
Like you said, there is no solution that is foolproof from a security perspective and you don't really know what the OP's requirements are, you are just surmising.
What I see is a solution that does what the OP said he wanted. You have read more into it, but have offered nothing better. End of.
I'd have thought the OP's requirements are clear enough:
What part don't YOU understand? Logit's solution is not a reliable way of achieving that end.
End of discussion.
Yes - end of discussion, gents, please, as it's going round in circles and adding nothing particularly useful to the thread.
ANY VBA solution should carry a 'health' warning: cavat lector.
Last edited by AliGW; 04-11-2021 at 04:06 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
End of discussion because you said so?
What don't YOU understand by it's as reliable as anything else that's been offered, it satisfies the OP's requirements as stated, and may even be good enough in his user environment?
Instead of telling everyone why the solution is no good, come up with something better.
Please don't ignore post #24. No more, please. This is descending into a rather silly tit-for-tat.
The caveat has been explained - that's it. It's up to the OP now.
The solution provided works within the limitations of Excel, but the wet blankets are right to point these out especially as the OP's states that the file is a sensitive one and their level of experience is unknown.
A further issue might be using Outlook to send the email. User would need to have it and might find the notification emails in their sent items.
If you wanted to do this in a more robust fashion you'd need to monitor the file from an external process and then perform an action if a change was detected.
A way would be to periodically check the modified date and then process a copy of the file to check what had been changed, or write an app using the FileSystemWatcher class.
However this is likely beyond the scope of this forum.
There are also a lot of utilities that claim to provide something similar online.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks