Email alert from Excel
 

I am at the starting point of designing a holiday planning sheet for staff
for 2007. Is it possible once the staff member has inputted the dates that
they want for Rostered Days Off or Annual Leave or the like to have an email
sent to them a couple of days prior reminiding them that they have leave
pending (just in case they want to change it)? Also, as the administrator of
the sheet I would like to be alerted anytime an employee puts a new date into
the sheet
--
Hope you can help
Kind Regards

Email alert from Excel
 

sheet1 - Right click on tab(bottom) select:
View Code - Left Drop List select WORKSHEET - Right Tab Select - DEACTIVATE.
Meaning it will send an email to specified recipients whe the work sheet is
closed off.

Should bring up in below box:

Private Sub Worksheet_Deactivate() ' <= Line 1
'<== Insert email code here to suit
End Sub ' <==== Line 2

Have a look at this link for email options and place code with email
addresses between the line 1 and 2 above.
http://www.rondebruin.nl/sendmail.htm

Something like:
Private Sub Worksheet_Deactivate()
Application.DisplayAlerts = False
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("Sheet1", "Sheet2") ' <========= Sheets to send
Addr = , ) '<= Email addresses

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "Sheet " & Shname(N) & " " & Format(Now, "dd-mmm-yy
h-mm-ss")

ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"This is the Subject line"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Application.DisplayAlerts = True
End Sub


Corey....

"Flea" wrote in message
...
I am at the starting point of designing a holiday planning sheet for staff
for 2007. Is it possible once the staff member has inputted the dates
that
they want for Rostered Days Off or Annual Leave or the like to have an
email
sent to them a couple of days prior reminiding them that they have leave
pending (just in case they want to change it)? Also, as the administrator
of
the sheet I would like to be alerted anytime an employee puts a new date
into
the sheet
--
Hope you can help
Kind Regards