By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,457 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Automating email from Access

P: n/a
Hey guys....

I need to find a way to loop through all of my records in the database
( so essentially query the database) based on a Date Field ("DueDate")
and if any record falls within 2 months of this date, then Run a
Command I have already written that will send an email to my specified
person.

I already have the email side of things "down pat" BUT I just need to
automate this process. Any suggestions?

Jun 1 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
dim rst as adodb.recordset
rst.open "Select ml.* From MailiingList As ml;"
rst.movefirst
do until rst.eof = true
CallYourEmailRoutine(rst.fields("UserEmailAddress" ))
rst.movenext
loop

How do you plan to combat the prompts imposed by Outlook when invoking it
from Access?
Jun 1 '06 #2

P: n/a
w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
dim rst as adodb.recordset
rst.open "Select ml.* From MailiingList As ml;"
rst.movefirst
do until rst.eof = true
CallYourEmailRoutine(rst.fields("UserEmailAddress" ))
rst.movenext
loop

How do you plan to combat the prompts imposed by Outlook when invoking it
from Access?

Why not Express click Yes?
Jun 1 '06 #3

P: n/a

w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
dim rst as adodb.recordset
rst.open "Select ml.* From MailiingList As ml;"
rst.movefirst
do until rst.eof = true
CallYourEmailRoutine(rst.fields("UserEmailAddress" ))
rst.movenext
loop

How do you plan to combat the prompts imposed by Outlook when invoking it
from Access?

No you guys seem to have miss-read my question. I ALREADY have the
email aspect covered....including the Security Prompt when a thrid
party application tries to access Outlook, this is not the Issue. What
I need help in is quering the database by the "DueDate", and if the
"DueDate" is within the next two months (i.e compare the systems
date...to DueDate, if the DueDate falls within the next two months)
then it needs to run automatically the Email aspect (which I have
already written)? So essentially when I need is CODE that will Query
the Database (auto ex.) on windows start up....check to see if the
DueDate is in the next two months...if YES...then execute an Email,
which I have already written, called "Command 38", if someone could
provide me with the neccessary code and instructions of where to
implement this within my database....it would be greatly apprecated!
Kind Regards

Liam

Jun 1 '06 #4

P: n/a
Just curious. I've seen it advertised, but know nothing about it firsthand.


On 5/31/2006 9:02:00 PM, Bob Alston wrote:
w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
dim rst as adodb.recordset
rst.open "Select ml.* From MailiingList As ml;"
rst.movefirst
do until rst.eof = true
CallYourEmailRoutine(rst.fields("UserEmailAddress" ))
rst.movenext
loop

How do you plan to combat the prompts imposed by Outlook when invoking it
from Access?

Why not Express click Yes?

--
Science is organized common sense where many a beautiful theory is killed by an ugly fact (Thomas Huxley).
Jun 1 '06 #5

P: n/a
Change "Select ml.* From MaillingList As ml" to "Select ml.* From
MaillingList as ml Where " & strMyWhere

in strMyWhere, put your conditions, such as [SomeDate] <= #" & MyDateField &
& "#"

etc.
Jun 1 '06 #6

P: n/a

w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
Change "Select ml.* From MaillingList As ml" to "Select ml.* From
MaillingList as ml Where " & strMyWhere

in strMyWhere, put your conditions, such as [SomeDate] <= #" & MyDateField &
& "#"

etc.


sorry dude...can I have that in english....I am obviously a little slow
on the up take and instructions when it come to V.B

Regards

Jun 1 '06 #7

P: n/a

Li****@awamarine.com.au wrote:
w_a_n_n_a_l_l_ -@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:
Change "Select ml.* From MaillingList As ml" to "Select ml.* From
MaillingList as ml Where " & strMyWhere

in strMyWhere, put your conditions, such as [SomeDate] <= #" & MyDateField &
& "#"

etc.


sorry dude...can I have that in english....I am obviously a little slow
on the up take and instructions when it come to V.B

Regards


where abouts to I place this code....do I create it as a module....and
get a macro to run it on start-up, any suggestions?

Jun 1 '06 #8

P: n/a
ONe way to do this is to create a form. Put a button on that form. In the
button's _Click event, you put vb code modeled on what I submitted.

The idea is to create a recordset, and them move through that recordset, and
use the email address on each line to send an email.

If you're going to send the same email to everyone, you can just collect the
email addresses as you go and add them to an array. At the end, you create
your email item and add the email addresses from the array into the email
items address list. If you're sending different email to each recipient,
you create your email item for each record in the recordset, add the email
address from only one record, and send the item before moving to the next
record.

You indicated thatyou wanted to use some date criteria to choose who would
get an email. You can either incorporate the date criterion or criteria
into a where clause that you use when creating your recordset, or you can
move through the recordset and determine by examining the fields in each
record whether or not to send and email and perhaps what kind of email to
send.
Jun 1 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.