auto email late clients

[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

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

1. A recordset of late job's
2. A function to email each client
3. A table to store the last email date

I need to:

1. Collect certain job info for clients with multiple jobs late
2. write over the old info in the store table for sent emails so the table doesn't get enormous
3. 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:

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

Any thoughts??

[XLGibbs]

First things first, some of this is easy..

You can clear the email sent table using a Truncate Table statement
[VBA]
sqlTruncate = "Truncate Table [TableName]"
[/VBA]

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..
[VBA]
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...
[/VBA]

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:
[VBA]
'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
[/VBA]

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?

[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?
[VBA]
Dim rstLate As DAO.Recordset, rstTable As DAO.Recordset
[/VBA]
[VBA]
Set rstLate = CurrentDb.OpenRecordset("qryEmailTest", dbOpenDynaset, dbSeeChanges)
Set rstTable = CurrentDb.OpenRecordset("tblTestEmail", dbOpenDynaset, dbSeeChanges)
[/VBA]

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

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

DoCmd.RunSql sqlEmailSent
[/VBA]

instead of the
[VBA]
rstLate!EmailSent = 1
[/VBA]

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:

[VBA] 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[/VBA]

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



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

DoCmd.RunSql sqlEmailSent [/VBA]

[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
[VBA]
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
[/VBA]
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

[VBA]
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
[/VBA]

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

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

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

How can this be?