Worksheet Mailing Conditions

[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 "(e-mail address removed)", _
"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.

 

[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

 

[Tomek]

the answers You may find in "Only Show User Form Once" posted by
Cornishbloke

 

[Tomek]

sorry.. it was supposed to be placed right below the original message from
MailHelp