Hi Suzy,
Wow, lots of activity on this thread--I just got home from work and read
through the posts. Have you had success yet using Smartin's help? I didn't
include this in my original post on this thread, but you might want to use
the Nz function to convert any nulls into zero length strings. For example:
DoCmd.SendObject _
To:=Nz(strTo,""), CC:=Nz(strCC,""), BCC:=Nz(strBCC,""), _
Subject:=strSubject, EditMessage:=True
The code that Smartin gave you in his first reply for the BCCList function
requires that you have a reference set to the "Microsoft DAO 3.6 Object
Library", since this is DAO code. (Use the 3.6 Object Library for Access 2000
or later; use the 3.51 Object Library for Access 97). In addition, I would
like to offer some improvements to his code:
Function BCCList() As String
' returns a semicolon-separated list of BCC recipients
Dim DB As Database
Dim SQL As String
Dim RCD As Recordset
Dim tmp As String
Set DB = CurrentDb()
SQL = "SELECT Name FROM BCCRecipients;"
Set RCD = DB.OpenRecordset(SQL)
Do While Not RCD.EOF
tmp = tmp & Trim(RCD.Fields(0).Value) & ";"
RCD.MoveNext
Loop
BCCList = tmp
End Function
1.) All procedures should include error handling
2.) Use explicit declarations for recordsets (and any other methods that are
common to the ADO library). Here is an article that I wrote that discusses
this issue:
ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/gem_tips1.html
3.) To prevent database bloat, make sure to close objects that you open:
http://tinyurl.com/2dmpw
and more specifically:
How to prevent database bloat after you use Data Access Objects (DAO)
http://support.microsoft.com/?id=289562
4.) Name is a reserved word (SQL = "SELECT Name FROM BCCRecipients;"). You
should avoid using reserved words for anything that you assign a name to in
Access.
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
5.) You should avoid using spaces (Email Address) in anything you assign a
name to in Access. You'll need to make sure to add square brackets [ ] in any
VBA code that includes an object name with spaces. Similarily, square
brackets may be needed in some situations if you use reserved words. My
advice is to avoid both potential headaches.
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Combining all these ideas together, we have the following two procedures in
a code module associated with a form. The form includes a command button
named "cmdSendMessage" (without the quotes). The form includes the following
four text boxes: txtToEmailAddress, txtCCEmailAddress, txtSubject and
txtMessage. You can set default values for these text boxes if you'd like, or
you can omit the text boxes and hard code the values into the VBA code. The
e-mail addresses from your table will be added to the BCC field.
You might run into an issue with your ISP (Internet Service Provider) where
they reject e-mail messages that include spam characteristics, specifically a
very long list of e-mail addresses. In that case, you might want to look into
using an alternate method. Here is the revised code:
'***************Begin Code*********************
Option Compare Database
Option Explicit
Private Sub cmdSendMessage_Click()
On Error GoTo ProcError
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strMessage As String
strTo = Nz(Me.txtToEmailAddress, "")
strCC = Nz(Me.txtCCEmailAddress, "")
strSubject = Nz(Me.txtSubject, "")
strMessage = Nz(Me.txtMessage, "")
DoCmd.SendObject _
To:=strTo, CC:=strCC, BCC:=Nz(BCCList, ""), _
Subject:=strSubject, MessageText:=strMessage, EditMessage:=True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdSendMessage_Click..."
Resume ExitProc
End Sub
Function BCCList() As String
On Error GoTo ProcError
' returns a semicolon-separated list of BCC recipients
Dim DB As Database
Dim SQL As String
Dim RCD As DAO.Recordset
Dim tmp As String
Set DB = CurrentDb()
SQL = "SELECT [Email Address] FROM ClientInfo;"
Set RCD = DB.OpenRecordset(SQL)
Do While Not RCD.EOF
tmp = tmp & Trim(RCD.Fields("Email Address").Value) & ";"
RCD.MoveNext
Loop
BCCList = tmp
Debug.Print BCCList
ExitProc:
On Error Resume Next 'Cleanup
RCD.Close: Set RCD = Nothing
DB.Close: Set DB = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure BCCList..."
Resume ExitProc
End Function
'***************End Code**********************
Good luck, and please let us know how it goes. Don't forget to set that
required reference to the DAO Object Library. Also, click on Debug > Compile
ProjectName before attempting to run the code. Correct any compile errors
that you might get first.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Thanks for your patience! The client database I'll be sending out to has
600+ records... don't think I want to enter each one...
Is there something I should be replacing 'Recipients' with? The information
is in the "ClientInfo" table and named "EmailAddress"
Boo hoo!
