Emailing Groups with Combo Box in VBA

[Imagemo]

I am very new to using VBA, so hopefully someone can help me. I am trying to email certain groups by using a Combo Box. If I select a group in the list and then send email, it opens Outlook fine, but with no addresses in the To: field. This is with using If...Else statements or Select Case statements. But if I get rid of the conditional statements, it works fine and pulls the right addresses. Can someone tell me how I can get it to open Outlook with the emails populated in the To: Field?


Here is what I have in my code:

Public Sub btnSend_Click()


Dim OlApp As Object
Dim OlMail As Object
Dim rs As DAO.Recordset
Dim ToRecipient As String
  
  Set OlApp = CreateObject("Outlook.Application")
  Set OlMail = OlApp.CreateItem(olMailItem)


If Me.GroupLst = "Group A" Then
  Set rs = CurrentDb.OpenRecordset("SELECT Email FROM qryGrpA")
    Do While rs.EOF = False
    ToRecipient = rs!Email
    OlMail.Recipients.Add ToRecipient
    rs.MoveNext
  Loop
rs.Close
  Set rs = Nothing
Else
    If Me.GroupLst = "Group B" Then
      Set rs = CurrentDb.OpenRecordset("SELECT Email FROM qryGrpB")
    Do While rs.EOF = False
    ToRecipient = rs!Email
    OlMail.Recipients.Add ToRecipient
    rs.MoveNext
  Loop
rs.Close
  Set rs = Nothing
    End If
End If


  OlMail.Subject = " "
  OlMail.Display
End Sub

[rpeare]

have you done any debugging to see if it's correctly pulling email addresses?

for instance

debug.print torecipient

after each time you hit a record?

You may also want to try concantenating all of your email address into a single add statement

Do While rs.EOF = False
    ToRecipient = ToRecipient & rs!Email & ";"
rs.MoveNext
torecipient = left(torecipient, len(torecipient) -1)
OlMail.Recipients.Add ToRecipient

[Imagemo]

Thank you, rpeare...I will give this a try.

[Imagemo]

I've tried it and unfortunately it's not even going "inside the If statements"...my values are coming up "null".

[rpeare]

try inserting a debugging statement to print out the email addresses as you go through them to see if it's actually finding what you think it should.


debug.print rs!email

[June7]

Instead of multiple query objects, apply filter in SQL statement. The .Add should handle concatenation of addresses.

Public Sub btnSend_Click()

Dim OlApp As Object
Dim OlMail As Object
Dim rs As DAO.Recordset
  
Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(olMailItem)
Set rs = CurrentDb.OpenRecordset("SELECT Email FROM table WHERE Group = '" & Me.GroupLst & "'")
Do While rs.EOF = False
    OlMail.Recipients.Add rs!Email
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
OlMail.Subject = " "
OlMail.Display

End Sub

Consider SendObject instead of Outlook automation. Use other method of concatenating addresses from recordset.

Either way, make sure combo or list box value is pulled by code.

[Imagemo]

I appreciate both of your replies.

rpeare - after trying to insert Debug.Print rs!Email, it gives me a "Runtime Error 424 - Object Required" but I do have it declared up top.

The other curious thing it does is that if I put in a <> instead of =, it brings up the email addresses associated with the group in the combo box.

[Imagemo]

I got it figured out. The simple solution was because I didn't put Me.GroupLst.Column(1)...wow! Thank you for all your help.