OK - I've got some time............
First, review this article:
Sending e-mail via Outlook, and set up a module to do so.
Second, as NeoPa said, your code is a disaster (he didn't say, that, but I won't mince words). Here is how I would approach your code--then at least you will be able to troubleshoot things a bit better.
- Public Sub SecondEmailAttempt()
-
On Error GoTo EH:
-
Dim db As DAO.Database
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Dim strSendTo As String
-
Dim strSubject As String
-
Dim strEMailBody As String
-
-
strSQL = _
-
"SELECT * " & _
-
"FROM ProductRequestForm_Eric " & _
-
"WHERE SecondEmailDate Is Null " & _
-
"AND FirstEmailDate <= #" & Date - 7 & "#;"
-
Set db = CurrentDb()
-
Set rst = db.OpenRecordset(strSQL)
-
With rst
-
If Not (.BOF And .EOF) Then
-
Call .MoveFirst
-
Do While Not .EOF
-
strSendTo = !SubmitterEmail
-
strSubject = "Second Email Attempt"
-
strEMailBody = _
-
"Hello " & !SubmitterFirstName & "," & vbCrLf & _
-
"You have recently used an item that is undergoing " & _
-
"evaluation. Please Click the link below to tell us " & _
-
"about your experience with the" & _
-
!ProductDescription & ". You should receive an email " & _
-
"each time you use an item under evaluation until the " & _
-
"evaluation is complete. Lack of compliance could " & _
-
"impact the decisions made on items under evaluation."
-
Call .Edit
-
!SecondEmailDate = Date
-
Call .UPDATE
-
-
'Generate and Display the E-Mail
-
Call SendAnEMail(olSendTo:=strSendTo, _
-
olSubject:=strSubject, _
-
olEMailBody:=strEMailBody, _
-
olDisplay:=True)
-
Call .MoveNext
-
Loop
-
End If
-
End With
-
-
ExitSub:
-
-
rst.Close
-
db.Close
-
Set rst = Nothing
-
Set db = Nothing
-
-
EH:
-
MsgBox _
-
"There was an error sending the e-mail!" & _
-
vbCrLf & vbCrLf & _
-
Err.Number & vbCrLf & _
-
Err.Description, vbOKOnly
-
GoTo ExitSub
-
End Sub
If you receive any errors now, let us know and then we will be able to address more succinctly.
Hope this hepps!