I am stuck in a Loop!! 1

[mike1975]

Can anyone help?,

I am trying to run a macro in Access that will automatically send an email every Wednesday only once. I have written the following code to run the macro however I am stuck trying to automate it.

Dim MyDate, MyWeekday
MyDate = Date
MyWeekday = Weekday(MyDate, vbUseSystemDayOfWeek)
If MyWeekday = 3 Then
DoCmd.SendObject acSendReport, "Support Request Log Query", acFormatSNP, "mikeharris", , , "Pending Report", False
End If
End Sub

How would I build loop statements around this that would enable the macro to send the mail on a Wednesday only once rather than keep repeating itself over and over again on a Wednesday filling up the recipients in box. To enable the macro to run automatically would i need to name it Autoexec?

MIKE

 

[gwinn7]

One way to do it is to have a small table store the date/time of the last sent e-mail. Perform a "DateDiff" function between today's date/time and the last date/time the mail was sent. If greater than or equal to 7 days, send another message. Sample code (something like this):

Public Function SendMailYN() as Boolean

Dim dLastDate as Date
Dim idays as Integer

On Error Goto Handler

SendMailYN = False
dLastDate = Dlookup("DateSent", "Lookup_MailLog&quot
If Datediff("d", Now(), dLastDate) >= 7 then
SendMail = True

Handler:
' Exit function


End Function

The above code has not been debugged completely, so you may have to tweak it. The code is pretty self-explanatory.

Obviously, you will have to overwrite the last date with your new one after an e-mail is sent.

Hope this helps.

Gary
gwinn7

 

[gwinn7]

Well, I forgot an "End IF" after the SendMailYN = TRUE

Here is an excerpt of the above code revised...

If Datediff("d", Now(), dLastDate) >= 7 then
SendMailYN = True
End if

Gary
gwinn7

 

[pflangan]

macro ain't the right terminology for what yer doing.

to stop the code from looping, declare a static boolean variable, and set this to true when you have sent the email.

like this

dim MyDate,MyWeekday
static bSent as boolean

MyDate=Date
MyWeekday = Weekday(MyDate, vbUseSystemDayOfWeek)
If ((MyWeekday = 3) And (bSent=False))Then
DoCmd.SendObject acSendReport, "Support Request Log Query", acFormatSNP, "mikeharris", , , "Pending Report", False
bSent = True
End If

This will send the report once and then stop. In order to do it every wednesday, you will need to set bSent to FALSE at some stage. Why not Tuesday?

If MyWeekday = 2 then bSent = FALSE

-----------------------------------------
now we move onto what to call it etc.

I would put this code in the Timer event of a form, and then set the Timer interval to 86400000 (24 hours)


langan.

 

[mike1975]

Thanks for your replies folks - I am trying them now - I would expect to be asking for more help soon.

Mike

 

[mike1975]

Langan,
Is there anyway to test the code to see if it will work? Appart from waiting until the clock ticks over?

Mike

 

[pflangan]

Mike,
Set the timer to something smaller, say to 60000 (1 minute),
and then set the date on your pc first to tuesday, then to wednesday, then back to tuesday etc.

you can also set a breakpoint (f9) at the start of the form_timer event to have a peek at the code as it's being executed.

 

[mike1975]

Thanks for that and the code works well - the only thing that crossed my mind is that when I open the form with the timer set to 24hours is there not the possibility of the check not happening on a Monday particularly if the form was opened and closed a few times as this would start the timer to zero again? I have put the timer on the 'Welcome' screen as this is where all the navigation throught the Database takes place. I could overcome this problem by reducing the timer amount - but then I could get the report mailed twice on the same day.

Sorry to be a pain - Mike

 

[pflangan]

yes,
reduce the timer to twelve hours. This will not mail the report twice, because the first time it is sent the bSent variable will be set to True.

If you look at the code, bSent must be False in order for the report to be sent (as well as the weekday being 3). that was the whole point of the excersise in the first place.

Paul Langan.