Something like this in the Workbook module could probably work
Option Explicit
Private Sub Wworkbook_Open()
Dim LastDateSent As Date
On Error GoTo FirstTIme
LastDateSent = ThisWorkbook.CustomDocumentProperties("EmailSentDate")
On Error GoTo 0
If LastDateSent < Int(Now) Then
MsgBox "Send email" ' logic
ThisWorkbook.CustomDocumentProperties("EmailSentDate") = Int(Now)
End If
Exit Sub
FirstTIme:
Call ThisWorkbook.CustomDocumentProperties.Add("EmailSentDate", False, msoPropertyTypeDate, Int(Now) - 1) ' yesterday
Resume
End Sub