How To - Email / Reports

[Quixote]

I am new to Access so forgive me if this sounds like a 'dumb' question. I have a table (generated from a query) that contains a list of users and various courses that they have been signed up for.

What I would like to do is to send each user contained in this database an email message showing the courses for which they are currently enrolled in (some users will have one course listed while others may have many more). The email will also include the start date / start time / and lcoation of the course together with some generic messages about cancelling classes. I want the application to automatically loop around and create one email message per user.

So the question is -- how do I do this? Your help is appreciated.

 

[Chris RR]

I've done this, but my method requires VBA coding skills.

The basic idea is to start with two things. One is a query that will tell you should get email, and their email address, and you have this already. The other is to create a report that contains the info that you want to send (we take care of limiting the report to a single person later...)

You need to open a recordset based on the first query. Loop through the recordset, and for each record you:
*Open the report (passing it a "WHERE" clause to limit it to the ID of this one person)
*Output the report to some temp file (you can use the same one over and over)
*Send the email, with the report as an attachment

The next level is to have a macro fire this up, then use a scheduler to start Access and run the macro on a regular basis.

There may be a simpler approach, so let's see what other people come up with.


[This message has been edited by Chris RR (edited 11-02-2001).]

 

[MrTibbs]

Review postie. It's a low-cost command-line email program or look for pointers at e.g. http://www.emailman.com/win/tools.html which includes some tools offering the ability to email from your applications. http://www.swsoft.co.uk/index.asp?page=freesoftware offers smtpmail which may suit.
Basically you've got your list of attendee's and want to email them a standard, yet personalised, notice.
Postie can handle sending standard texts to specified email addresses. If you want to mail-merge so all your emails start 'Dear person's name' you'll need to investigate file concatenation or real (Word style) mailmerge.
It should be possible to Postie from your query using a macro or 2 rather than full-blown VBA but VBA offers finer control, once mastered.
Once you've got your query you could write a text file with the data in the right format for your chosen mailer to pick up and use.
Then launch a script to read your text file.
We don't do this for email from Access but have written something similar to create and launch ftp sessions against our Unix file-server.
We have implemented 'command line' email from our Unix based Manufacturing system to email reports to the right recipient automatically.