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.

Solved: Excel email notification when changes saved, include changes

Discussion in 'Business Applications' started by amarkle, May 15, 2012.

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

    amarkle Thread Starter

    Joined:
    May 15, 2012
    Messages:
    4
    The following thread helped me to create an email notification upon saving an excel sheet:
    http://forums.techguy.org/business-applications/171894-email-notification-excel-when-changes.html

    The code I used is the following code:


    I am wondering if anyone can update this code, so that it also lists what changes or what sheet&cell was modified.

    Thank you! :D

    BTW, I am a complete noob at VBA codes.
     
  2. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Because this is written into the Before_Save event it would be hard to do this. One idea is to put code into each worksheet change event that writes the changes to a log file. Then you could attach that log file - though there are sticky points to that.

    Or, perhaps you could put code in the worksheet change even that plugs the sheet and cell changed into another cell. Then when you send the e-mail, place the text from that cell into the body.
     
  3. amarkle

    amarkle Thread Starter

    Joined:
    May 15, 2012
    Messages:
    4
    Do you know of a code or could you create a code for me that sends an email with the following:

    -lists the username (if possible)
    -document name
    -changes made or sheet&cell changed

    and where the code will work on any computer in either excel 2003,2007,or 2010 (as we have all 3 in the office)


    I sent my workbook to a coworker and the code did not work like it does on my computer. It did not generate an email.

    I have tried multiple codes and site and tried to manipulate a few codes myself, but I have gotten nowhere in the last couple months and am starting to get frustrated. I believe that if I can get this to work, it would solve a large problem in our office with the document we use.

    I would much appreciate anything you could get me.
     
  4. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    Step 1: Create a sheet in your workbook called "LogSheet"

    Step 2: For each worksheet in the VBE enter this.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim strChanges As String
    
    strChanges = "Sheet: " & Target.Parent.Name & " | " & "Cell: " & Target.Address
    
    Dim lngRows As Long
    With ThisWorkbook.Sheets("LogSheet")
        If .Cells(1, 1) = vbNullString Then lngRows = 1 Else: lngRows = .UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
        .Cells(lngRows, 1) = strChanges
    End With
    
    End Sub
    Step 3: I've modified and cleaned up your code below. See comments:

    Code:
    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 = Environ("username") ' no need for all that gooey code, when this will suffice!
    '*****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********
    
    Dim cel As Range
    For Each cel In Sheets("LogSheet").UsedRange
        strBody = strBody & cel & vbCrLf
    Next
    Sheets("LogSheet").UsedRange.EntireRow.Delete 'essentially start log off fresh after e-mail
    
    strBody = "The sheets and cells changed are listed below: " & vbCrLf & vbCrLf & strBody
    
    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
    .Body = strBody
    .Send
    End With
    If olapp.ActiveExplorer Is Nothing Then
    olapp.Quit
    Set olapp = Nothing
    End If
    End Sub
     
  5. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    try what I just did. let me know if there are issues.
     
  6. amarkle

    amarkle Thread Starter

    Joined:
    May 15, 2012
    Messages:
    4
    That works awesome! Thank you!!! I have been working on this for a few months now.

    One issue I had with the last code, is that some of the computers in our office are 32-bit and others are 64-bit. Is there a way to ensure that it works on both kind??
     
  7. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    I think that shouldn't be an issue. If it is please let me know.
     
  8. amarkle

    amarkle Thread Starter

    Joined:
    May 15, 2012
    Messages:
    4
    It works! That is super awesome!

    Thank you VERY much! :D
     
  9. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    You got it!
     
  10. 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/1053347

  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