auto email late clients

[samohtwerdna]

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:

1. Loop through the qry of late job's to send email notifications (working)
2. 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?)
3. store the execution date of the email notification in a table
4. Check the stored date before send email so only one email gets sent per day.

My email function is:
[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

[XLGibbs]

Here is what I would do..

Set up another table for this purpose which would store the customer or primary key field from the rstLate record source (for linking), the date, and perhaps other information..

You can then build your list by specifying to select the rstLate recordset where the primary key does not exist in the emailsent table..
[vba]
strSQL = "Select Customer From rstLate where Customer not in (Select Customer From [EmailSent]) "
[/vba]
you would then (since this list would not have ben emailed yet) send and Insert Into statement to the EmailSent table...

[vba]
Insert Into [EmailSent] (Customer, Date, OtherInfo)
Select Customer, getdate(),"Other Info" From rstLate
[/vba]

which would update the list of emailed customers with a date thus eliminating them from the first part the next time through..

[samohtwerdna]

Forgive my ignorance but I'm not catching on.

I think you're suggesting adding two new tables to handle my problems. One to store the dates of the emails sent and one to store collected data??

I think I could use VBA to store an array of all the data in my query for late customers. After that - how do I collect certain feild data (e.g. orderNumber, and orderName) where the client is the same (meaning one client has more than one late job)?

If the previous post explains this - I am sorry but I dont understand. - Where would the code snippets go?

Please help...

[XLGibbs]

I am suggesting one additional table which has the unique client list, a late item indicator(like date), and an blank field which you would populate as emails are sent (like a boolean flag, or even the date sent)

You can set up this table however it suits your needs, my suggestion was conceptual in that if you had a table that stored the relevant data, you could update fields in it (using update) or add records to it (using Insert)

Without seeing the database itself, there is no way I could provide code that you could just plug and play, so those snippets were for example..

However, you have connected to the table or query in the post above, so those snippets could be incorporated in.. for example the Insert Into idea could be incorporated above the rstLate.Movenext line of yours. You would have to have another connection available in the code..

[samohtwerdna]

Thanks,

I figured the code was conceptual - I just was not getting the entry point of the concept.

So If I understand correctly "Insert Into" is used to pass info into a table and not into an array to collect data to be emailed?

If that is true, then I might say
Do While Not rstLate.EOF
strCurrentEmailAddress = rstLate!EmailAddress
SendEmail "this is a test", "andy@wmohs.com", "Late Short Orders"
rstLate.MoveNext
Insert Into [My_New_Table]
Loop But how does this collect the data I want for the email I want to send out?

Sorry if I am missing the point

[XLGibbs]

I must have misunderstood. The nature of your code indicates that you are trying to send email by looping through the recordset. Maybe I am the one who is missing the point.

The Insert Into would happen before the Movenext line...and relies on a table to Insert the records Into.

[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"

Insert Into [TableName] (Clientname, EmailAddress,EmailDate)
Select rstLate!ClientName, rstLate!EmailAddress, format(now(),"mm/dd/yy")

rstLate.MoveNext
Loop

End Sub

[/VBA]

[samohtwerdna]

Thanks again for the help.

I must be missing something though, because I get an error > "Expected end of statement > when I use the Insert Into [tblTestEmail] (field1, field2, field3)??

[XLGibbs]

My bad. Forgot to add the Access RunSQL syntax. The SQL statement needs to be a string...

[VBA]
DoCmd.RunSql _
"Insert Into [TableName] (Clientname, EmailAddress,EmailDate) _
Select " & rstLate!ClientName & "," & rstLate!EmailAddress & "," & _
format(now(),"mm/dd/yy")

[/VBA]

Sorry been working in SQL Query analyzer all day...

[samohtwerdna]

um, do I need another SQL statement? Because now I get the expected End of Statement on the "Select" command?

also should the syntax be:
DoCmd.RunSql "Insert Into []()" & "Select " & "ect"

??

[XLGibbs]

You have to make sure the whole thing is one string. Did you include the _ at the end of the first line I had?

[VBA]
strSqlInsert = "Insert Into [TableName] (Clientname, EmailAddress,EmailDate) " _
& "Select " & rstLate!ClientName & "," & rstLate!EmailAddress & "," & _
Format(Now(), "mm/dd/yy")

msgbox strSqlInsert

'DoCmd.RunSql strSqlInsert[/VBA]


use the msgbox to "test" the sql statement..make sure it looks like it should which is

then uncomment the DoCmd and take out the msgbox

[VBA]
Insert Into [table] (feild,field, field)
Select data,data,data
[/VBA]

[samohtwerdna]

Ok so Access says the syntax looks like:

RunSQL(SQLStatement,[useTransaction])

I think that the Insert Into and the Select are both (or should be) part of the SQLStatement I'm not sure what the heck [useTransaction] is but currently Access thinks "Insert Into [tblTestEmail] (Clientname, EmailAddress,EmailDate) Select " & rstLate!ClientName & "," = my SQLStatement and that " & rstLate!EmailAddress & " = my [useTransaction] - the rest of the code is not considered.

So I changed the code to:
[VBA] Do While Not rstLate.EOF
strCurrentEmailAddress = rstLate!EmailAddress
SendEmail "this is a test", "andy@wmohs.com", "Late Short Orders"
DoCmd.RunSQL _
"Insert Into [tblTestEmail] (Dealer, EmailAddress,EmailDate)" & _
"Select " & rstLate!Dealer & "," & rstLate!EmailAddress & "," & _
Format(Now(), "mm/dd/yy")

rstLate.MoveNext
Loop[/VBA]

and now it tell's me that there is a syntax error (missing operator) in query expresion 'andy@wmohs.com'.
I assume this means that something is missing in my query - like an email address? Perhaps I have a null value that it doesn't like but I thought I got rid of all the null values

[samohtwerdna]

It seem like I am getting closer with the storing of the email date - but I am still confused about the collecting of data into one email. my SendEmail function requires a "message", "recipient", "Subject"
currently I have:

SendEmail "this is a test", "andy@wmohs.com", "Late Short Orders"

I would like to replace the recipient with

SendEmail "this is a test", strCurrentEmailAddress, "Late Short Orders"

Once I have the message working - maybe something like:

SendEmail GatheredInfo, strCurrentEmailAddress, "Late Short Orders"

How can I collect the data?

[XLGibbs]

Get the message working, then I can help you gather the information. What type of information is needed to be gathered? that may be quite tricky depending on what is "what" ...

[XLGibbs]

I think that the Insert Into and the Select are both (or should be) part of the SQLStatement


They are one SQL Statement, I am not sure why this was unclear.

[samohtwerdna]

xlGibbs,

Thanks for all the help!

You weren't unclear - I was just having problems getting the whole thing as one string (not enough "" and &'s ) but that is fixed now. For simplicity I formated it as a string like you showed and it looks like:

[VBA] Do While Not rstLate.EOF
strCurrentEmailAddress = rstLate!EmailAddress
SendEmail "this is a test", "andy@wmohs.com", "Late Short Orders"

strSqlInsert = "Insert Into [tblTestEmail] (Dealer, EmailAddress, EmailDate)" & _
"Select " & rstLate!Dealer & "," & rstLate!EmailAdd & "," & _
Format(Now(), "mm/dd/yy")

DoCmd.RunSQL strSqlInsert


rstLate.MoveNext
Loop[/VBA]

However, I still get a runtime error 3075 - telling me ther is a syntax error(missing operator) in query expresion 'andy@wmohs.com'. When I check the query or rstLate I have a value for each email address and only the first address is "andy@wmohs.com" a value I threw in for null - so everything looks right ??

I will try to trim this down to just what I am working on here so I can post it for you to see the problem.

[samohtwerdna]

ps. I put the msgBox in for kicks

It came up with all the correct info for the first record then when I hit "ok" I got the runtime error again?

[XLGibbs]

Yeah, Silly me. You need to tag the email address with ' ' on each end to qualify it as text otherwise SQL reads the @ as a function call or declared variable with a missing &

strSqlInsert = "Insert Into [tblTestEmail] (Dealer, EmailAddress, EmailDate)" & _
"Select " & rstLate!Dealer & ",'" & rstLate!EmailAdd & "'," & _
Format(Now(), "mm/dd/yy")

[XLGibbs]

Oops actually you need those for all of them..
[VBA]
strSqlInsert = "Insert Into [tblTestEmail] (Dealer, EmailAddress, EmailDate)" & _
"Select '" & rstLate!Dealer & "','" & rstLate!EmailAdd & "','" & _
Format(Now(), "mm/dd/yy") & "'"
[/VBA]
Note the ' in the following spaced out version
[VBA]
strSqlInsert = "Insert Into [tblTestEmail] (Dealer, EmailAddress, EmailDate)" & _
"Select ' " & rstLate!Dealer & " ',' " & rstLate!EmailAdd & " ',' " & _
Format(Now(), "mm/dd/yy") & " ' "
[/VBA]

[samohtwerdna]

Very Cool,

Now I have my tblTestEmail appended with the pertantent info. It would be nice not to have to say yes to the append dialog box for each record appened - Is there anyway around that??

Again thanks a ton xlGibbs


now on to the collection of data

[XLGibbs]

[VBA]
DoCmd.SetWarnings Off
[/VBA]

Above turns off that warning...turn them back on at the end of the module