here's some code i use (with ron debruin's help) to put the last saved name and
timestamp on the report sheet when the vacation calendar has been updated and
saved. then it sends an email to the designated people. maybe you can adapt it
to your needs.
this is on the this workbook code page
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim vType As String
Application.ScreenUpdating = False
Worksheets("Reports").Activate
Range("A1").Select
With Worksheets("Reports")
..Unprotect
End With
Range("K2").Value = "Saved by " & Environ("UserName") & " on " & Format(Date, _
"mm/dd/yy") & " at " & Format(Now(), "[$-409]h:mm AM/PM")
With Worksheets("Reports")
..Protect
End With
' send email
Call Mail_Text_in_Body
GoTo Xit
Xit:
Application.ScreenUpdating = True
End Sub
Sub Mail_Text_in_Body()
Dim msg As String, cell As Range
Dim Recipient As String, Subj As String, HLink As String
Dim Recipientcc As String, Recipientbcc As String
msg = "\\servername\sharename" ' path to file being updated
Recipient = "
Recipientcc = "
Recipientbcc = ""
'You can use a cell value also like this for the recipients
'Recipient = Sheets("mysheet").Range("A1").Value
Subj = "Vacation calendar has been updated by " & _
Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" & _
Recipientbcc & "&"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & msg
ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "%s"
End Sub
--
Gary
"bsnapool" wrote in
message ...
Is there anyway I would be able to know when a worksheet has been
updated by using a macro?
I have 73 files within a folder which are either updated from Monday -
Wednesday (Or sometimes not even updated) by managers.
Is there anyway I could automatically find out whether the files have
been updated and maybe the date of when it was last saved?
Hope this is all the info you may need.
Many thanks
Andrew
--
bsnapool
------------------------------------------------------------------------
bsnapool's Profile:
http://www.excelforum.com/member.php...o&userid=36115
View this thread: http://www.excelforum.com/showthread...hreadid=563912