+ Reply to Thread
Results 1 to 22 of 22

Email Question without user knowing

  1. #1
    Registered User
    Join Date
    11-12-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    48

    Email Question without user knowing

    Just a quick question out of curiosity..

    I have a fairly complex Workbook with UserForms and formulas. We use it at work for automated data collection. I have all the sheets set to be xlVeryHidden and password protection on my code, etc. Anyway, we have this guy at work that knows a little about excel and he uses a password erase macro every shift and goes into the sheets and changes stuff around. He hasn't edited the code, so I don't think he is getting into the actual VBA part. What I'm curious to know is, say I have a sheet called October and it's xlVeryHidden. Is it possible to have an email sent to me without using Outlook and without him knowing if he does his little trickery and messes with the book and unhides October? If it's possible to get any extra information, such as time, his username or PC name, etc, that would be great, but since he works midnight shift and is the only person on site, it would be very easy to narrow it down based on the timing.

    Thanks ahead of time...

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Email Question without user knowing

    Have you tried to save your file as password protected, so he cant even open it to begin with?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-12-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Email Question without user knowing

    Can't do it.. Everyone has to have access to upload their data...

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Email Question without user knowing

    you can set the P/W to read-only...he can get in and do whatever he wants, just cant save it, give the PW to those who need it...alternatively, shoot him!!

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Email Question without user knowing

    hmm another option...if users need to upload data to that file, give them their own (simple basic) file and then reference the data to your file?

  6. #6
    Registered User
    Join Date
    11-12-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Email Question without user knowing

    Yeah I suggested the gun thing actually when we first started having the problems. And the reference thing isn't a bad idea... I just think my boss wants to catch him because I guess it used to happen before and the guy was written up for it.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Email Question without user knowing

    I will post this thread to the other seniors and see if they can come up with anything (my bet is they can)

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    In Simple No You Can’t Catch him…

    For example assume that you have an hidden Worksheet called sheet1 in Book1 using ExcelVeryHidden Property. But note that clearing the contents don’t need the user to select the sheet.

    Add the below code in another workbook like book2 and Keep the focus on Book1 and run the code. This will clear the contents of Book1-Sheet1 without selecting the sheet. Even you can’t catch him by adding On Change Event when the code written in the below way to turn off the event…

    Please Login or Register  to view this content.
    Last edited by :) Sixthsense :); 10-21-2013 at 02:33 AM.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  9. #9
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Email Question without user knowing

    how about "track changes" under review tab just don't highlight the changes on screen. just hoping that he don't notice it.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Email Question without user knowing

    I do this by writing a text file from the excel workbook and then I have a macro in my outlook application to check the folder and e-mail any text files to myself.

    Obviously this only works over a network.
    You can get username from application.username

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    Quote Originally Posted by vlady View Post
    how about "track changes"
    Nice suggestion but the workbook needs to be Shared.

    At the same time shared workbook needs to be unshared in case of changing the very hidden property of a sheet or Unprotecting the sheet / Adding / Editing the VBA code in that case all the users should be kicked out and it needs the sole ownership.
    Last edited by :) Sixthsense :); 10-21-2013 at 03:55 AM.

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    @ yudlugar,

    Do you think it will work when the user turned off the events as described in post #8?

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Email Question without user knowing

    Think it through ... if the sheet is password protected and he used a macro or eraser tool to remove the password, he'd have to put the original password back on or you'd notice. That's not as easy as it sounds in my experience. It's one thing to take it off, another to leave it the way you found it. Unless he actually knows the password, in which case, change it. So, if it was password protected when you left work and not protected when you got back, and he's the only one in, he must have done it.

    However, as Sixthsense says, you can clear/change the data on a hidden/very hidden sheet without being able to see it ... and equally, you can view what is in it with a simple link to the sheet/cell. What's worse, you can make the sheet visible without being able to see it if you know it's there and what it's called.

    And the sheet must be there for a reason, so I guess you refer to it in formulae and he's going to know a) that it's there and b) which cells and ranges you use.

    So, pretty negative so far ... what to do!? Some suggestions:

    1. On the visible sheets, lock and hide the cells with formulae; unlock the cells that users key data in.
    2. Password protect the visible data input sheets.
    3. Change the sheet names of the hidden sheets and change the sheet code names to something less obvious.
    4. Refer to the sheet code names in your code rather than the visible (changed) sheet name.
    5. Maybe set up and use Named Ranges to refer to the hidden sheets ... and hide the Named Ranges. That's really belt and braces because he shouldn't be able to see the formulae anyway.
    6. Lock the VBA Project for viewing with a password; again, not hard to break if you have the right tool.
    7. Use a workbook sheet change event to monitor changes to hidden reference sheets and write details of the changes to a text file on the network; user name, date, time, worksheet name, cell address, new value. You could exclude changes to sheets where users are allowed to make changes.
    8. Audit his PC for unauthorised software; this can be done remotely.
    9. Lock USB ports so that he cannot load or use applications on a USB stick.
    10. Look at Ron de Bruin's web site for ways to send emails without using Outlook so you won't leave a trail in his outbox if you do send emails when a change is made. You could do this and write to a text file.

    Some ideas. Failing that, promote him and recruit him to the IT support team and get him working for you


    Regards, TMS
    Last edited by TMS; 10-21-2013 at 04:01 AM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  14. #14
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Email Question without user knowing

    Sixthsense,

    I was just looking at the "send e-mail without knowing" bit I guess.

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    Nice suggestions TMShucks

    In all your suggestions I like the sheet protection since it sounds Great Suggestion.

    Regarding the Changing of Sheet names, again a simple loop from another workbook getting the names

    Please Login or Register  to view this content.
    Regarding the installed application audit, I have a 2 MB Exe which is not needed to install in system and it just run on click and we can keep it somewhere in the deeper root path which is removing the VBA password in less than 5 seconds too…

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    Quote Originally Posted by yudlugar View Post
    I was just looking at the "send e-mail without knowing" bit I guess.
    Oh thanks for the reply... I am just curious to know if there is any method exist to get notified thats why raised a question to you

    Anyhow Two Suggestions seems to be valid for me one is Sheet Protection by TMShucks and another one is Track Changes by Vlady

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Email Question without user knowing

    @Sixthsense: absolutely right. And, if he didn't know how to do that before, he does now And thanks for the rep.

    The point is, you can do some things to make it less easy and/or less obvious. When I started work with one of my clients, he sent me a workbook and I sent it back to him within a few minutes with all the protection removed ... just to demonstrate how easy it is to do, given the right tools and knowledge (well, with the right tool(s), you don't actually need much knowledge).


    The reality is, Excel is not, and probably never will be a secure environment and, as this "gentleman" has demonstrated, a little knowledge can take you a long way. The only other way that I can think of is to use a Third Party encryption tool like Data Sentry but that costs money.

    I think that setting up a log file would also be a useful exercise

    @rybussell: PM me your email address (the forum email will do fine) or email me at my hotmail dot com account for this user name and I will send you a sample workbook that will create a text file log.

    The output looks something like:

    HTML Code: 
    I don't want to make it too public

    Regards, TMS

  18. #18
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Email Question without user knowing

    @TMShucks,

    You’re Welcome and thanks for the brief too…

  19. #19
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Email Question without user knowing

    I use something very similar (thank you TMS ) because I,ve been faced with the same problem!
    and yes, an email (using Outlook) is sent to me and then the macro deletes the email from "sent email".
    http://www.excelforum.com/outlook-pr...en-delete.html
    Last edited by john55; 10-21-2013 at 06:50 AM.
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  20. #20
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Email Question without user knowing

    @john55: thanks for that link ... that might be useful one day ... and at least now I am aware of the approach I can research if/when I need to.

    Lots of mutual back patting here! ... thanks for the rep.

    TMS

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Email Question without user knowing

    To all, thanks for the assist on this

  22. #22
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Email Question without user knowing

    You're welcome. Some interesting ideas and suggestions.

    Be good to have some more input from the OP.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Email fixed and user selected range to email.
    By Barking_Mad in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-11-2013, 05:18 AM
  2. Checking user provided email with current user in document
    By AJRight in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-17-2012, 12:29 PM
  3. Email current user
    By Tony Vargo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2010, 08:29 AM
  4. email a user form?
    By scadaman29325 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2007, 05:02 AM
  5. User From & EMail
    By Glenn in forum Excel General
    Replies: 1
    Last Post: 06-23-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1