Sending email to multiple address using a query in Access 2007

[bobhallata]

Hello all. This is my first post to the site and I have a problem that I've been beating my head against the wall on for the last couple weeks. I'm sure that it's child's play for most of you. I'm not brand new to VBA coding but I am by no means a master of the art. I have a database that tracks course delivery records. Within that database, instructors can be assigned to specific course deliveries. What I would like to do is have a button that when you click on it, creates an email with all of the instructors email addresses in the To line seperated by a semi-colon and the subject line to be a field called DelHis from the form (which will be open) called Courses. My query, which displays only the email addresses in a column, is called QryEmail. The query pulls the names from a form called Courses. I have pulled multiple examples from forums thorughout this site but cannot seem to make them work for my situation. Can anyone break it down "pebble level" style for me? Thanks much.

Bob

 

[SkipVought]

hi,

Do you have any code?

Where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bobhallata]

Skip,

I've gone through several different variations of coding while bludgeoning myself but here is the last one.

Dim strSql
Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Dim Lrs As DAO.Recordset
Dim Outlook

strSql = "SELECT E-Mail FROM QryEmail2"
Set Lrs = db.OpenRecordset(strSql)

Do While Not Lrs.EOF
Outlook = Outlook + Lrs("E-Mail") + ";"
Lrs.MoveNext
Loop


Dim rng
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Outlook
.CC = ""
.BCC = ""
.Display
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing



End Sub

I'm pretty much stuck with the whole concept of it. I'm self taught by failure so I'm sure that the coding that I've used over the last several weeks has had multiple issues.

Thanks for your help.

Bob

 

[SkipVought]

Where are the CourseDeliveries in your query?

I would expect to see something like this...

select E-Mail, Course 
FROM [SomeTable]
where course in ('math101','sci101','hist101')

where the course list would be generated from the courses selected in your form.

Then you would from this, create your recipient lists for each of the courses.

Is this anything like what you had in mind?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bobhallata]

Skip,

Each course is more unique in that it is it's own record. The instructor drop downs are keyed off of what they are vetted to teach. So when you have a record for delivery of the FRTI course, only instructors who are vetted for that specific course will be available in the drop down. Once an instructor is assigned to a team and the team is complete I wanted the button to gather all of their e-mail addresses from a query like the example entries below and then autofill them into the To line of the e-mail.

Example QryEmail results for a record with E-Mail being the header:

E-Mail
me@tired.com
Frank@tacguy.com
John@bluegun.com
Steve@redkey.com

NOTE* When I run the code that I pasted above I get

Run-time error '3061': Too few parameters. Expected 11.

It highlights the below line of code.

Set Lrs = db.OpenRecordset(strSql)

 

[bobhallata]

The other code that I was playing with for a little bit is below.

Dim r As Recordset
Dim email As String
Set r = CurrentDb.OpenRecordset("select * from QryEmail")
Do While Not r.EOF
    email = email & r(2) & ";"
    r.MoveNext
Loop
r.Close

DoCmd.SendObject acSendNoObject, Null, Null, email, Null, Null, "Test subject", "Message body of the test letter", False, Null