I am working on adding a auto email function to a existing database. I have a OnTimer event that calls for a email notification based on the number a days that a item is due. Every time I try to save the code, I get the following error:
The database is a Access 2002-2003 file, so I am not sure if this is a factor as to why the application is not working, or if I am missing something else. Any assistance would be appreciated.
Code for the Form:
Option Explicit
Private Sub Form_Timer()
Me.TextTime.Value = Format(Time, "HH:mm:ss AM/PM")
Static iCount As Integer
If iCount = 60 Then
Me.TimerInterval = 0
Call modAutoEmail("SELECT * FROM qNMReport3D")
If Me.TimerInterval = 0 Then
Me.TimerInterval = 125
End If
Exit Sub
End If
End Sub
Code for the email module:
Option Explicit
Function GenerateEmail(mysql As String)
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
Dim MyEmpName As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(mysql)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs!Email) Then
rs.MoveNext
Else
If oOutlook Is Nothing Then
Set oOutlook = New Outlook.Application
End If
With oEmailItem
MyEmpName = DLookup("[FullName]", "tEmployee", "[ID] = " & rs!EmpName)
.To = rs!Email
.Subject = "3D due in 1 days Reminder"
.Body = "Task ID: " & rs!taskid & vbCr & _
"Task Name: " & rs!TaskName & vbCr & _
"Employee: " & MyEmpName & vbCr & _
"3D Due: " & rs!DueDate & vbCr & _
"This email is auto generated from Corporate Database. Please Do Not Reply."
.Send
rs.Edit
rs!dateemailsent = Date
rs.Update
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
rs.MoveNext
End If
Loop
Else
End If
rs.Close
Exit Function:
Exit Function
End Function
It seems you try to call the module name:
Call modAutoEmail("SELECT * FROM qNMReport3D")
This doesn't work, I don't know though if this triggers this funky error message.
You need to call the function:
Call GenerateEmail("SELECT * FROM qNMReport3D")
Also in your Timer function, you never increase iCount
, so the email will never be sent.
.TimerInterval
is in milliseconds, so if it would work, it would be called every 60/8 = 7.5 seconds, which is probably not what you want.