?
Solved

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

Posted on 2003-02-26
21
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
21 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

801 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