MS Access 2000, scheduled emails, queries, and other such fun

[Ladi]

Whelp, I've run into a problem I can't even find an approach for, so hopefully someone around here is either guru-like with Access:

I need to write a scheduled, twice-weekly (by days of the week) macro (module? something else?) that will run a query for every entry in a table column and email out the field for which the query was run and the results of that query (there will be a variable number of results, including zero), preferably within the body of the email.

Example:
To: <entry>
Subject: <text string>
Body: <text string> <result> <result> <result>

I know it's not a very clear explanation, but that's probably why I have no idea how to approach a solution beyond writing the query (which is, btw, based off another query, if this makes a difference to you).

So questions? comments? suggestions? solutions? links?

~Ladi

 

[Ladi]

ttt

 

[Ladi]

please?

 

[KB]

How are you sending out the emails? My recommendation is set taskmanager to run a VBscript (Windows Scripting Host). Using vbscript you can connect to the access database, run a SQL statement to on the database, and even send emails if you are running MS Outlook.

But you will need to know SQL and VBscript of course.

If you don't know vbscript you can learn some of it here: http://wsh.glazier.co.nz/ and http://msdn.microsoft.com/scripting/default.htm?/scripting/vbScript/doc/vbsfsotutor.htm

If you know vbscript this is what your code might look like:

Set Conn=CreateObject(&quot;ADODB.Command&quot
Conn.ActiveConnection=&quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; &amp; &quot;AccessDB.mdb&quot; &amp; &quot;;&quot;
Conn.CommandText=&quot;SELECT * FROM table_name&quot;
Set record=Conn.execute()

Set Outlookobj=CreateObject(&quot;Outlook.Application&quot
Set mapiobj=Outlookobj.GetNameSpace(&quot;MAPI&quot

Do Until Record.EOF
'set the message attributes
to=record.fields(&quot;email_address&quot
sub=record.fields(&quot;subject&quot
body=record.fields(&quot;body&quot

Set message=mapiobj.CreateItem(num)
num=num+1 'increment number of messages
message.Recipients.Add to
message.Subject=sub
message.Body=body
message.Send 'email out the message
Loop

Good Luck!

 

[Ladi]

The only issue I'm having right now with the actual sending of the email is formatting results; the database is already sending out several emails for various actions. I'm not too worried about the formatting since it's an internal application and making it pretty can be worried about later.

My main concern is getting the query to be run for each entry in the table and sending the results to a different person for each query (entry in table = person results sent to). Although task manager is a possibility, I'd like this to be self-contained within the Access application as there is no guarantee that the database will stay open on a specific computer. Also, there is the possibility that the database will have more than one instance open, but obviously, more than one set of emails cannot be sent out.

~Ladi

 

[Xanathar]

bump to the top (this is Ladi...really)