Hello!
I would like an automatic email to be sent if I update a manual I have
created to ensure users have the most recent copy. Is there a way I can
set-up the nofication if I change anything in the workbook?
Thanks,
Jodie

--
Jodie Gardner

Hi Jodie

One way is to use the BeforeSave event in this ThisWorkbook module
to run a mail macro

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call macroname
End Sub


For mail code see
http://www.rondebruin.nl/sendmail.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"jodieg" .(donotspam) wrote in message ...
Hello!
I would like an automatic email to be sent if I update a manual I have
created to ensure users have the most recent copy. Is there a way I can
set-up the nofication if I change anything in the workbook?
Thanks,
Jodie

--
Jodie Gardner

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 _
", _
", _
") '<<=== 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.



---
Regards.
Norman


"jodieg" .(donotspam) wrote in message
...
Hello!
I would like an automatic email to be sent if I update a manual I have
created to ensure users have the most recent copy. Is there a way I can
set-up the nofication if I change anything in the workbook?
Thanks,
Jodie

--
Jodie Gardner