Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-02-26
21
Medium Priority
?
314 Views
Last Modified: 2012-05-04
I have a table "CustomerInformation".  In it I have several fields, 3 notable ones, "Email", Tracking", and "DateEntered".

I want to send emails at the end of the day to each row (person) that has a "DateEntered" of today.  In each email I want some generic text, and I want to include the field "Tracking".  The email will be sent to "Email" email address.

I can't seem to figure out how to do this.  Any suggestions?

Thanks,

Tim

tim.macking@blackhawk.com
0
Comment
Question by:imcre8ive
  • 10
  • 9
20 Comments
 
LVL 1

Expert Comment

by:jonsykes
ID: 8032217
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
0
 

Author Comment

by:imcre8ive
ID: 8038205
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.
0
 

Author Comment

by:imcre8ive
ID: 8038248
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Expert Comment

by:jonsykes
ID: 8040463
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() & "#;"

0
 

Author Comment

by:imcre8ive
ID: 8041155
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 :)
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8041219
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.
0
 

Author Comment

by:imcre8ive
ID: 8041414
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
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8041497
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.
0
 

Author Comment

by:imcre8ive
ID: 8041588
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.
0
 

Author Comment

by:imcre8ive
ID: 8041599
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
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8041672
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.
0
 

Author Comment

by:imcre8ive
ID: 8041703
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

0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8041754
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.
0
 

Author Comment

by:imcre8ive
ID: 8041793
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?
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8041968
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.
0
 

Author Comment

by:imcre8ive
ID: 8042234
I think I like the option of using ADO 2.1, just like you :)  Can I install that on my Office XP ?
0
 
LVL 1

Expert Comment

by:jonsykes
ID: 8042306
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.
0
 

Author Comment

by:imcre8ive
ID: 8043380
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
0
 
LVL 1

Accepted Solution

by:
jonsykes earned 200 total points
ID: 8055943
The False value at the end of the docmd.sendobject command line should tell the mail package that you do not want to edit the e-mail and to send it immediately. Maybe Eudora doesn't understand what Access is telling it, because this works fine in Exchange.

If you've missed the False from the statement, then True is the default value.
0
 
LVL 18

Expert Comment

by:1William
ID: 8771989
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question