Linked Questions

Popular Questions

Microsoft Access VBA Event Procedure On Timer Error

Asked by At

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:

enter image description here

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

Related Questions