1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Email Notification from excel when changes are made

Discussion in 'Business Applications' started by greggio, Oct 14, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. greggio

    greggio Thread Starter

    Joined:
    Oct 14, 2003
    Messages:
    8
    I am trying to find out if it is possible to send a notification to other users if one user edits and saves a particular document. The notification could be by email or windows messaging or whatever.

    Does anyone know if this is even possible?
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Sure. But you only want it if the file is EDITED? What if someone opens the doc and doesn't edit it?

    How well do you know VBA?
     
  3. greggio

    greggio Thread Starter

    Joined:
    Oct 14, 2003
    Messages:
    8
    Yes, only if it is edited. Many people will open the file, but only a few will be able to make changes, and they want to know when it is changed.

    I don't know VBA much at all. On a side not though, it is something I wanted to get into to help with excel and access. Do you think it is something that can be learned through a few good books? Any suggestions?

    Thanks!
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Well, you don't have to know VBA to get a VBA solution, but that is what you'll need.

    If you're interested in learning, it's easiest to start with Excel. Here's a couple sites you can start with:

    http://www.cpearson.com/excel/topic.htm
    http://j-walk.com/ss/excel/index.htm

    I personally think the books are terrible. Something like this is pretty cool. I got a complimentary copy from the seller, and it's fairly easy:

    http://www.add-ins.com/vbcollection.htm

    For the VBA code, perhaps XLGuru can help you out here. Let's see if he shows up. Personally, I just can't think when the email event should fire. There's a workbook change event, but you wouldn't want to send an email everytime it was changed--only when it was changed then closed. And changing when closed isn't good, 'cause you'll get an email everytime someone opens it. We could do it "before save", but I'm not sure that'll do it.
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Let's see if he shows up.

    <bg>, nice one, ;) ; but seriously, I also can't see when you'd fire it. It's beyond my coding capabilities for sure. If you poke the right words into a Google search you can get a lot of pointers, eg:

    http://www.google.co.uk/groups?hl=e...anged&safe=images&ie=UTF-8&oe=UTF-8&lr=&hl=en

    , tho' I've never managed to get this kind of thing working personally.

    *If* only a few can change it and "they" want to know when it is changed, I honestly think it would save a lot of hassle to set up an email group.

    Rgds,
    Andy
     
  6. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    What about.......

    Code to prevent unlocking protection unless an "EDIT" button is first pressed.

    Code that puts an Email into a particular cell when the "EDIT" BUTTON is pressed

    Code that sends an Email to the address listed in that cell (if there is one) upon saving the file

    Code that deletes the contents of that cell upon opening of the file.


    ?


    or some morph of that logic.

    -C :confused:
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Well, I know it's just as easy to send the given email to a group as it is to one person.

    Can the email go via Outlook? Or does it have to be sent regardless of default email program?

    Need to know the version of Office. If Excel and Outlook version don't match, need to know version of each.

    Will this "special" group be looking a lot WITHOUT changing? We could say "if the user name from the Windows login is Grumpy, Doc, or Sneezy, then email them when the file is closed".

    What do you want the subject and body of the email to read? Do you need it to say, for instance: "Grumpy just edited the file."?
     
  8. greggio

    greggio Thread Starter

    Joined:
    Oct 14, 2003
    Messages:
    8
    Sending to a group is not a bad idea.

    Everyone is using Office XP.

    Using the windows login name would work as well. As for the subject, "Grumpy just edited the file." is great.



    Thanks!
     
  9. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    There are only three in the group.
    Bashful is to shy to send Email.
    Happy just sits at his desk and smiles a lot.
    Sneezy isn't allowed to use the computer because he gets the keyboard sticky.
    And Dopey..... well he tries, but.....




    :D







    All mails should be cc: S. White
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    You......are......such......a......dork.

    I've given greggio (via email) instructions on where to take his criteria and get some free code. Hopefully, he'll get it and copy the code back here when he's done. It should only take him a day or two.

    :)
     
  11. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    :D it's so nice to be wanted!
     
  12. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I forgot to mention Sleepy.

    He doesn't need an email. He'll just read S. White's cc :eek:
     
  13. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    My favorite TSG graphic from Mulder--used WITHOUT permission:
     

    Attached Files:

    • bart.gif
      bart.gif
      File size:
      27.3 KB
      Views:
      146
  14. greggio

    greggio Thread Starter

    Joined:
    Oct 14, 2003
    Messages:
    8
    I got my answer thanks to dreamboat. Here is the code that I got:

    Private Declare Function getUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Private Function getLoggedUserName() As String
    Dim strBufferString As String
    Dim lngResult As Long
    strBufferString = String(255, Chr(0))
    lngResult = getUserName(strBufferString, 255)
    getLoggedUserName = Replace((Mid(strBufferString, 1, 255)), Chr(0), "")
    End Function

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim olapp As Object
    Dim olmail As Object
    Dim LoggedUserName As String
    Dim LoggedUserEmail1 As String
    Dim LoggedUserEmail2 As String
    LoggedUserName = getLoggedUserName
    '*****CHANGE EMAIL ADDRESS HERE - MyCompany.com part********
    LoggedUserEmail1 = "greg.giordano" & "@eyetk.com"
    LoggedUserEmail2 = "demetra.barlas" & "@eyetk.com"
    LoggedUserEmail3 = "richard.sullivan" & "@eyetk.com"
    '*****CHANGE EMAIL ADDRESS HERE********

    Set olapp = CreateObject("Outlook.Application")
    Set olmail = olapp.CreateItem(0)
    With olmail
    .Subject = LoggedUserName & " has edited " & ThisWorkbook.Name & "Test 100"
    .To = LoggedUserEmail1
    .CC = LoggedUserEmail2 & ";" & LoggedUserEmail3
    .Send
    End With
    If olapp.ActiveExplorer Is Nothing Then
    olapp.Quit
    Set olapp = Nothing
    End If
    End Sub





    I added a couple extra people in the "LoggedUserEmail" part. There was only one originally, but I I added the others and then added the ".CC" field. It seems you can keep adding people to that field. It took me a while to figure out that you had to use an "&" between just about everything, and that I had to put the ; in quotes.

    All in all, it wasn't that bad. I think I am going to like this stuff.
     
  15. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/171894

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice