Auto Email

[Jonny45wakey]

Hi I have a requirement to send a report by email only once daily, this is triggered by the Form Event Timer.

I am using the following code which works and sends the report by email after 9am but it keeps sending it every time the form requeries, is there a way to say if todays date is already in the tbl_emailLog then dont send the email?

Thanks

Jonny

Private Sub Form_Timer()
Me.Daily_Time.Requery

If TimeValue(Now()) >= #9:00:00 AM# Then

If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "dd\/mm\/yyyy") & "#") = 0 Then

'send email
Dim olLook As Object 'Start MS Outlook
Dim olNewEmail As Object 'New email in Outlook
Dim strContactEmail As String 'Contact email address
Dim filename As String
Dim strReportName As String
Dim strPath As String



strReportName = "rptOpenJobsReport"
strPath = "S:\Canal Business Management System (BMS)\Open Jobs Report\"
strFilename = "OPEN JOBS REPORT" & "Now()" & ".pdf"
'Send PDF report to network file location
'DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptOpenJobsReport", acViewPreview, , , acHidden
DoCmd.OutputTo acOutputReport, "rptOpenJobsReport", acFormatPDF, "S:\Canal Business Management System (BMS)\Open Jobs Report\" & strFilename, False
DoCmd.Close acReport, "rptOpenJobsReport"


'Export Report
strReportName = "rptOpenJobsReport"
strFilename = "OPEN JOBS REPORT" & "Now()" & ".pdf"
'DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "rptOpenJobsReport", acViewPreview, , , acHidden
DoCmd.OutputTo acReport, "rptOpenJobsReport", acFormatPDF, strFilename, False
DoCmd.Close acReport, "rptOpenJobsReport"
'Email the results of the report generated

'With olNewEmail

Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.CreateItem(0)
olNewEmail.To = ("User email address to be nominated")
olNewEmail.sentonbehalfofname = "BusinessManagementSystem@canalengineering.co.uk"
olNewEmail.Body = "END OF DAY - OPEN OPERATIONS REPORT"
olNewEmail.Subject = "Business Management System (BMS) - END OF DAY - OPEN OPERATIONS REPORT"
olNewEmail.attachments.Add strPath & strFilename

'olNewEmail.display
olNewEmail.send
Set olLook = Nothing
Set olNewEmail = Nothing

CurrentDb.Execute ("INSERT into tbl_emailLog (sentDate) select date()")

End If
End If


End Sub

 

[Gasman]

No code tags?

As you are inserting acknowledgment into a table when sent, check the table first?
Which you appear to be doing just with the wrong format of date?

Use mm/dd/yyyy

Test your logic in the immediate window, don't just assume it will work as you expect it to?

I used to use a constant in my DBs that I found on the net.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[ComputerVersteher]

I prefer ISO Format yyyy-mm-dd, as it can be used almost everywhere and can be sorted as text.

If tbl_emailLog.sentDate is type DateTime you can use:

DCount("*", "tbl_emailLog", "[sentDate] > Date()") = 0

 

[arnelgp]

as suggested use Either US Date format or ISO Date format:

(US Date format):

...
If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "mm/dd/yyyy") & "#") = 0 Then
...
...

(ISO Date):

...
If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "yyyy-mm-dd") & "#") = 0 Then
...
...

also immediately Kill the timer, since you only need to do this Once:


Private Sub Form_Timer()
Me.Daily_Time.Requery

If TimeValue(Now()) >= #9:00:00 AM# Then
Me.TimerInterval = 0
If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "mm/dd/yyyy") & "#") = 0 Then
...
...