Send email report on certian date

[jdcallejas]

Hello,

Ok I have a form timer where I set the timer event to send an email of a report every morning at 1145am. This works fine.

Now I need to make this code do the following; I need to have this report sent on the 10th of each month. How can I do that? Here is the code that I have right now:

Private Sub Form_Timer()
If Format(Now(), "hh:mm:ss") = "11:45:00" Then
DoCmd.SendObject acSendReport, "AM Email Tracker Report", "Rich Text Format", "rbenton@wm.com,dhall2@wm.com", , "Trackers", "Attached please find AM Traker Report for today", False
End If
End Sub


Thanks
Daniel

 

[boblarson]

Change this line:

If Format(Now(), "hh:mm:ss") = "11:45:00" Then


to this

If Date = DateSerial(Year(Date), Month(Date),10) AND Format(Now(), "hh:mm:ss") = "11:45:00" Then

 

[jdcallejas]

Hello,

thanks for the prompt response, now if i have multiple days like the 10th, 15th and 25th all I have to do is copy and paste this code and change the day?

thanks,

ohh also for some reason, in access 2003 this timer form use to be always closed, and the email would work, I noticed that in 2007 if the form is closed it will not run the code.. Why is this?

Thanks,

Daniel

 

[boblarson]

Hello,

thanks for the prompt response, now if i have multiple days like the 10th, 15th and 25th all I have to do is copy and paste this code and change the day?

yes, that is true

ohh also for some reason, in access 2003 this timer form use to be always closed, and the email would work, I noticed that in 2007 if the form is closed it will not run the code.. Why is this?

Not sure why it would work in 2003 if the form was closed because the timer will not run if the form is closed. Are you sure you didn't have it open but hidden? Or some other code was doing it. A timer will not work if the form is closed in either 2003 or 2007.

 

[jdcallejas]

Hello,

You r right! I just found the code, I did this like 10 years ago and I need to use this timer for a new project am doing, but yes there is a macro that opens the forms in hidden mode..

So about the dates I can just copy and paste the code you gave me and put the different days I want it to send the email?

Thanks,

Daniel

 

[boblarson]

So about the dates I can just copy and paste the code you gave me and put the different days I want it to send the email?

Yep, just change the 10 to 15 or 20 or 25 and it will do it for the current month/year.

 

[jdcallejas]

Hello Bob,

Thanks again, now I have a big problem and I cant figure out how to get rid off... This office is using access 2007 and they have VISTA!!!! Well in XP when I set the comfirm to false it just sends the email automatic, with this VISTA!!! Its always asking to confirm the email... How can I get rid of that? I just want it to send the email without asking "Are you sure?" bull..

Thanks,

Daniel

 

[boblarson]

How can I get rid of that? I just want it to send the email without asking "Are you sure?" bull..

I think you would need one of the third party tools for that. I can't remember the name of them right now though.

 

[unluckytoe]

Bob:

Is there a way that with this code that only one computer could email this report? I have a front end that does this, but when I get ready to deploy it, I don't want all of the FE's to be sending the same email.

I've seen here somewhere that you could implement a code to verify a certain computer and if it passes, it will send out the email.

Is this possible, or is it way to complicated?

Thank you:

Brian

 

[boblarson]

If you know the computer name you could do a check in the code so:

If Environ("computername") = "WhateverItShouldBeHere" Then
   '...do your code to send the email
End If

 

[unluckytoe]

Bob:

Here's my code:

Private Sub Form_Timer()
If Environ("computername") = "WALSH-BMOORE" Then
If Date = DateSerial(Year(Date), Month(Date), 1) And Format(Now(), "hh:mm:ss") = "10:00:00" Then
DoCmd.RunMacro "Daily Project Tracker"
End If
End Sub

I'm getting this error: Block If without End If

I'm not sure what this means. Could you help?

Thank you so much:

Brian

 

[boblarson]

You're missing an End If (see red):

Private Sub Form_Timer()
If Environ("computername") = "WALSH-BMOORE" Then
   If Date = DateSerial(Year(Date), Month(Date), 1) And Format(Now(), "hh:mm:ss") = "10:00:00" Then
       DoCmd.RunMacro "Daily Project Tracker"
   End If
[COLOR="Red"]End If[/COLOR]
End Sub

I wouldn't use = 10:00:00 because it could be that will never hit it exactly, or something happens and it doesn't run. I would use a flag to set if it does run but set it so it will possibly run (if the flag has not yet been set) to something BETWEEN x time and y time (maybe a 5 to 10 minute interval, just in case).

 

[unluckytoe]

Bob:

That's fantastic, I truly thank you for all your help.

In regards to the flags, how would I go by setting that up? Is there a place where I can read about it? Or is it pretty simple and you could throw me a sample?

Thank you again:

Brian

 

[boblarson]

I would either use a custom database property (Allen Browne has code to add one and then set them on his website) or use a table to store the value (probably the date of the run) so that way if today's date is in there it isn't run again until the date changes. You can use a DLookup to get the date from the table and then check it against DATE to see if it matches and if not, run the code (if the computer matches).

 

[unluckytoe]

Bob:

I just tried to test the code that you provided to me, and it doesn't fire. I have a feeling that it's the computer name isn't right. I looked at the "My Computer" preference and typed it in perfectly. But alas, nothing happens.

What is the best way for me to obtain the computer name that access sees?

Thank You Again:

Brian

 

[boblarson]

Which version of Windows are you on?

 

[unluckytoe]

Bob:

Im on windows XP. But I have another problem, I can't even get my original code that I started with to fire. I had it load up on a hidden form at startup, it worked before but now it's not, and I can't figure out why. LOL, this is way to complicated for me.

Brian

 

[boblarson]

What event do you have it on?

 

[unluckytoe]

On Timer and set the Timer Interval to 1000. Still nothing. I'm using Access 2007 if that helps.

Brian

 

[boblarson]

Did you remember to set a trusted location (especially if you moved the file to another location).