Can I e-mail a group that is pulled from an Access query?

W

wrens_f8

I'm trying to find a way to send an e-mail to a group of people that are
pulled using an Access query. I would ultimately need to create a menu for
this as it would be a common occurence, and e-mails would need to be sent
reguarly based on a few different queries.

If this can't be done completely in Access, any combination of using Outlook
with Access would be fine.

Thank you for any suggestions and help you can offer.

Karen
 
T

Tom Wickerath

Hi Karen,

You can use code similar to the following, to produce a concatenated list of
e-mail addresses, where EMailAddress is the name of the field, and Contacts
is the name of the table. Make the appropriate substitutions for your field
and table names.


Option Compare Database
Option Explicit

Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

strSQL = "SELECT [EMailAddress] FROM [Contacts] " _
& "WHERE [EMailAddress] Is Not Null;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![EMailAddress] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

' Debug.Print BulkEmail

ExitProc:
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function


You can then use this concatenated list of e-mail addresses to create a new
e-mail message, using the DoCmd.SendObject method with a MAPI compliant
e-mail program. This should work okay, as long as your list does not exceed
the number of e-mail addresses that you ISP might otherwise flag as spam. For
example:

Function SendEMail()
On Error GoTo ProcError

DoCmd.SendObject _
To:="YourEmailAddressHere", _
BCC:=BulkEmail, _
Subject:="Enter your subject here", _
MessageText:="Enter your text here", _
EditMessage:=True

ExitProc:
Exit Function

ProcError:
Select Case Err.Number
'User cancelled message (2293 & 2296 are raised by Outlook, not Outlook
Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure SendEMail..."
End Select
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Top