How to send an email to each row for specific date?

Try something like this:

Dim rst as recordset, strQuery as String, strMessage as string

strQuery = "SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #" & Now() & "#;"

set rst = currentdb.openrecordset(strQuery, DbOpenDynaset)

with rst

if Not .Eof and Not .Bof Then

Do while not .eof

strMessage = "This is your message, including " & .fields("Tracking") & " which goes in your e-mail."

DoCmd.SendObject acSendNoObject, , , .Fields("Email"), , , "E-mail Message Title", strMessage, False

.movenext

Loop

End If

End With

Where exactly would I put this code now?  I have tried putting it behind a command button in a form, and as a macro, but am missing something obviously.

Where exactly would I put this code now?  I have tried putting it behind a command button in a form, and as a macro, but am missing something obviously.

Sorry, slight mistake in typing this in.

The line:

strQuery = "SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #" & Now() & "#;"

Should read:

strQuery = "SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #" & Date() & "#;"

In putting this behind a form I get an invalid arguement and it stops on:

Set rst = CurrentDb.openrecordset(strQuery, DbOpenDynaset)

The typing mistake you said you made, they look identical to me??

Thanks for your help :)

The difference is that the second version uses Date() and not Now() to evaluate the date. Now() also returns the time, so, matching a date to a date and time will result in no records being returned.

As for the error, try inserting DEbug.Print strQuery before the Set rst... line and then go to the debug window (Ctrl + g) and copy the text of the query and paste it here.

SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #2/28/2003#;

That is what it displayed in the bottom window.

Thanks so much for helping me through this.

Tim

OK, there is nothing wrong with that code. If you copy it again, then create a new query, close the box containing the available tables & queries, and the view the SQL statement, you can paste the code into the SQL window.

If you can do that, does the query run?

If it does, then there is noting wrong with the table and/or field names. If so, can you copy the entire code and paste it? Maybe I'll be able to spot something.

If you do get an error when attempting to run the query, switch back to design view and see if Access displays any of the fields differently, e.g. prefixing them with Expr1:

If so, it could be that a field name is incorrect and mis-spelled.

Ok, I took the entire code, copied it and then went to a new query.  I did not select tables, closed the boxes.  Then went to SQL view, pasted the code and ran it, I received an error "
Invalid SQL statement; expected 'DELETE insert, procedure, etc."

I am obviously doing something wrong here.

Dim rst As Recordset, strQuery As String, strMessage As String


strQuery = "SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #" & Date() & "#;"


Debug.Print strQuery

Set rst = CurrentDb.openrecordset(strQuery, DbOpenDynaset)

With rst

If Not .EOF And Not .BOF Then

Do While Not .EOF

strMessage = "This is your message, including " & .Fields("Tracking") & " which goes in your e-mail."

DoCmd.SendObject acSendNoObject, , , .Fields("Email"), , , "E-mail Message Title", strMessage, False

.MoveNext

Loop

End If

End With
End Sub

Did you copy the whole function ie from the Dim statement to the end sub, or just

SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #2/28/2003#;

?

I've just copied and run your function and it runs fine.

Ahh, Ok.  Well I pasted the line:

SELECT Email, Tracking, DateEntered FROM CustomerInformation WHERE DateEntered = #2/28/2003#;


And it displayed 3 records with correct information

Hmm.. OK

I've just noticed that Access hasn't correctly capitalised the word OpenRecordset, which suggests that it is not recognising this as a valid command.

Can you check your references (Tools, References) and see what you got selected? It may that either a reference that Access requires is not selected or that another reference is selected but is marked as MISSING.

Lots of available references, 4 are checked,

VB for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Any others that should be checked?

Ok, I've tried matching your references and I'm now getting the same error.

You have ADO 2.1 installed, whereas I'm using DAO.

You have two choices, either install DAO instead of ADO, or, change this line to open a recordset under ADO.

As I have no experience of using ADO, I can't recommend either approach as I don't know what effect this will have on the rest of your database.

Maybe in a few months, when we've upgraded to Office XP I'll be able to answer you... but I reckon you'll want your answer before then.

http://www.mvps.org/access/bugs/bugs0031.htm may help you in your search.

I think I like the option of using ADO 2.1, just like you :)  Can I install that on my Office XP ?

Err, you are using ADO 2.1

If you want to use DAO, uncheck the ActiveX Data Objects reference and scroll down to Microsoft Data Access Objects and select that.

Well, we're almost there.  It brings up my Eudora Email and types in the info, no problem.  The only thing that it doesn't do is "send" it.  Is there a way to do that as well?  I also have Outlook XP that I can send mail through, or directly to mail server.

90% there :)

Thanks,

Tim

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to jonsykes
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer