By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

I want to send an email using a macro but only once? Access 2010

P: 37
Hi there.

I have a macro set up to send an email when a condition exists. The macro will send when the value in a field is equal or greater than 500,000, but I only want to send this email once. At the minute, everytime the field is updated, because its still over 500,000, the macro sends again. I have tried adding a stopMacro after the if statement but this doesn't appear to do anything?
Mar 24 '12 #1

✓ answered by ADezii

@Corwin Moyne
  1. Declare a Private, Form Level, Boolean Variable as such:
    Expand|Select|Wrap|Line Numbers
    1. Private blnEMailSent As Boolean
  2. Execute Code similar to the following:
    Expand|Select|Wrap|Line Numbers
    1. If Me![txtTest] > 500000 And Not blnEMailSent Then
    2.   blnEMailSent = True
    3.     MsgBox "Send E-Mail here"
    4. End If
  3. An E-Mail will only be set if the Value of Me![txtTest] > 500000 AND blnEMailSent = False
  4. By Default, blnEMailSent will be initialized to False, and if the Value is > 500000 it will then be Reset to True and another E-Mail will not be sent unless blnEMailSent is somehow again Reset to False.

Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,624
@Corwin Moyne
  1. Declare a Private, Form Level, Boolean Variable as such:
    Expand|Select|Wrap|Line Numbers
    1. Private blnEMailSent As Boolean
  2. Execute Code similar to the following:
    Expand|Select|Wrap|Line Numbers
    1. If Me![txtTest] > 500000 And Not blnEMailSent Then
    2.   blnEMailSent = True
    3.     MsgBox "Send E-Mail here"
    4. End If
  3. An E-Mail will only be set if the Value of Me![txtTest] > 500000 AND blnEMailSent = False
  4. By Default, blnEMailSent will be initialized to False, and if the Value is > 500000 it will then be Reset to True and another E-Mail will not be sent unless blnEMailSent is somehow again Reset to False.
Mar 24 '12 #2

NeoPa
Expert Mod 15k+
P: 31,419
Check the before status as well as the after status.

Only if the after status is > 500,000 and the before status not > 500,000 should you trigger the routine.
Mar 24 '12 #3

P: 2
Thank you ADezii!!! I was looking for hours for this code!!
Mar 25 '12 #4

ADezii
Expert 5K+
P: 8,624
You are quite welcome. Keep in mind that you can Reset the Value of the Variable at any time, and return to the Original State by executing:
Expand|Select|Wrap|Line Numbers
  1. blnEMailSent = False
Mar 25 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.