software

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(quot;outlook.applicationquot;)

myMsg = quot;Hello,quot; amp; Chr(10) amp; Chr(10)
myMsg = myMsg amp; quot;This email message was automatically generated by quot; amp; _
ThisWorkbook.Name amp; Chr(10) amp; Chr(10)
myMsg = myMsg amp; quot;The changes to the workbook are listed below.quot; amp; Chr(10) amp; Chr(10)
myMsg = myMsg amp; quot;Best Regardsquot; amp; Chr(10) amp; Chr(10)
myMsg = myMsg amp; Replace(strChanges, quot;ZZZXXXZZZquot;, Chr(10) amp; Chr(13))

strChanges = quot;quot;

Set myItem = ol.CreateItem(olMailItem)
For Each myCell In Range(quot;NotificationListquot;).Cells
strToList = strToList amp; IIf(strToList = quot;quot;, quot;quot;, quot;;quot;) amp; myCell.Value
Next myCell
myItem.to = strToList
myItem.Subject = quot;Notification of changes to quot; amp; 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 lt;gt; quot;quot; Then EmailNow
End Sub

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

Application.EnableEvents = False

NewValue = Target.Value
Application.Undo
OldValue = Target.Value
Application.Undo
strChangedField = Sh.Name amp; quot; cell quot; amp; Target.Address
strChanges = strChanges amp; strChangedField amp; _
IIf(OldValue lt;gt; quot;quot;, quot; changed from quot;quot;quot; amp; OldValue amp; quot;quot;quot; to quot;quot;quot; amp; NewValue amp; quot;quot;quot;.quot;, _
quot; was newly entered as quot;quot;quot; amp; NewValue amp; quot;quot;quot;.quot;) amp; quot;ZZZXXXZZZquot;
Application.EnableEvents = True

End Sub

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

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