Email Automation

[Chad]

Hello, I want to be able to send an email with a message stating the persons
name and a date from a query in my DB. I wanted this to run in the background
with no pop up windows ect when the DB is opened. The query I made is named
qrySendEmail and it has 2 fields "EmployeeName" and "PeriodRatedTo". How
could I do this? Thanks!

 

[Chad]

Sorry I forgot to add that I wanted the email to send 30 days f=prior to the
"PeriodRatedTo" date...

 

[Dennis]

If your company is using MS Exchange, use the SendObject command in VBA.

 

[Chad]

How would I do this? And what and where would the code look like and be?
Thanks!

 

[Dennis]

Just open up the VBA window in Access, click on HELP, and read up on the
SendObject command. There's way more information than I can provide you with.
The HELP entry on it will make it very clear as to how it's used.

You'll place a button control on your form, and in its ON CLICK event,
you'll spin through the database and execute the SendObject command for each
record.

Here's the code I use to spin through a table:


Dim dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim i as integer

Set recSet = New ADODB.Recordset

recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic

recSet.ActiveConnection = CurrentProject.Connection
recSet.Open "SELECT * from YourTableNameHere WHERE YourSelectionCriteriaHere"

for i = 1 to recSet.RecordCount
' DO STUFF HERE (You know, like format and send an email)

recSet.MoveNext
next i

recSet.Close
Set recSet = Nothing
dbConn.Close
Set dbConn = nothing

' ------------- ALL DONE ---------------

 

[Arvin Meyer [MVP]]

There are email examples at:

http://www.datastrat.com/Download/EmailSenate2K.zip

and:

http://www.datastrat.com/Code/OutlookEmail.txt

 

[Chad]

I have a query named "qrySendEmail" that if there is a review coming up
within 30 days it shows the name of the person thats review is coming up
"EmployeeName" and the date the review is due "PeriodRatedTo". I want this to
run and auto send "In background" when the DB is opened. I want it to send to
3 email addresses with the subject "Reviews Due" and the memo field would be
the "EmployeeName and "PeriodRatedTo" date. Im clueless on how to write and
incorpate this with code into my DB can someone help me with the code and
where to enter it? Thanks!

 

[Dennis]

Actually, what I'd do is create a macro that executes code in a module. Then
I'd create a CMD file which runs Access via the command-line. In that
command-line, you can specify what to execute when the application starts.
Once you know THAT works, add that CMD file to Windows scheduler to run every
day at a predefined time. That way, YOU never have to touch it. It's all
automatic. You could even code it up to send YOU an email every time it runs,
letting you know that it DID run and complete successfully.

I can't write your code for you. You have the tables in-hand (I don't), and
an example of how to do VBA-based ADO table lookups (per my earlier post on
this thread). Armed with that information, you should be able to write a few
SQL queries to open RecordSets that qualify for email notices. Use the
DateDiff function to determine the 30-day window.

Really, that's all there is to it.

- What is "today's date"?
- What date is 30 days ago from today?
- Lookup all emplyees whose review due-date is > the 30-days ago date and <=
today
- Create the email and send
- Set a "was an email sent?" flag in the employee table to YES so they don't
get an email every day, but just once
- Clear the flag and reset the review due-date once the review has been done

 

[Ed Robichaud]

BTW, SendObject also works with Outlook and Outlook Express - no Exchange
needed.

 

[Beth]

I am trying to change the from field (via Automation) so that the staff are
forced to send emails from our General Mailbox. All code examples I have
found do not include a from field.

 

[Arvin Meyer [MVP]]

In Outlook, one must be logged into a profile to send mail. You can have
multiple profiles, but you need to log into your system for your own, or
change the profile (with appropriate permissions) once you are logged in.
Can you imagine the problems it would cause if someone could change their
profile to any other user?

The code I posted will use whichever profile that's active as the sender.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com