Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want a mesage sent when a change is made to an excel file
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want a mesage sent when a change is made to an excel file
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 "charlieking4747" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why does an Excel email attachment change file name when opened. | Excel Discussion (Misc queries) | |||
Weird File Open/Save As Behavior | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Remove or change password protected excel file | Excel Discussion (Misc queries) | |||
Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) |