Adding Multiple Recipients to an Email Sub

I have the follwoing code that sneds me an email about an appointment that has been scheduled for an employee (say a review or something)

Private Sub EmailLineManager()        
Dim objMail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.Application")
With objMail.CreateItem(olMailItem)
    .Recipients.Add "craig@emailaddress"
    .Subject = "Appoinment added for " & Forms.frmEmployeeDetails.tboRvwFullName
    .Body = "An appointment has been scheduled for " & Forms.frmEmployeeDetails.tboRvwFullName _
            & " for " & Me!ApptDate & " at " & Me!ApptTime & ", lasting about " & Me!ApptLength _
            & vbCrLf & vbCrLf _
            & "The reason for this appointment is " & Me!Appt & "."
    .Send
Set objMail = Nothing
End If

I have in my form a Combo Box that lists the employees line manager (not me). When I call this routine, I want to send an email not only to this employees line manager, but to all line managers in the same Department. I have in my line managers table a field called DeptCode, so lets say PersonA and PersonB are in department HD, but Person C in in department CS.

Assuming that this employee reports to PersonA, in the .Recipients.Add line I would like to add not only the employees line manager (PersonA n department HD), but also PersonB, but not PersonC.

Any ideas?
End Sub
Craig_MucklestonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

perkcCommented:
Try this:

Private Sub EmailLineManager()
Dim objMail As Outlook.MailItem
Dim rs As DAO.Recordset
Dim rssql As String
rssql = "ADD THE SQL STRING HERE THAT PULLS THE MANAGERS;"
Set rs = CurrentDb.OpenRecordset(rssql)
rs.MoveLast
rs.MoveFirst
Set objOutlook = CreateObject("Outlook.Application")
With objMail.CreateItem(olMailItem)
    .Recipients.Add "craig@emailaddress"
   
    Do While Not rs.EOF
         .Recipients.Add (rs.Fields("COLUMN NAME FOR THE MANAGER'S EMAIL"))
         rs.MoveNext
    Loop
    .Subject = "Appoinment added for " & Forms.frmEmployeeDetails.tboRvwFullName
    .Body = "An appointment has been scheduled for " & Forms.frmEmployeeDetails.tboRvwFullName _
            & " for " & Me!ApptDate & " at " & Me!ApptTime & ", lasting about " & Me!ApptLength _
            & vbCrLf & vbCrLf _
            & "The reason for this appointment is " & Me!Appt & "."
    .Send
End With
Set objMail = Nothing
rs.Close
Set rs = Nothing
End Sub

0
Craig_MucklestonAuthor Commented:
Hi perkc,

I have changed the code as per yours above, and edoited to pull my deprtment off, but I am getting a run-time error for Too Few Parameters Supplied. Expected 2.

My code as it stands is below

Private Sub EmailLineManager()
Dim objMail As Outlook.MailItem
Dim rs As DAO.Recordset
Dim rssql As String
Dim recDept As String
recDept = (Forms.frmEmployeeDetails.[cboDept].Column(1))

'rssql = "ADD THE SQL STRING HERE THAT PULLS THE MANAGERS;"
rssql = "SELECT tblLineManagers.LineManager "
rssql = rssql + "FROM tblLineManagers "
rssql = rssql + "WHERE tblLineManagers.tblDept = " & recDept & ";"
MsgBox (rssql)

Set rs = CurrentDb.OpenRecordset(rssql)
rs.MoveLast
rs.MoveFirst
Set objOutlook = CreateObject("Outlook.Application")
With objMail.CreateItem(olMailItem)
   
    Do While Not rs.EOF
         '.Recipients.Add (rs.Fields("COLUMN NAME FOR THE MANAGER'S EMAIL"))
         .Recipients.Add (rs.Fields("tblLineManagers.LineManager"))
         rs.MoveNext
    Loop
    .Subject = "Appoinment added for " & Forms.frmEmployeeDetails.tboRvwFullName
    .Body = "An appointment has been scheduled for " & Forms.frmEmployeeDetails.tboRvwFullName _
            & " for " & Me!ApptDate & " at " & Me!ApptTime & ", lasting about " & Me!ApptLength _
            & vbCrLf & vbCrLf _
            & "The reason for this appointment is " & Me!Appt & "."
    .Send
End With
Set objMail = Nothing
rs.Close
Set rs = Nothing
End Sub

recDept gives me the Department Name, eg, Management, Customer Services, Training, etc.
0
perkcCommented:
Change the following line:

rssql = rssql + "WHERE tblLineManagers.tblDept = " & recDept & ";"

to:

rssql = rssql + "WHERE tblLineManagers.tblDept = '" & recDept & "';"

Because the recDept value is text it needs to be enclosed in '.

perkc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.