I want a mesage sent when a change is made to an excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a spreadsheet in excel for keeping track of stock we hold.
When a customer request a call off from stock the customer contact will enter
the information onto the spread sheet. I want to be able to create a message
and send it to the despatcher, notifying him of the call off request, with as
little work as possible.
 
Charlie,

This code requires a reference to MS Outlook - in the VBE, choose Tools / References, and check the
box next to outlook

In a regular module, put this code:

Option Explicit
Public OldValue As Variant
Public NewValue As Variant
Public strChanges As String

Sub EmailNow()
Dim ol As Object
Dim myItem As Outlook.MailItem
Dim myMsg As String
Dim myCell As Range
Dim strToList As String

Set ol = CreateObject("outlook.application")

myMsg = "Hello," & Chr(10) & Chr(10)
myMsg = myMsg & "This email message was automatically generated by " & _
ThisWorkbook.Name & Chr(10) & Chr(10)
myMsg = myMsg & "The changes to the workbook are listed below." & Chr(10) & Chr(10)
myMsg = myMsg & "Best Regards" & Chr(10) & Chr(10)
myMsg = myMsg & Replace(strChanges, "ZZZXXXZZZ", Chr(10) & Chr(13))

strChanges = ""

Set myItem = ol.CreateItem(olMailItem)
For Each myCell In Range("NotificationList").Cells
strToList = strToList & IIf(strToList = "", "", ";") & myCell.Value
Next myCell
myItem.to = strToList
myItem.Subject = "Notification of changes to " & ThisWorkbook.Name
myItem.Body = myMsg
myItem.Send

Set ol = Nothing

End Sub

In the codemodule of the thisworkbook object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If strChanges <> "" Then EmailNow
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

NewValue = Target.Value
Application.Undo
OldValue = Target.Value
Application.Undo
strChangedField = Sh.Name & " cell " & Target.Address
strChanges = strChanges & strChangedField & _
IIf(OldValue <> "", " changed from """ & OldValue & """ to """ & NewValue & """.", _
" was newly entered as """ & NewValue & """.") & "ZZZXXXZZZ"
Application.EnableEvents = True

End Sub

Of course, you can modify the message and information anyway you want.

HTH,
Bernie
MS Excel MVP
 
Back
Top