Worksheet Mailing Conditions

  • Thread starter Thread starter MailHelp
  • Start date Start date
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 "(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.
 
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
 
Back
Top