Thread: Creating an Array and Sending email to Different Groups

Hello all!

I am new to VBA and am in need of assistance.

I would like to know how to create an array that I can use to determine what is selected in a combo box and then based on that selection have the form sent to a group of Recip (recipient email addresses).

The workbook contains a Combo Box (TypeComboBox1). Based on what a user selects from the TypeComboBox1, I need to send an email to 1 of 7 different groups of emails (Recip).

For example:

TypeComboBox1 contains the following drop down options:

Fruits (email to: sally@gmail.com, bob@gmail.com, rich@gmail.com)
Vegetables (email to: helen@gmail.com, jesse@gmail.com)
Deli (email to: karen@gmail.com, seth@gmail.com)
Meats (email to: lacey@gmail.com, ted@gmail.com, tony@gmail.com, renee@gmail.com, josh@gmail.com)
Frozen (email to: alli@gmail.com, steve@gmail.com, jackie@gmail.com)
Bakery (email to: gail@gmail.com, ron@gmail.com, bart@gmail.com, james@gmail.com, olli@gmail.com)
Customer Service (email to: steph@gmail.com, kim@gmail.com, lee@gmail.com, randy@gmail.com, matt@gmail.com)

To send the email I have:

 ActiveWorkbook.SendMail Recipients:=Recip, Subject:="Grocery Request: " & TypeComboBox1 & " - " & Format(Range("G4"), "dd-mmm-yy hhmm")

How do I create an array that can check for which is selected and have the form sent to the correct email group? Would I use a Select Case here??


Any help would be greatly appreciated!

THANK YOU!!!

Vb@n3wb|3

hi vb,
just an idea, see if it helps you! test file attached.

Private Sub ComboBox1_Change()
Select Case Me.ComboBox1.ListIndex
Case 0
   Me.TextBox1.Value = Sheets("Sheet1").[b2] 'fruits
 Case 1
    Me.TextBox1.Value = Sheets("Sheet1").[c2] 'vegetables
Case 2
    Me.TextBox1.Value = Sheets("Sheet1").[d2] 'deli
Case 3
    Me.TextBox1.Value = Sheets("Sheet1").[e2] 'meats
Case 4
    Me.TextBox1.Value = Sheets("Sheet1").[f2] 'frozen
Case 5
    Me.TextBox1.Value = Sheets("Sheet1").[g2] 'bakery
Case 6
    Me.TextBox1.Value = Sheets("Sheet1").[h2] 'customer s
End Select
End Sub

Private Sub CommandButton1_Click()
With CreateObject("Outlook.application").createitem(0)
      .to = Me.TextBox1.Value
      .Subject = "Grocery Request: " & ComboBox1.Value & " - " & Format(Sheets(1).[G4] & " " & Now) ''Format(Date, "dd/mmm/yy-hhmm"))
      .body = "This is your message"
      '.attachments.Add ActiveWorkbook.FullName
      .display  'or use .Send
    End With
    Unload Me
End Sub

Private Sub UserForm_Initialize()
Worksheets("Sheet1").Range("a2:a8").Name = "group"
Me.ComboBox1.RowSource = "group"
End Sub

hi VB,
Thank you for rep!
If you feel that your problem has been resolved, please mark
your thread as solved.