Automatically email based on cell value

[Garden Utopia Productions]

Hey all - I would like to create a macro that automatically creates an email for a group of people based on a cell value that's selected from a drop-down menu in a range of cells. There would only be 2 or 3 status options from the drop-down menu, but a specific group of people would be emailed depending on the task name.

Since there are a lot of tasks and groups, I'm thinking it might make sense to have Sheet1 with the list of these tasks in column A. Column B would be the drop-down menu that indicates the status of the task.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Task1[/TD]
[TD]Status1[/TD]
[/TR]
[TR]
[TD]Task2[/TD]
[TD]Status3[/TD]
[/TR]
[TR]
[TD]Task3[/TD]
[TD]Status2[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 would have each group's email addresses.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Task1[/TD]
[TD]email1; email2; email3[/TD]
[/TR]
[TR]
[TD]Task2[/TD]
[TD]email1; email2[/TD]
[/TR]
[TR]
[TD]Task3[/TD]
[TD]email1; email2; email3; email4[/TD]
[/TR]
</tbody>[/TABLE]


I found a bit of code that's supposed to run different macros based on the drop-down selection from a single cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then
        Select Case Range("B1")
            Case "Status1": Macro1
            Case "Status2": Macro2
            Case "Status3": Macro3
        End Select
    End If
    End Sub

Would I change the "B1" to "B1:B100" to check that cell range or does this need to be in a For loop instead?

I know I would then need to create separate email commands for Macro1,2, & 3, but I'm not exactly sure how Worksheet_Change() passes the cell number who's status changed or task name to Macro1() to indicate which group of emails to use from Sheet2.

I'm guessing Macro1's code would look something like this:

Private Sub Macro1()
    Dim guOutApp As Object
    Dim guMailItem As Object
    
    Dim vbCrLf As String
    Dim guMailBody As String
    Dim guMailSubject As String
        
    On Error Resume Next

    vbCrLf = "< br >"
    
    guMailSubject = ""
    guMailBody = "< HTML >< BODY >"
    guMailBody = guMailBody & vbCrLf & " "
    guMailBody = guMailBody & "< /BODY >< /HTML >"

    If guMailSubject <> "" Then
          Set guOutApp = CreateObject("Outlook.Application")
          Set guMailItem = guOutApp.CreateItem(0)
          With guMailItem
            .Subject = guMailSubject
            .To =  SomeVariable(Cell# From Sheet2)
            '.Cc =
            .HTMLBody = guMailBody
            .Display
          End With
    End If


End Sub

I should also mention that this workbook has another button activated macro in Module1. Not sure how this will affect the requested macro - or if I need to create another Module to run this macro in.

I hope this all makes sense. Thanks in advance for any advice!

~ GU Productions

 

[Garden Utopia Productions]

Update:


I was able to get a couple things to work. I got the email part to work and learned that the Change event needs to be in the worksheet module while the other Macros have their own Modules.

The code below works for a single cell range:

PrivateSubWorksheet_Change(ByValTarget AsRange)    
    If Not Intersect(Target, Range("E1")) Is Nothing Then
       Select Case Range("E1")
           Case "Under Review": Macro1
           Case "Done": Macro2
           Case "Redo": Macro3
       End Select
   End If
    EndSub

But I get a Run-time error '13': Type Mismatch if I try a larger range. When I click the Debug button, Case "Under Review" gets highlighted.

PrivateSubWorksheet_Change(ByValTarget AsRange)    
   If Not Intersect(Target, Range("E1:E100")) Is Nothing Then
       Select Case Range("E1:E100")
           Case"Under Review": Macro1
      ...

I'm not sure what the type mismatch is.

My next request is to be able to assign the cell's position as a variable to be used in Macro1,2, or 3. Can this be done?
For example, each cell the whole range of column E has the same drop-down menu items. You select "Done" from E25. I want to Set Variable_Name = E25 and pass that variable to Macro2.