Email distribution list idea.

Okay, I think this one might be little tough. Though I'm learning very
quickly that there's nothing too difficult for all you geniuses out there.

Here's what I'd like to do. I've got a Personel table with related contact
information and I've got a Sites table. there's a one to many relationship
between the Personel and Sites tables. Many people can be a one site. What
I'd like to do is have a form with a drop down list box that shows all of the
site names, and a button. When I click this button I want a blank email to
open up that has all of the email addresses of the individuals that are
related to that site in the To: field.

I think the code should probably look something like this but different:

***************Begginning of Code*********************
Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

Set objOutlook = New Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

EmailAddr = Me.Email
CopyEmailAddr = ""
Subj = "Information about the FSR Update database."
Body = ""
PathName = ""

With objMail
.To = EmailAddr
.cc = CopyEmailAddr
.Subject = Subj
.Body = Body
.NoAging = True
.Display
End With
********************End of Code***********************

The EmailAddr variable will have to contain all of the email addresses of
the individuals of a certain site. I probably need a function that finds out
those email addresses that assigns them to a variable that I can call
somehow. Anyway, I have no idea. Any help would be appreciated. Thanks a
ton for all the help I've received in previous posts.

Hi Jonathan,

To build a list of all the email addresses you simply open a recordset that
returns all the email addresses for the given site, loop through it and
build your string. First, build a query that selects the Personel for the
selected Site. This is pretty easy to do - if your combo for sites is named
cboSiteId and the form is named "frmEmailer" then youre query would be
something like this:

Select EmailAddress from tblPersonel where
Siteid=forms!frmEmailer!cboSiteID;

Save this query as "qrySelEmailBySite"

Then the code to use this query to build a string would be something like
this:

dim rst as dao.recordset
dim db as dao.database
dim strEmail as string
set db=currentdb()
set rst=db.openrecordset("qrySelEmailBySite")
with rst
if not .bof and .eof then
do until .eof
strEmail=strEmail & .fields("EmailAddress") & "; "
.movenext
loop
endif
.close
end with

strEmail will contain the list of email addresses.

For more information on this you might want to take a look at the following
article - this one sends a separate email to each address but the article
might give you some ideas anyway:

ACC97: How to Use a Recordset to Send Outlook E-Mail to Multiple Recipients
http://support.microsoft.com/?id=318881


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Jonathan Brown wrote:
Okay, I think this one might be little tough. Though I'm learning very
quickly that there's nothing too difficult for all you geniuses out there.

Here's what I'd like to do. I've got a Personel table with related
contact information and I've got a Sites table. there's a one to many
relationship between the Personel and Sites tables. Many people can be a
one site. What I'd like to do is have a form with a drop down list box
that shows all of the site names, and a button. When I click this button
I want a blank email to open up that has all of the email addresses of
the individuals that are related to that site in the To: field.

I think the code should probably look something like this but different:

***************Begginning of Code*********************
Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

Set objOutlook = New Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

EmailAddr = Me.Email
CopyEmailAddr = ""
Subj = "Information about the FSR Update database."
Body = ""
PathName = ""

With objMail
.To = EmailAddr
.cc = CopyEmailAddr
.Subject = Subj
.Body = Body
.NoAging = True
.Display
End With
********************End of Code***********************

The EmailAddr variable will have to contain all of the email addresses of
the individuals of a certain site. I probably need a function that finds
out those email addresses that assigns them to a variable that I can call
somehow. Anyway, I have no idea. Any help would be appreciated. Thanks
a ton for all the help I've received in previous posts.

I started with the code below and made a few modifications. It's posted
under another discussion thread - search for "send email" and it should be in
a thread started by a user named Connie. So far it's working pretty well for
me.

"Sandra Daigle" wrote:

Hi Jonathan,

To build a list of all the email addresses you simply open a recordset that
returns all the email addresses for the given site, loop through it and
build your string. First, build a query that selects the Personel for the
selected Site. This is pretty easy to do - if your combo for sites is named
cboSiteId and the form is named "frmEmailer" then youre query would be
something like this:

Select EmailAddress from tblPersonel where
Siteid=forms!frmEmailer!cboSiteID;

Save this query as "qrySelEmailBySite"

Then the code to use this query to build a string would be something like
this:

dim rst as dao.recordset
dim db as dao.database
dim strEmail as string
set db=currentdb()
set rst=db.openrecordset("qrySelEmailBySite")
with rst
if not .bof and .eof then
do until .eof
strEmail=strEmail & .fields("EmailAddress") & "; "
.movenext
loop
endif
.close
end with

strEmail will contain the list of email addresses.

For more information on this you might want to take a look at the following
article - this one sends a separate email to each address but the article
might give you some ideas anyway:

ACC97: How to Use a Recordset to Send Outlook E-Mail to Multiple Recipients
http://support.microsoft.com/?id=318881


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Jonathan Brown wrote:
Okay, I think this one might be little tough. Though I'm learning very
quickly that there's nothing too difficult for all you geniuses out there.

Here's what I'd like to do. I've got a Personel table with related
contact information and I've got a Sites table. there's a one to many
relationship between the Personel and Sites tables. Many people can be a
one site. What I'd like to do is have a form with a drop down list box
that shows all of the site names, and a button. When I click this button
I want a blank email to open up that has all of the email addresses of
the individuals that are related to that site in the To: field.

I think the code should probably look something like this but different:

***************Begginning of Code*********************
Dim objOutlook As New Outlook.Application
Dim objMail As MailItem

Set objOutlook = New Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)

EmailAddr = Me.Email
CopyEmailAddr = ""
Subj = "Information about the FSR Update database."
Body = ""
PathName = ""

With objMail
.To = EmailAddr
.cc = CopyEmailAddr
.Subject = Subj
.Body = Body
.NoAging = True
.Display
End With
********************End of Code***********************

The EmailAddr variable will have to contain all of the email addresses of
the individuals of a certain site. I probably need a function that finds
out those email addresses that assigns them to a variable that I can call
somehow. Anyway, I have no idea. Any help would be appreciated. Thanks
a ton for all the help I've received in previous posts.