email from Access

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I have an Access database and I would like to send the
same email to all members in it. I would also like the
emails to be personalized (Dear John, Bob, etc.). Please
help me if you know how to do this.

Thank you,
Todd
 
Here is some code that you can get started with:

Dim g_db as Database
Dim g_rs as DAO.Recordset
Dim strSQL as String
Dim oApp As Outlook.Application
Dim objNewMail As Outlook.MailItem

Set oApp = New Outlook.Application
Set g_db = CurrentDb

strSQL = "Select FirstName, EMailAddr, MsgText from MyTable;"
Set g_rs = g_db.OpenRecordset(strSQL, dbOpenSnapshot)

g_rs.MoveLast
g_rs.MoveFirst
Do While Not g_rs.EOF
Set objNewMail = oApp.CreateItem(olMailItem)
With objNewMail
.To = g_rs!EMailAddr
.Subject = "Put your subject here"
.Body = "Dear " & g_rs!FirstName & ", " & chr(13) & chr(10) &
chr(13) & chr(10) _
& "additional message text which can come from a memo
field such as MsgText"
.Save
.Send
End With
g_rs.MoveNext
Loop
MsgBox "Finished sending emails!"

Set oApp=Nothing

g_rs.close
set g_rs=Nothing
 
We use Lotus Notes here, so the code I have is only for
Lotus Notes. There's a whole crapload of code floating
around out there on how to email through Outlook from
Access.

In order to find out who's got the database open, look up
how to read the .ldb file into a table. This will give
you their machine names. You'll have to keep a table of
machine names to people and then run a query. This way,
you can personalize their emails.

Other option:

Use a workspace file and make the users log into the
database. This way you can access their usernames via
CurrentUser()

Hope this helps,
Crystal
 
One little but important addendum:

You must also set a reference to the Microsoft xx.x Outlook Object Library
(where xx.x is your version) for this code to run.
 
Todd said:
I have an Access database and I would like to send the
same email to all members in it. I would also like the
emails to be personalized (Dear John, Bob, etc.). Please
help me if you know how to do this.
Click to expand...

What method do you want to use? See the Access Email FAQ at my
website for options.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Cheryl Fischer said:
One little but important addendum:

You must also set a reference to the Microsoft xx.x Outlook Object Library
(where xx.x is your version) for this code to run.
Click to expand...

And if there is the possibility of differening Outlook versions then
consider Late Binding once the code is debugged. For more info see my
tips page on this topic.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top