Dynamic email recipient list based on cell colour VBA

[Lisa05]

Hello All,
I have a macro created to send an email straight from the Excel file including Excel table in an email body. I have a question on the recipient list. Right now I fill in recepient list manually. But the recipients to whom an email should be sent are included in the Excel table pasted into mail body. Those cells in the table which include people to whom an email should be send are coloured red. Is it possible to create a condition to search for the red coloured cells within the table and add the value to the recepient list?
Thank you in advance for help.

 

[gordsky]

you can loop through the table checking the cells for colour and then add the contents to a recipient list. If you could attach a sample table and confirm if they are always in the same place

 

[Lisa05]

you can loop through the table checking the cells for colour and then add the contents to a recipient list. If you could attach a sample table and confirm if they are always in the same place

Thank you for reply gordsky.

Attached is the example of the table. The email shall be send only to recpients in red. Also, there will be additional columns each month.

 

[gordsky]

Thank you for reply gordsky.

Attached is the example of the table. The email shall be send only to recpients in red. Also, there will be additional columns each month.

will the people to be emailed in red always be in the LAST COLUMN

 

[Lisa05]

will the people to be emailed in red always be in the LAST COLUMN

No, it can be either last or any prior columns

 

[gordsky]

No, it can be either last or any prior columns

try this.
You will need to change "TableName" in the code to be the actual name of your table.
Then in your email code change the "TO" line to = recip

It works on the standard red. If you have used a lightly different colour it wont pick them up. You could however just get the RGB colour for the colour you want to use and change the vbRed reference in the code to be RGB(num,num,num) where num are the three RGB code numbers

Sub MailList()

Dim tbl As ListObject
Dim col As Long
Dim c As range
Dim recip As String

Set tbl = ActiveSheet.ListObjects("TableName")
col = tbl.range.Columns.count

'  change "TableName" to be the name of your table
   For Each c In ActiveSheet.ListObjects("TableName").range

      If c.Interior.Color = vbRed Then
         recip = recip & ";" & c.Value
        
      End If
    
   Next c

End Sub

 

[Lisa05]

try this.
You will need to change "TableName" in the code to be the actual name of your table.
Then in your email code change the "TO" line to = recip

It works on the standard red. If you have used a lightly different colour it wont pick them up. You could however just get the RGB colour for the colour you want to use and change the vbRed reference in the code to be RGB(num,num,num) where num are the three RGB code numbers

Sub MailList()

Dim tbl As ListObject
Dim col As Long
Dim c As range
Dim recip As String

Set tbl = ActiveSheet.ListObjects("TableName")
col = tbl.range.Columns.count

'  change "TableName" to be the name of your table
   For Each c In ActiveSheet.ListObjects("TableName").range

      If c.Interior.Color = vbRed Then
         recip = recip & ";" & c.Value
       
      End If
   
   Next c

End Sub

Thank you so much, itworked!

 

[gordsky]

Thank you so much, itworked!

No problem, glad I could help