autoemailing Please Help

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

I am in need of help with 2003. I have a database that
stores address and email address along with the date
sent. I am currenty running a report in access
downloading the emails in excel and then email the
recipients in outlook under blind copy. I need to send a
letter to individuals in the database, that will put
there email address in TO field, put the words Thank You
in the Subject line. Put a message in the Message box,
with there name after the Dear _______: and then update
the date field to say that the message was sent. Please
help me I have about two thousand emails that need to be
personalized and sent out. P.S. I work in HR, this is not
for Spam.
 
This piece of code will send a personalized email to every contact that has
an email address, assuming there is a table called "tblContacts", a field
called ContactEmail and a field called ContactLastName in that table.

Create a new module in Access, and paste this code in there. Then, run it.
*********************************

Option Compare Database
Option Explicit

Public Function SendEmail()
On Error GoTo SendEmail_Err

' This is the start of variable declarations
Dim cnn As Connection
Dim rst As New ADODB.Recordset
Dim x As Integer
Dim theMessageBody As String
Dim theEmail
Dim theLastName
' This is the end of variable declarations

' This sets the connection variables and opens the recordset
Set cnn = CurrentProject.Connection
rst.Open "tblContacts", cnn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

' With the recordset selected, we will perform several steps
With rst

.MoveFirst

For x = 1 To .RecordCount

theEmail = .Fields("ContactEmail")
theLastName = .Fields("ContactLastName")

' if the email field is not empty, then it sends the email
If Not IsNull(theEmail) Then
' This is the body of the email message to go out
theMessageBody = "Dear Mr./Ms. " & theLastName & ": " & vbNewLine &
vbNewLine & _
"We appreciate that you have chosen us to deliver the database
system that will help you run your business in a more efficent way. We look
forward to working with you on this project. Thanks again!" & vbNewLine &
vbNewLine & _
"Regards," & vbNewLine & vbNewLine & _
"Sal Rosario" & vbNewLine & _
"Database Consultant" & vbNewLine & _
"www.salrosario.com"

' This sends the email to the current record
DoCmd.SendObject , "", "", theEmail, "", "", "Thank You",
theMessageBody, False, ""
End If

.MoveNext
Next

End With

SendEmail_Exit:
Exit Function

SendEmail_Err:
MsgBox Error$
Resume SendEmail_Exit

End Function
 
Back
Top