automate email after set time

[eric]

Hi Guys,
How can I set a time interval where it would execute the
sendobject to send out an email at the end of every month.
Right now, I have the code to execute docmd.sendobject at
the event form open. But I don't want to send out an email
everytime a user open up the form. I want an automate
email to be sent out at the end of every month period. So
when the user opens the form today, it would not send out
an email unless it's at the end of the month e.g. 7/31/03.
Thanks for the help.

 

[June Macleod]

Try the following:
(Note, I have used British date format, you may need to adjust to American
date format)

add this code to the Form Open event:

If FindLastDay() Then
'send email
End If

and add the following Function to the code module (either in the form or a
public module)

Function FindLastDay() As Boolean
On Error GoTo err_FindLastDay

Dim thedate As Date
Dim themonth As Integer
Dim lastday As Date
Dim thenextmonth As Date
Dim theyear As Integer

'get today's date
thedate = Date
'thedate = CDate("31/07/2003") 'this line was for testing purposes only
'find out what the current month is
themonth = Month(thedate)

'find out what first date of next month is
thenextmonth = DateAdd("m", 1, thedate)
themonth = Month(thenextmonth)
theyear = Year(thenextmonth)
thefirstday = CDate("01/" & themonth & "/" & theyear)

'find out what the last date of this month is
lastday = DateAdd("d", -1, thefirstday)

If thedate = lastday Then
FindLastDay = True
Else
FindLastDay = False
End If

exit_FindLastDay:
Exit Function
err_FindLastDay:
MsgBox Err.Description
Resume exit_FindLastDay

End Function


This code will be run each time the form is opened. If the current date
matches the last day of a month it will run your email code. However, you
may want to add in some other flag to ensure that it only runs once on the
last day of the month otherwise on that day you may end up sending muliple
emails depending on howmany times someone opens the form.