Worksheet Mailing Conditions

M

MailHelp

I have the following code:

Sub Mail_ActiveAheet()
Dim strDate as String
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & _
Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "[email protected]", _
"Subject Line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkBook.FullName
ActiveWorkbook.Close False
End Sub

....and I am using it to send a read receipt to my email
address when the Excel file has been opened. I have a
hidden worksheet that includes the current date and time
that the file was opened. This hidden sheet is what gets
sent to me. What I need to do is figure out how to get
Excel to set a variable that will be checked when the file
is opened more than once. I don't want to get an email
everytime it's opened, just the first time. Is this
possible? Thanks.
 
D

David Coleman

Hi

On your hidden sheet, you could consider reserving cell "A50" (or similar)
for this purpose - set it to TRUE if the sheet needs mailing and FALSE once
it's been done - then set the code to send the mail to check for this value
first....

e.g. if range("A50") then {sendmail}

Hope this helps

David
 
Top