Send Email

[lead 27]

Hi
I have a macro to send a query to an email address. Is there any way I can have this execute every Friday?

Thanks

 

[Alc]

Do you have a default form that opens up when your db opens?
If so, you could add running the macro to the form's On Open event.

 

[lead 27]

HI
Thanks for replying. Yes I have a form which opens when the DB is opened which acts as a menu for all the other forms. How would I go about doing it so that the macro runs on a friday only?

Thanks

 

[Alc]

You could use something like this.
Change the On Open event to be code, rather than the macro name and add

If Format(Now,"DDDD") = "Friday" Then
       DoCmd.RunMacro ("[I]the name of your macro[/I]")
End If

Just a thought, unless you want it to run every time the db is opened on a Friday (as oppsed to just teh first time), you might want to add a table with a field that stores whether it has been run or not. The first time the code gets run on a Friday, set the value to FALSE. Add code that resets it to TRUE the first time the db is opened on any day other than Friday. The code would then be along the lines of

If Format(Now,"DDDD") = "Friday" Then
       If DLookup([I]etc etc[/I])
               DoCmd.RunMacro ("[I]the name of your macro[/I]")           
               DoCmd.SetWarnings FALSE
               DoCmd.RunSQL "Update [I]etc etc[/I] SET [I]fieldname[/I] = FALSE;"
               DoCmd.SetWarnings TRUE
       End If
Else
       DoCmd.SetWarnings FALSE
       DoCmd.RunSQL "Update [I]etc etc[/I] SET [I]fieldname[/I] = TRUE;"
       DoCmd.SetWarnings TRUE
End If

 

[lead 27]

Hi
Thanks for that. Yeah I had thought of the problem about only the first time its open on a friday.
I have never used the DLookup before is there any chance you could fill in the blanks i.e. DLookup(etc etc) and "Update etc etc"
The table with the if run field is 29 Sqn and the field is "run" which is yes/no.
Thanks

 

[Alc]

I also changed the true/false bits around, as I'd written them incorrectly.
If it's Friday AND the macro hasn't already been run, run it and set the field to show that.
If it's Friday AND the macro has already been run, do nothing.
If it's any other day, set the field to say that the macro needs to be run.

If Format(Now,"DDDD") = "Friday" Then
       If DLookup("run","[29 sqn]") = FALSE Then
               DoCmd.RunMacro ("the name of your macro")           
               DoCmd.SetWarnings FALSE
               DoCmd.RunSQL "Update [29 sqn] SET run = TRUE;"
               DoCmd.SetWarnings TRUE
       End If
Else
       DoCmd.SetWarnings FALSE
       DoCmd.RunSQL "Update [29 sqn] SET run = FALSE;"
       DoCmd.SetWarnings TRUE
End If

 

[lead 27]

Thanks that works great, just one more thing is there any way that I can not have the warning that comes up saying do you want to allow this? Just as I dont want anyone to click no.

 

[Alc]

Glad to help.

As far as your new question goes, short of installing some new software that automatically clicks 'Yes' for you (there are a few posts on the forum about this), I don't know of a way.

If you work one out, let me know - the users here are regularly complaining about having to click it, but I'm forbidden from installing anything else.

 

[lead 27]

Hi
Thanks I will have a think and let you know

Thanks for all your help

 

[Alc]

No probs.