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:


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

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

My progress so far:

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


Any ideas??

Thanks :thumb

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

strSQL = "Select Customer From rstLate where Customer not in (Select Customer From [EmailSent]) "

you would then (since this list would not have ben emailed yet) send and Insert Into statement to the EmailSent table...


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


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 :doh:

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


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

[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)??

:dunno

[XLGibbs]

http://vbaexpress.com/forum/images/smilies/ohhmygosh.gif

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


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



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?


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

msgbox strSqlInsert

'DoCmd.RunSql strSqlInsert


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


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

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

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 :dunno

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

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

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..

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

Note the ' in the following spaced out version

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

[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 :thumb


now on to the collection of data :think:

[XLGibbs]

DoCmd.SetWarnings Off


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

[samohtwerdna]

I know I'm dull - but does it matter if I set that before or after the
DoCmd.RunSQL strSqlInsert??

I thought it wouldn't so I placed it before. I'll test that in a second

[XLGibbs]

well, if you want the warning to be off when that SQL statement runs, you would want it before....right?

[samohtwerdna]

it is amazing how dumb I can be :giggle

[XLGibbs]

Hey we have all be there before. I think I have actually seen someone post the quesion:

"Is there a function in excel for Average?"


I remember once asking a very stupid question, but it was not VBA related and not on a forum. It was "Will you marry me?"

It is easy to get completely removed from common sense sometimes well coding, i remove myself from common sense out of habit quite frequently now!

[samohtwerdna]

OK, XLBiggs -

It's a new day, I hope to have my right mind with me today (but that might not help).

Now that I have:

A recordset of late job's
A function to email each client
A table to store the last email date
I need to:

Collect certain job info for clients with multiple jobs late
write over the old info in the store table for sent emails so the table doesn't get enormous
format the collected data into the email so it makes sense

I am thinking that I will need to put an IF statement in or maybe a For Each to filter through the "Dealer" (clients) to find matches? But I am not sure what to tell Access to do - in order to collect the data?

I might have:

For Each [Dealer] In rstLate
Collect [orderNumber], [orderName], [DaysLate] '???

Any thoughts??

[XLGibbs]

First things first, some of this is easy..

You can clear the email sent table using a Truncate Table statement

sqlTruncate = "Truncate Table [TableName]"


Which leaves the structure intact but removes data..or you can delete certain records once sent..

If you have an email sent flag in the table..

sqlDelete = "Delete From [TableName] Where [EmailSent] is not null"
'or
sqlDelete = "Delete From [TableName] Where [EmailSent] = 1"
'presumes a flag of 1 or 0 is set when mail is sent...


Now, as far as "collecting" the mutliple records that are late, you would have to cycle through the records for the Client in the rstLate table and build the necessary information into a string for the body of the email.

Something like this would cycle through the records:

'make a string something absurd that will always return a false first time through

Do While Not rstLate.EOF 'outer loop
If rstLate![EmailSent] = 1 Then 'or true ...
strClientName = rstLate![ClientName]
strEmailBody = ""
Do while not rstLate.EOF 'inner loop to get the relevant information
If rstLate![EmailSent] is null 'or False or 1, up to you...
If rstLate![ClientName] = strClientName then
strEmailBody = strEmailBody & rstLate![OrderNum] & " " & rstLate![Amount] & vbNewLine
End If
rstLate![EmailSent] = 1 'or True or something sets this record as having email sent below
End if
rstLate.MoveNext
Loop

'send email bit here...strEmailBody is the text of the email..



End if 'email check on outer loop
rstLate.MoveNext 'go to next record on outer loop, will skip over emails sent already
Loop


The general idea is...move through the recordset looking at the client name and in that row, look for an email sent. If an email not sent, go through the records and get the information into the strEmailBody, and if it does do that, tag that line with email sent, so the outer loop skips it next time..


There are likely easier ways, but I am just shooting from the hip..

[samohtwerdna]

um, Should the [EmailSent] Flag be a field in tblTestEmail - instead of rstLate? :dunno

[samohtwerdna]

Instead of a boolean EmailSent I would rather check the EmailDate and see if the date is less than today then if true send another email and record a new EmailDate but if false that means I already sent an email today - so don't send another one.

To do this would I need to set tblTestEmail (the table that is storing the clientName + their email address + EmailDate) as another recordset?

Dim rstLate As DAO.Recordset, rstTable As DAO.Recordset


Set rstLate = CurrentDb.OpenRecordset("qryEmailTest", dbOpenDynaset, dbSeeChanges)
Set rstTable = CurrentDb.OpenRecordset("tblTestEmail", dbOpenDynaset, dbSeeChanges)


???
Or is this not necessary?

[samohtwerdna]

OK maybe I just need to understand how to append a flag to the qry (rstLate) for the emails sent??

How would I do that?

[XLGibbs]

Just have a column in the table itself if you can named EmailSent and set it to boolean. 1 is true, 0 is false. Then the syntax would just be


sqlEmailSent = "Update [TableName] Set [EmailSent] = 1 Where [ClientName] = '" & RstLate![ClientName] & '"

DoCmd.RunSql sqlEmailSent


instead of the

rstLate!EmailSent = 1


From my above sample which is a guide not a plug-n-play attempt..

[samohtwerdna]

Thanks XlBiggs,

I am trying not to plug and play - I would rather understand the process than just get it to work.

So I was thinking - Why not have my orginal loop through the qry collect every thing I want (name, number, number of day's late, email address, date last emailed)
then loop again through the new table collecting the data to test and see if the date last emailed is over a day ago and combine together in the "inner loop" of this the (name, number and number of days late) to be part of my message text?

This is a bit redundant, but it makes sense to me

What do you think?

[XLGibbs]

That sounds like a plan. You can get the general idea from the outer/inner loop I posted earlier and see if that logic can work with your idea...

[samohtwerdna]

distilling it into what I think I understand I have come to:

Do While Not rstLate.EOF 'check the qry of late jobs
strCurrentEmailAddress = rstLate!EmailAddress
If rstTable![emailDate] Then
If Not rstTable![emailDate] = Date Then 'do nothing if emailDate is today
'insert the data you need for the email into a new table
strSqlInsert = "Insert Into [tblTestEmail] (Dealer, orderNumber, Name, DaysElapsedSinceProccessed, EmailAddress, EmailDate)" & _
"Select ' " & rstLate!Dealer & " ',' " & rstLate!orderNumber & " ',' " & rstLate!Name & " ','" & rstLate!DaysElapsedSinceProccessed & " ',' " & rstLate!EmailAdd & " ',' " & _
Format(Now(), "mm/dd/yy") & "' "

'turn off the append warnings
DoCmd.SetWarnings (0)
DoCmd.RunSQL strSqlInsert
End If
End If

strClientName = rstTable![Dealer]
strEmailBody = "You have these jobs that need to be signed off"
Do While Not rstTable.EOF ' check the store Table
If rstTable![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstTable![orderNumber]
MsgBox "could be a good start!"
End If

SendEmail strEmailBody, "andy@wmohs.com", "Test Late Short Orders"
MsgBox "Your Email has been sent"

' rstTable![EmailSent] = 1

rstTable.MoveNext 'go to next record on outer loop, will skip over emails sent already

Loop

rstLate.MoveNext
Loop

My problems are still numerous. At rstTable![EmailSent] = 1 Access tells me "Update or Cancel Update without Addnew or Edit" so if I comment that out - I seem to get through the first row correctly but then I am stopped by a "No current record" error on the next pass through the qry?

However, this will send 8 emails to me. The odd thing is I have 15 late jobs? with 11 unique clients - and the emails sent always have the first job as the job number?? - So I must be way off

Attached is a paired down sample

[XLGibbs]

I had corrected the syntax to update the Email sent field in an earlier post...a lot of stuff getting comingled and thrown in....

I am not sure you are being clear.

In the first section you appear to be checking if an email date exists, then checking if it was today..and if not then insert into the tstEmail table.

This is from the rstTable! recordset.

It does not appear that this first section is moving through the records in the rstTable.I think you need another Do While Not RstLate.EOF ..rstLate.MoveNext..Loop on the outside of that first section

strClientName in between the two inner loops should be reading a client name from the rstLate recordset, otherwise, it will not be properly checking against the rstTable recordset for a matching dealer...




sqlEmailSent = "Update [TableName] Set [EmailSent] = 1" & _
"Where [ClientName] = '" & RstLate![ClientName] & "'"

DoCmd.RunSql sqlEmailSent

[samohtwerdna]

No I am sure I am not being clear, because I have effectively managed to confuse myself.

Anyway, I want to loop through the rstLate to collect at least the CleintName and the OrderNumber and the EmailAddress then append to that the current date. Then I want to loop my table that stores this appended info to see if this had already been done today - if yes exit sub - if not append the data again. Finally I want to loop through the rstLate records to combine data where the clientName is the same - gather that into a string for the email body.

Checking to see if the email has been sent is where my brain is diconnecting. Maybe I should first look at collecting the similar jobs into one email then worry about whether or not I've sent it before?

Thanks for your patience! Sorry about not being clear.

Andy

[XLGibbs]

Yes, I think some thought needs to be directed at what order things happen.

I would recommend doing it in separate subroutines rather than one just to keep the procedures separate.

I would first loop through the recordset and build one table that contains the late information, including and a blank email sent date

Then, loop through that table to send your emails. Since there were multiple issues being addressed concurrently herein, that point seems to have been lost somewhere.

So your loop to gather the records into one place is one routine. Followed by a routine to loop through the resulting records and send the emails. As it loops through this recordset is when you want to update the email sent field...

[samohtwerdna]

Thanks XL,

I hope your head is not spinning like mine.

So I tried to set up a simple loop to gather similar info

Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
Do While Not rstLate.EOF
If rstLate![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate![orderNumber]
End If
rstLate.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"

rstLate.MoveNext
Loop

It loops through the first record then it looks through all the other records to see if there is a match, then once its finished it sends an email - but when it moves to the next record it says "no data found" ??

What am missing in this simple loop?

[samohtwerdna]

actualy the error says "No current Record" - not "No data found"

[XLGibbs]

Try referring to them as two distinct recordsets..untested...but it would seem that the inner loop would actually move the recordset forward when you don't want to, since the rstLate.MoveNext applies to the that recordset, regardless of position in the loop


Dim rstLate2 as Recordset
Set rstLate2 = rstLate

Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
Do While Not rstLate2.EOF
If rstLate2![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate2![orderNumber]
End If
rstLate2.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"

rstLate.MoveNext
Loop


Yes, my head is spinning, but more from the work I am doing on a monster reporting database for a project at work.

[samohtwerdna]

Sorry to hear about your headache :hi:

I still get the error - and after debugging I noticed that

Do While Not rstLate.EOF = True - instead of False???

How can this be?

[XLGibbs]

Hmmm.

maybe you need to prefix it with...

rstLate.MoveFirst before the loop starts...

[samohtwerdna]

Uh, wont that put me into an infinite loop?

[XLGibbs]

Not if you do it OUTSIDE the beginning of the Do While...like above it...come on now, use some of that noggin, granted it hurts a little...

[samohtwerdna]

Sorry but that comes up with the same error because the inner loop goes to the last record then I try on the outer loop to move to the next record -

Nevermind I see my total stupidity after the inner loop! duh!!:banghead:

Sorry about that!!

I'll leave the rest for tommorrow

[samohtwerdna]

I thought that would work but it does not.

rstLate.MoveFirst
Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
Do While Not rstLate2.EOF
If rstLate2![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate2![orderNumber]
End If
rstLate2.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"

rstLate.MoveNext
Loop also doesn't work??
This only gets me to the second record

when I try

rstLate.MoveFirst
Do While Not rstLate.EOF
strClientName = rstLate![Dealer]
strEmailBody = "The first test"
rstLate2.MoveFirst
Do While Not rstLate2.EOF
If rstLate2![Dealer] = strClientName Then
strEmailBody = strEmailBody & rstLate2![orderNumber]
End If
rstLate2.MoveNext
Loop
SendEmail strEmailBody, "andy@wmohs.com", "New Test Late Short Orders"

rstLate.MoveNext
Loop this also does not work - So I am missing something

[samohtwerdna]

Ok so I figured out what was wrong with my Inner and Outer Loop. I had


Set rstLateI = rstLateO


I needed to set the inner loop record set the same way I did the outer loop. Not sure why that is different than above - but it worked.

So now I have:

Dim rstLateI As DAO.Recordset
Set rstLateI = CurrentDb.OpenRecordset("qryEmailTest", dbOpenDynaset, dbSeeChanges)

rstLateO.MoveFirst
Do While Not rstLateO.EOF
strClientName = rstLateO![Dealer]
strEmailBody = "This Email was sent to address short orders that have been processed by WmOhs but for some reason have not bee signed off yet."
rstLateI.MoveFirst
Do While Not rstLateI.EOF
If rstLateI![Dealer] = strClientName Then
strEmailBody = strEmailBody & "<h3>Your order</h3>" & _
rstLateI![orderNumber] & "<br />" & _
rstLateI![Name] & " <p> Was Processed " & rstLateI![DaysElapsedSinceProccessed] & " " & "days ago! <br /></p>"
End If
rstLateI.MoveNext
Loop

SendEmail strEmailBody & "Please let us know if you have any questions", "andy@wmohs.com", "Late notice for Short Orders"
rstLateO.MoveNext

Loop

This goes through the loops correctly - but for some reason when it sends the email it sends out as many emails as gathered info. For example if client "KSD" has 3 late jobs - it correctly compiles the three orders into one email, but then it send that email out 3 times. I assume this is because when I loop through the rst the first time I hit "KSD" I gather all the other "KSD" jobs with the inner loop then send an email off, but then I enter back into the Outer loop and find anouther "KSD". This shows me why I need a emailSent flag.

For the emailSent flag you showed me:

sqlEmailSent = "Update [qryEmailTest] Set [EmailSent] = 1" & _
"Where [Dealer] = '" & rstLateO![Dealer] & "'"

DoCmd.RunSQL sqlEmailSent
That I assume would be inserted right after my SendEmail
Then I would and an IF statement before the inner loop that would check this field. But when I set up my code like:

Do While Not rstLateO.EOF
strClientName = rstLateO![Dealer]
strEmailBody = "This Email was sent to address short orders that have been processed by WmOhs but for some reason have not bee signed off yet."
rstLateI.MoveFirst
If rstLateO![EmailSent] = 1 Then
Do While Not rstLateI.EOF
If rstLateI![Dealer] = strClientName Then
strEmailBody = strEmailBody & "<h3>Your order</h3>" & _
rstLateI![orderNumber] & "<br />" & _
rstLateI![Name] & " <p> Was Processed " & rstLateI![DaysElapsedSinceProccessed] & " " & "days ago! <br /></p>"
End If

rstLateI.MoveNext
Loop
End If
SendEmail strEmailBody & "Please let us know if you have any questions", "andy@wmohs.com", "Late notice for Short Orders"

sqlEmailSent = "Update [qryEmailTest] Set [EmailSent] = 1" & _
"Where [Dealer] = '" & rstLateO![Dealer] & "'"

DoCmd.RunSQL sqlEmailSent

rstLateO.MoveNext

Loop

I get A syntax error missing operator on the DoCmd.RunSQL sqlEmailSent

Am I at least getting closer??

[XLGibbs]

You might need to add arguments for the connection necessary...to the point of identifying a recordset to perform the sql on...

[samohtwerdna]

Ok I think that qryEmailTest is the record set that I want to perform the sql on - Didn't I set that with the "Update [qryEmailTest] ??

[XLGibbs]

What I mean is, you have to establish a connection to a database to perform the update in...

[XLGibbs]

Samoh...If you have a sanitized version of the database, i can take a look at using your table structure

.....it is kind of hard to provide correct syntax out of context....

I know this is giving you fits, so if you want to you can email me a copy...my email addy is in my profile