Hello!
Here I am again with another wacky question.
I ave a db for jobs that my factory processes and manufactures. When I client submits an order we process the job and send it back to the client to sign off on. So I have a form to record the date a job was recieved (dateRecieved) the date a job was processed (dateProcessed) and a date signed (dateSigned) among other things. I have a query that creates a report of all the jobs we've processed but have not been signed (3 day's after processing)
What I want is to:
My email function is:
- Loop through the qry of late job's to send email notifications (working)
- collect orderNumber and Name for clients with more than one late job so only one email is sent out (not sure how to collect these?)
- store the execution date of the email notification in a table
- Check the stored date before send email so only one email gets sent per day.
[VBA]
Public Sub SendEmail(strMessage As String, strTo As String, strSubject)
Dim objCDOSYSMail As New CDO.Message
Dim objCDOSYSCon As New CDO.Configuration
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.co.eschelon.com"
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objCDOSYSCon.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objCDOSYSCon.Fields.Update
'Update the CDOSYS Configuration
Set objCDOSYSMail.Configuration = objCDOSYSCon
objCDOSYSMail.From = "diane@wmohs.com"
objCDOSYSMail.To = strTo
objCDOSYSMail.BCC = "andy@wmohs.com"
objCDOSYSMail.Subject = strSubject
objCDOSYSMail.HTMLBody = strMessage
objCDOSYSMail.Send
'Close the server mail object
Set objCDOSYSMail = Nothing
Set objCDOSYSCon = Nothing
MsgBox "Your Email has been sent"
End Sub
[/VBA]
My progress so far:
[VBA]
Private Sub Form_Open(Cancel As Integer)
Dim rstLate As DAO.Recordset
Dim strCurrentEmailAddress As String
Set rstLate = CurrentDb.OpenRecordset("qryEmailTest", dbOpenDynaset, dbSeeChanges)
Do While Not rstLate.EOF
strCurrentEmailAddress = rstLate!EmailAddress
SendEmail "this is a test", "andy@wmohs.com", "Late Short Orders"
rstLate.MoveNext
Loop
End Sub
[/VBA]
Any ideas??
Thanks