S
Stephanie
Hi. Fabulous MVPs showed me how to create an email list
from my recordset. I'm wondering how complicated the
SELECT statement can be within the recordset. Currently,
I have:
Private Sub EmailHydrant_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null And
[EmailHydrant] = -1")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
'line that would open Outlook to a new message with the
bcc:
'box populated with the list you built in strTo
DoCmd.SendObject acSendNoObject, , , , , strTo, "Hydrant
Email Link"
End Sub
I created a query using the query wizard and tried to put
this SELECT statement in the code, but it didn't work.
Too complicated? Syntax incorrect for VBA? My SQL is a
bit weak. Any suggestions? Can it be done? This way I
could have an access created email list based on current
handler members and not have to keep the email information
in more than one place. Thanks for your help, Stephanie
("SELECT Contacts.EmailName FROM (Contacts INNER JOIN
MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON
Contacts.MemberTypeID = MemberType.MemberTypeID
WHERE (((Contacts.EmailName) Is Not Null) AND
((MemberStatus.MemberStatus)="Current-Active") AND
((MemberType.MemberType)="Handler") AND ((Contacts.Member)
=Yes))")
from my recordset. I'm wondering how complicated the
SELECT statement can be within the recordset. Currently,
I have:
Private Sub EmailHydrant_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Set rst = CurrentDb.OpenRecordset("SELECT [EmailName]
FROM Contacts WHERE [EmailName] Is Not Null And
[EmailHydrant] = -1")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
'line that would open Outlook to a new message with the
bcc:
'box populated with the list you built in strTo
DoCmd.SendObject acSendNoObject, , , , , strTo, "Hydrant
Email Link"
End Sub
I created a query using the query wizard and tried to put
this SELECT statement in the code, but it didn't work.
Too complicated? Syntax incorrect for VBA? My SQL is a
bit weak. Any suggestions? Can it be done? This way I
could have an access created email list based on current
handler members and not have to keep the email information
in more than one place. Thanks for your help, Stephanie
("SELECT Contacts.EmailName FROM (Contacts INNER JOIN
MemberStatus ON Contacts.MemberStatusID =
MemberStatus.MemberStatusID) INNER JOIN MemberType ON
Contacts.MemberTypeID = MemberType.MemberTypeID
WHERE (((Contacts.EmailName) Is Not Null) AND
((MemberStatus.MemberStatus)="Current-Active") AND
((MemberType.MemberType)="Handler") AND ((Contacts.Member)
=Yes))")