Need help with email code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that contains records for contact escalations. "ECBData1"
is the table that holds the data. The record contains the Status, Date
Opened, Owner, Customer and Email Sent columns. I have a query ("RemQry1")
that returns all records that have a status of open and are 2 days old. What
I need to do is enter a function that when the database is open will run
"Remqry1" and send an email for each record that is returned from the query.
The email address to send to is in a column called "Address" in "Remqry1".
Once the emails are sent I need the function to update the "Email Sent"
column to reflect "Yes" in the table "ECBData1". If anyone has an example of
the code that will be a huge help.
 
Here's some code that works for me for emailing (you'll need to substitute
your own references to fields - this can be done singly from controls on a
form or en masse with DAO or ADO). Remember to tick M/soft Outlook Object
Library in Tools, References:

Sub myEmail(myAddress, Optional mySubject)
On Error GoTo handler
Dim myOut As New Outlook.Application
Dim myItem As Outlook.MailItem

Set myItem = myOut.CreateItem(olMailItem)
myItem.Recipients.Add myAddress
If Not IsMissing(mySubject) Then
myItem.Subject = mySubject
End If
myItem.Display
Exit Sub

handler:
If Err.Number = 287 Then
MsgBox "Email cancelled"
Exit Sub
Else
MsgBox Err.Number & vbNewLine & Err.Description
End If

End Sub
 
Back
Top