Email list into BCC field

[Tomdhu]

I've got a Contacts & Events database in Access 2K where contacts
attend various events.

At present, where I want to group e-mail all the attendees who are
going to attend a particular event, I run a macro which runs a select
query which strips out and exports a list of their e-mail addresses (
separated by commas) into a text file. I then copy the CSV e-mail
addresses and paste them into the BCC field of Outlook.

I'd love to have a means of achieving this in one step. Is there a way?

If it's of interest , my select query is as follows:-

SELECT [email] & "," AS EMAILADDRESS
FROM Contacts LEFT JOIN tblAttendance ON Contacts.Person ID =
tblAttendance.P ersonID
WHERE (((Contacts.EMA IL)<>"") AND
((tblAttendance .EventID)=[Forms]![FrmEvent2].[EventID]));

Many thanks

Jim Tomdhu

[pietlinden]

Tomdhu wrote:

I've got a Contacts & Events database in Access 2K where contacts
attend various events.

At present, where I want to group e-mail all the attendees who are
going to attend a particular event, I run a macro which runs a select
query which strips out and exports a list of their e-mail addresses (
separated by commas) into a text file. I then copy the CSV e-mail
addresses and paste them into the BCC field of Outlook.

I'd love to have a means of achieving this in one step. Is there a way?

If it's of interest , my select query is as follows:-

SELECT [email] & "," AS EMAILADDRESS
FROM Contacts LEFT JOIN tblAttendance ON Contacts.Person ID =
tblAttendance.P ersonID
WHERE (((Contacts.EMA IL)<>"") AND
((tblAttendance .EventID)=[Forms]![FrmEvent2].[EventID]));

Many thanks

Jim Tomdhu

You can automate Outlook (search here or there's code at Danny
Lesandrini's website
www.amazecreati ons/datafast) and then you can open the query inside a
recordset and then loop through it, adding to the BCC list of the
message.

[Tomdhu]

Hi,

Thanks, but I ought to have mentioned that I'm a relative Newbie and
not too good at doing VB.
I scanned the site you mentioned but couln't find anything suitable
however I did find an earlier post from Albert Kallai in 2002 as
follows:-
Private Sub cdmEmail_Click( )
Dim rstMailList As Recordset
Dim strMySql As String
strMySql = "select emailname from qryGroupList where (emailname is
not null) " _
& " and (Mailto = True) order by emailname"
Set rstMailList = CurrentDb.OpenR ecordset(strMyS ql)
Call BulkEmail(rstMa ilList)
rstMailList.Clo se
Set rstMailList = Nothing
End Sub
Public Sub BulkEmail(rstMa ilList As Recordset)
Dim lngMailCount As Long
Dim strWhoList As String
If rstMailList.Rec ordCount = 0 Then
MsgBox "No email names in selected list", vbInformation, "No
email names found"
Else
' display names found...give chance
rstMailList.Mov eLast
rstMailList.Mov eFirst
lngMailCount = rstMailList.Rec ordCount
If MsgBox("There are " & lngMailCount & " email names in this
list" & vbCrLf _
& "Do you want to make a email for this list?",
vbQuestion + vbYesNo) = vbYes Then
' build the list
strWhoList = ""
Do While rstMailList.EOF = False
If InStr(rstMailLi st(0), "@") > 0 Then
strWhoList = strWhoList & rstMailList(0) & ";"
End If
rstMailList.Mov eNext
Loop
If InStr(strWhoLis t, ";") > 0 Then
strWhoList = Left$(strWhoLis t, Len(strWhoList) - 1)
End If
DoCmd.SendObjec t , , , , , strWhoList, "Greetings from My
Company"
End If
End If
End Sub

I modified the field names to suit but it baulks at this line:-

Set rstMailList = CurrentDb.OpenR ecordset(strMyS ql)

Bearig in mind I run Access2000, is there any reason it would stutter
at this stage?

Jim

pi********@hotm ail.com wrote:

Tomdhu wrote:

I've got a Contacts & Events database in Access 2K where contacts
attend various events.

At present, where I want to group e-mail all the attendees who are
going to attend a particular event, I run a macro which runs a select
query which strips out and exports a list of their e-mail addresses (
separated by commas) into a text file. I then copy the CSV e-mail
addresses and paste them into the BCC field of Outlook.

I'd love to have a means of achieving this in one step. Is there a way?

If it's of interest , my select query is as follows:-

SELECT [email] & "," AS EMAILADDRESS
FROM Contacts LEFT JOIN tblAttendance ON Contacts.Person ID =
tblAttendance.P ersonID
WHERE (((Contacts.EMA IL)<>"") AND
((tblAttendance .EventID)=[Forms]![FrmEvent2].[EventID]));

Many thanks

Jim Tomdhu

You can automate Outlook (search here or there's code at Danny
Lesandrini's website
www.amazecreati ons/datafast) and then you can open the query inside a
recordset and then loop through it, adding to the BCC list of the
message.