Hi Jodie,
In the workbook's ThisWorkbook module
(see below), paste the following code:
'==========>>
Option Explicit
'-------------->>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If blChange Then
Call SendEmails
End If
End Sub
'-------------->>
Private Sub Workbook_SheetChange( _
ByVal Sh As Object, _
ByVal Target As Range)
blChange = True
End Sub
'<<==========
This is workbook event code and should
be pasted into the workbook's
ThisWorkbook module *not* a standard
module or a sheet module:
Right-click the Excel icon on the
worksheet (or the icon to the left of the
File menu if your workbook is maximised)
Select 'View Code' from the menu and
paste the code.
Alt-F11 to return to Excel.
In a standard module, at the head of the
module, before any other code, paste the
following declarations and routines:
'==========>>
Option Explicit
Public blChange As Boolean
'------------->>
Public Sub SendEmails()
Dim arrRecipients As Variant
Dim i As Long
arrRecipients = VBA.Array _
("(e-mail address removed)", _
"(e-mail address removed)", _
"(e-mail address removed)") '<<=== CH ANGE
For i = LBound(arrRecipients) To UBound(arrRecipients)
Call Mail_workbook_Outlook_1A(arrRecipients(i))
Next i
End Sub
'------------->>
Public Sub Mail_workbook_Outlook_1A(sRecipient As Variant)
'Working in 2000-2007
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = sRecipient
.CC = ""
.BCC = ""
.Subject = "Current Manual Version '<<=== CH ANGE
.Body = "Your body text"
.Attachments.Add ThisWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
'<<==========
Note that the last routine is an extremely
lightlymodified version of Ron de Bruin's
Mail_workbook_Outlook_1 routine,
which you can find, along with a
significant number of other email
procedures, at:
Mail the whole workbook
http://www.rondebruin.nl/mail/folder2/mail1.htm
Note also that any change, even if
subsequently reversed, will trigger the
above code.
As written, a boolean flag (blChange)
is activated by a change in any of the
worksheets; the email code is called by
the workbook's BeforeClose event if the
flag is set to true and the recipients are
fed sequentially to the email code from
an array in the SendEmails procedure.
