VBA Excel get values of updated cells

Question

OK, so I have been struggling with this problem for some time. I am fairly new to VBA so bear with me.

What I need my program to do is to grab the cell values in my workbook that were updated and push the data to an email. Let's say my range I want to watch is A4:A100. A user puts in a sales order number into A10 and A11. I need my program to take the values from those cells and insert them into an email when the workbook is saved. I already have the code at the end of this question that emails a list of people when the workbook is saved. Basically I would very much like to have this email contain the ID's (Column A) of records that were entered. Thank you for your help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim answer As String

answer = MsgBox("Would you like to save and email a notification?", vbYesNo,     "Save and Email")

If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
newmsg.Recipients.Add ("[email protected]")
newmsg.Recipients.Add ("[email protected]")
'add subject
newmsg.Subject = "Update on CREDIT HOLD list"
'add body
newmsg.Body = "Please check the Credit Hold excel file on the S drive for      updates."
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "File saved and messages sent.", , "Saved and Sent"



End If


'save the document
'Me.Worksheets.Save

End Sub

how do you want to insert those values in the email, I recommend you create a table in your email body and the values from the spreadsheet — Jeanno
I should have said...this is a sample list of items that are on or off a hold So...that should be fine. I could create an email that has a table with a list of ID's that have been changed and their corresponding "Hold" column (which is the range E4:E500). How would I pull just the updated fields into an array and pass them to the email? — William Smead
Copy/Paste is probably the easiest way to handle this. Range("A20:A21").Copy and then corresponding code to paste into Outlook. I don't know Outlook off the of my head. — Byron Wall

Raystafarian Raystafarian · Accepted Answer · 2015-07-14T18:12:52

You'll need a worksheet change event in the sheet you're watching -

Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Range("A4:A100")) Is Nothing Then
    Dim ws As Worksheet
    Set ws = Sheets("changes")
    Dim lastrow As Integer
    lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    ws.Cells(lastrow + 1, 1) = target.Address
    ws.Cells(lastrow + 1, 2) = target
End If
End Sub

You'll need to create a sheet named changes and put something in row 1 col A & B like "address" and "new value" -

enter image description here

You can see I had a misspelling and had to correct it. You should sort that stuff out in your macro above.

VBA Excel get values of updated cells

1 Answers