# Access Database - Send E-mail To Group/Distribution List



## computerman29642 (Dec 4, 2007)

Is there a way to have an Access database insert a distribution list of e-mails in the "To" field of Outlook?

I have a database where I am able to do this with a single user, but at times I would like to send it to more than one person.


----------



## gurutech (Apr 23, 2004)

Are you sending to a single person as "[email protected]" or as "Firstname Lastname" ?

If you are using Firstname Lastname, then just replace that text with the name of the DL, and let Outlook look up the name in the GAL.


----------



## OBP (Mar 8, 2005)

If you are using the first technique that gurutech mentioned, then it is quite easy to use a Recordset to just add the selected recipients to the "To" string.


----------



## computerman29642 (Dec 4, 2007)

gurutech said:


> Are you sending to a single person as "[email protected]" or as "Firstname Lastname" ?
> 
> If you are using Firstname Lastname, then just replace that text with the name of the DL, and let Outlook look up the name in the GAL.


I am using the [email protected] method.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> If you are using the first technique that gurutech mentioned, then it is quite easy to use a Recordset to just add the selected recipients to the "To" string.


Would a table need to be setup for the distribution group(s)?


----------



## OBP (Mar 8, 2005)

I would add an "email" check box field to your current table (if you have one) then a Form to tick them for selecting who you want to sedn it to.
Or you could set up a "Groups" Table where you have group names, like "relations" & "friends" which you can then have a rletaed table with the names of the email recipients in. You would then just select a Group to send the email to.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> I would add an "email" check box field to your current table (if you have one) then a Form to tick them for selecting who you want to sedn it to.
> Or you could set up a "Groups" Table where you have group names, like "relations" & "friends" which you can then have a rletaed table with the names of the email recipients in. You would then just select a Group to send the email to.


I like the idea of selecting a group to send the e-mail.

How would I go about doiung that?


----------



## OBP (Mar 8, 2005)

Create a Table of Groups, with GroupID (Autonumber) and Group Name Fields, you could have a Group Description field as well if you like.
You then need a Recipients Table with a RecipientID, Recipient name and GroupID (Number type long) fields.
Link the 2 tables via the GroupID fields.
Have a query with the 2 tables to list all the recipients for a Group.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Create a Table of Groups, with GroupID (Autonumber) and Group Name Fields, you could have a Group Description field as well if you like.
> You then need a Recipients Table with a RecipientID, Recipient name and GroupID (Number type long) fields.
> Link the 2 tables via the GroupID fields.
> Have a query with the 2 tables to list all the recipients for a Group.


I have created the two tables, and the query. How would the two new tables work with my existing table (tblContacts)?


----------



## OBP (Mar 8, 2005)

Well if your Contacts Table contains the email addresses you could use that instead of the Recipients table by adding the GroupID to it. 
Or instead of the Recipients table containing the Contact's Name it could just contain their ID. In that way you could use it as a Many to Many table with Contacts in more than one Group, which sounds like a good option to me.


----------



## computerman29642 (Dec 4, 2007)

> Or instead of the Recipients table containing the Contact's Name it could just contain their ID. In that way you could use it as a Many to Many table with Contacts in more than one Group, which sounds like a good option to me.


I like that option as well.

Would I keep the GroupID in the Recipient Table, or move that to the contacts Table?


----------



## OBP (Mar 8, 2005)

Keep it in the recipients table so that it becomes a Many to Many.


----------



## computerman29642 (Dec 4, 2007)

Here is the structure of my three tables

*Contacts*
ID - Autonumber
First Name
Last Name
E-Mail
etc.

*Recipient*
RecipientID - AutoNumber
GroupID - Long Integer

*Group*
GroupID - AutoNumber
GroupName

Would I need to add the ID field from the Contacts Table to the Recipient Table?


----------



## OBP (Mar 8, 2005)

Yes.


----------



## computerman29642 (Dec 4, 2007)

I will need to build a query based on all three tables...correct?


----------



## OBP (Mar 8, 2005)

you need a Form to enter the Groups and Contacts in to the Recipients table and as you say a query to output all the data for the list of contacts in a group.
It must include the email address.


----------



## computerman29642 (Dec 4, 2007)

I have a form setup now to enter a new contact. Could I modify that form to do what you have described?

Would you like for me to send you a copy of the db so you can see what I have done this far?


----------



## OBP (Mar 8, 2005)

Yes, but I can't look at it until tomorrow.
I would have a seperate main/subform. The Groups on the Mainform and the Recipients on the subform with a combo to select the contacts.


----------



## computerman29642 (Dec 4, 2007)

That will be fine.


----------



## computerman29642 (Dec 4, 2007)

I have been able to get the group name to display in the "TO" field of Outlook, but unable to get Outlook to associate the e-mail address(es) that corresponds with the group name.


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> you need a Form to enter the Groups and Contacts in to the Recipients table and as you say a query to output all the data for the list of contacts in a group.
> It must include the email address.


So, I need to have the query associate the contact to the e-mail addresses?


----------



## OBP (Mar 8, 2005)

I have created the form for you, I will send it to you.
I was about to create the Query/VBA to add the Recipients email addresses to the Email, but I am busy on another database at the moment.


----------



## OBP (Mar 8, 2005)

Finished database sent today using this code
Private Sub Command9_Click()
Dim subject As String, Body As String, EmailAddress As String, rs As Object, SQL As String
Dim count As Integer, recount As Integer
'set up the sql string that opens the Groupforemail query for the selected Group on the Form
SQL = "SELECT Groupforemail.* " & _
"FROM Groupforemail " & _
"WHERE GroupID = " & Me.GroupID
'set the Recordset to the SQL string to actually open the query
Set rs = CurrentDb.OpenRecordset(SQL)
'in the recordset move to the last record (the RecordCount can be wrong if this is not done)
rs.MoveLast
'move back to the first record
rs.MoveFirst
'sert the variable recount to the number of records (not absolutlely necessary)
recount = rs.RecordCount
'start a for next loop to go through the records (could use For count = 1 To rs.RecordCount)
For count = 1 To recount
'MsgBox rs![E-Mail Address] & " " & rs.[Last Name]
'adds each record's email address to the variable EmailAddress
EmailAddress = EmailAddress & rs![E-Mail Address] & "; "
'move to the next record in the recordset
rs.MoveNext
'increment the For/Next Counter
Next count
'remove the spare "; " from the end of the last email address
EmailAddress = Left(EmailAddress, (Len(EmailAddress) - 2))
'MsgBox recount & " " & EmailAddress
'close the recordset
rs.Close
'set it to nothing
Set rs = Nothing
'Put something in the email Subject line (Text0 is from an email generating Form)
subject = "test" '[Text0]
'Put something in the email body (Text boxes are from an email generating Form)
Body = "Test to send email to multiple recipients" '[Text1] & Chr$(13) & [Text2] & Chr$(13) & [Text3] & Chr$(13) & [Text4]
'send the email
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
End Sub


----------



## OBP (Mar 8, 2005)

You could also use a Group or Department field in the Contacts Table if they can only belong to one group.


----------



## computerman29642 (Dec 4, 2007)

In the process of testing database. I will mark as solved once testing is completed.

Thank you so much, OBP.


----------

