Optimizing my rows to send e-mail to different address

[MasterBash]

Hello,

I am trying to optimize my code that I use to send emails by clicking text in column C.

I want rows 2 to 25 (client A to X) to send to "test" and 27 to 29 (client 1 to 3) to send to "test123". Column A, B and C are all in a table, so we can easily expand the list if we choose to do so.

This is my current sheet :

email test.xlsm
	A	B	C	D	E	F	G
1	Client	Country	Email			Email	test
2	A	Country of client A	Send e-mail to Country of client A			Subject	REQUEST…
3	B	Country of client B	Send e-mail to Country of client B			Body	Please provide…
4	C	Country of client C	Send e-mail to Country of client C
5	D	Country of client D	Send e-mail to Country of client D				client A to X send email to test
6	E	Country of client E	Send e-mail to Country of client E
7	F	Country of client F	Send e-mail to Country of client F
8	G	Country of client G	Send e-mail to Country of client G
9	H	Country of client H	Send e-mail to Country of client H
10	I	Country of client I	Send e-mail to Country of client I
11	J	Country of client J	Send e-mail to Country of client J
12	K	Country of client K	Send e-mail to Country of client K
13	L	Country of client L	Send e-mail to Country of client L
14	M	Country of client M	Send e-mail to Country of client M
15	N	Country of client N	Send e-mail to Country of client N
16	O	Country of client O	Send e-mail to Country of client O
17	P	Country of client P	Send e-mail to Country of client P
18	Q	Country of client Q	Send e-mail to Country of client Q
19	R	Country of client R	Send e-mail to Country of client R
20	S	Country of client S	Send e-mail to Country of client S
21	T	Country of client T	Send e-mail to Country of client T
22	U	Country of client U	Send e-mail to Country of client U
23	V	Country of client V	Send e-mail to Country of client V
24	W	Country of client W	Send e-mail to Country of client W
25	X	Country of client X	Send e-mail to Country of client X
26
27	1	Country of client 1	Send e-mail to Country of client 1			Email	test123
28	2	Country of client 2	Send e-mail to Country of client 2			Subject	REQUEST…
29	3	Country of client 3	Send e-mail to Country of client 3			Body	Please provide…
30
31							client 1 to 3 send email to test123
Email client

Cell Formulas
Range	Formula
B2:B25,B27:B29	B2	="Country of client "&$A2
C2:C25,C27:C29	C2	="Send e-mail to "&B2

This is my VBA code :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim location As String
    Dim subject As String
    Dim body As String
    Dim email As String
    Dim Cell As Range
    Dim TargetRow

If Intersect(Target, UsedRange) Is Nothing Then Exit Sub
  
   For Each TargetRow In Intersect(Target, UsedRange).Rows

      If TargetRow.Columns.Count = 1 And TargetRow.Column = 3 Then ' column C is the only column selected in the row
         Set Cell = TargetRow
         location = Range("B" & Cell.Row).Value
     
         If Cell.Row <= 25 And Cell.Column = 3 And Cell <> "" Then

           email = Range("G1")
           subject = Range("G2") & location
           body = Range("G3") & location
          
        ElseIf Cell.Row >= 26 And Cell.Column = 3 And Cell <> "" Then
       
           email = Range("G27")
           subject = Range("G28") & " " & location
           body = Range("G29") & location
          
    If subject <> "" Then
        Send_The_Emails subject, body, email
    End If
    End If
    End If
    Next TargetRow
End Sub

I currently have 2 problems with this code. First, it does not work for client A to X (2 to 25) and second it is not modular enough. If possible, I would like to have ranges in cells. 2 to 25 will send to test and 27 to 29 to test123, and we will be able to modify those if we add more clients. In other words, instead of using Cell.Row <= or >=, I would prefer to have something that my team can easily modify if they were to add new rows.

How can I do that ?

Thank you.

 

[Sequoyah]

Hi MasterBash
here is a code example, assuming that there is always an empty row (only one) in the table between the two groups. This way you can use a different instruction depending on whether the click is made in the first group, before the empty row, or in the second group, after the empty row. To adapt the name of the table in the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tbl As ListObject
    Dim emptyRow As Range
    Dim emptyRowIndex As Long

    ' Disabilita gli eventi per evitare loop infiniti
    Application.EnableEvents = False
    
    ' Set your table
    Set tbl = Me.ListObjects("Table1") '<<==== ADAPT table name as needed
    
    ' Find first empty row
    On Error Resume Next
    Set emptyRow = tbl.ListColumns(3).DataBodyRange.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    ' If an empty row is found
    If Not emptyRow Is Nothing Then
        emptyRowIndex = emptyRow.Row ' Ottieni l'indice di riga della riga vuota
        
        ' column C is the only column selected in the row
        If Not Intersect(Target, tbl.ListColumns(3).DataBodyRange) Is Nothing Then
            ' Single cell selected
            If Target.Cells.Count = 1 Then
                ' If selected row is above the blank row
                If Target.Row < emptyRowIndex Then
                 ' <<< ==== first case   
                ' If selected row is under the blank row
                ElseIf Target.Row > emptyRowIndex Then
                   ' <<< ==== second case
                Else
                    'empty row DO nothing
                End If
            End If
        End If
    End If

    ' Riabilita gli eventi
    Application.EnableEvents = True
End Sub

However I think that this is not the best organization of the data, it would be better to insert a unique value in a column that identifies the group

 

[MasterBash]

Yes, thank you ! I will be using a column that identifies the group. That seems to be the best option.