emails

[compu]

hello all,

okay so i have a query which checks which tasks are between 1 and 3 days by using the date due and the current date to calculate the number of days and therefore sending out the email.

this works perfectly.

however, how can i add to this where if a user completes a task the email should not be sent to that specific user.

here is my code for the email:

Function GenerateEmail(MySQL As String)
On Error GoTo exit_function:
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 = Outlook.Application
    End If
    Set oEmailItem = oOutlook.CreateItem(olMailItem)

    With oEmailItem
                .To = rs!Email
                .Subject = "Task due in 3 days Reminder for " & rs!NameNew
                .HTMLBody = "You have a task pending & vbCr & _
                            "Employee: " & rs!NameNew & vbCr & _
                             "Due Date: " & rs!DueDate & vbCr & ""
                .Display
                .Send
                rs.Edit
                rs!dateemailsent = Date
                rs.Update
                
            
    End With
    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    rs.MoveNext
    End If
    
Loop
Else
'do nothing
End If
exit_function:
    Exit Function
End Function

this is the code that will call the email.

Private Sub Form_Timer()
Text100.Value = Format(Time, "HH:mm:ss AM/PM")
Static iCount As Integer
iCount = iCount + 1
If iCount = 60 Then
TimerInterval = 0
Call GenerateEmail("select * from QueryAllTasksduein3days")
If TimerInterval = 0 Then
TimerInterval = 125
End If
Exit Sub
End If
End Sub

any help would be appreciated.

 

[Gasman]

Restrict the sql to only tasks that have not been completed?

 

[compu]

any code you may have that could help?

 

[Minty]

In your QueryAllTasksduein3days change it to only include tasks that have no completion date? Then you will only be bringing in the data you need.