Setting Limit Send Email VBA

[asparagus]

Hello Master,

I build my project to send reminder via email when file excel open.
I want to setting limit sending email just once when open file excel.
If i open file a second time, email doesn't send to outlook
any reference to help may problem ?
Thanks

regards,
AsparAgus

 

[lohithsriram]

Hi Asparagus,

I am not very clear what exactly you want here but you can try a trick of saving True or False on a cell somewhere on your Excel workbook. By default set the cell value to False and check this before sending an email . when it is open for the first time, since its False send the email and set it True.

 

[shrivallabha]

You can try below function for doing this.

Public Function blHasThisBeenDone() As Boolean
Dim nmDone As Name

On Error Resume Next
Set nmDone = ThisWorkbook.Names("RunDate")
On Error GoTo 0

If nmDone Is Nothing Then
  ThisWorkbook.Names.Add "RunDate", Date, True '\\Change True to False if you want to hide name from user
Else
  If CDate(Date) = CDate(Replace(nmDone.Value, "=", "")) Then
  blHasThisBeenDone = True
  Else
  ThisWorkbook.Names.Add "RunDate", Date, True '\\Change True to False if you want to hide name from user
  End If
End If

End Function

And then use above function in the main code like below:

Public Sub MyCode()
   
  If blHasThisBeenDone Then
  MsgBox "Task is completed!", vbInformation
  Else
  MsgBox "Task is not completed!", vbInformation
  End If

End Sub