gusmspotify
New Member
- Joined
- Jul 17, 2018
- Messages
- 2
I am making a macro that will send out an email to different people depending on the value of a filter in cell C1.
I have managed to compile the macro to work depending on a cell value (cell C2) however as the cell value does not change when the filter is applied I need to select the different recipient depending on the filter value instead:
If Sheets("Current").Range("C2").Value = "Ramp" Then
nameList = "Ramp@ramptest.com"
ElseIf Sheets("Current").Range("C2").Value = "CustomerService" Then
nameList = "CustServ@CustSreveTest.com"
End If
With OutMail
.To = nameList
.CC = ""
.BCC = ""
I have tried to make it work depending on the filer value, but it does not.
If Sheets("Current").Range("$A$1:$H$13").AutoFilter(Field:=3, Criteria1:="Ramp") Then
nameList = "Ramp@ramptest.com"
ElseIf Sheets("Current").Range("$A$1:$H$13").AutoFilter(Field:=3, Criteria1:="CustomerService") Then
nameList = "CustServ@CustSreveTest.com"
End If
With OutMail
.To = nameList
.CC = ""
.BCC = ""
I have managed to compile the macro to work depending on a cell value (cell C2) however as the cell value does not change when the filter is applied I need to select the different recipient depending on the filter value instead:
If Sheets("Current").Range("C2").Value = "Ramp" Then
nameList = "Ramp@ramptest.com"
ElseIf Sheets("Current").Range("C2").Value = "CustomerService" Then
nameList = "CustServ@CustSreveTest.com"
End If
With OutMail
.To = nameList
.CC = ""
.BCC = ""
I have tried to make it work depending on the filer value, but it does not.
If Sheets("Current").Range("$A$1:$H$13").AutoFilter(Field:=3, Criteria1:="Ramp") Then
nameList = "Ramp@ramptest.com"
ElseIf Sheets("Current").Range("$A$1:$H$13").AutoFilter(Field:=3, Criteria1:="CustomerService") Then
nameList = "CustServ@CustSreveTest.com"
End If
With OutMail
.To = nameList
.CC = ""
.BCC = ""