Sending email to respective Individual as per Filter Selection

[Prakash M]

Hello,

I've managed to do a VBA which copies the fields as per filter selection.

Say if I'm selecting plant name as Searcy from filter, all the records which is only for Searcy - it gets copied to a new worksheet and is ready to be sent to a recipient as an attachment.

Now what I want is - When I filter the selection as Searcy - the email should get delivered to 123, 456, 789 (column H will be the list of email addresses), similarly when I filter for Van Wert - the email should get delivered only to ABC, DEF, GHI.

Many Thanks,
Prakash

 

[chirayu]

Assuming column G will have email addresses written down
Assuming you will somehow define the choice you made for Plant
- by either using a cell with plant dropdown (cell will act as input for macro)
- or using an inputbox/ userform list

Sub MailList()

'This can be used as TO for the email that is generated
Dim MyTo As String

'You will need to define this somehow
'- use a cell with dropdown & refer to that cell range or
'- use an inputbox/ userform list
'For now I have hardcoded as Searcy
Dim MyPlant As String
MyPlant = "Searcy"

'Below code doesn't need any filters to be applied. works on whole column
Range("G2").Select
Do Until IsEmpty(Range("B" & ActiveCell.Row))
  If Not IsEmpty(ActiveCell) And Range("B" & ActiveCell.Row).Value = MyPlant Then
    If Len(MyTo) = 0 Then
      MyTo = ActiveCell.Value
      ActiveCell.Offset(1, 0).Select
    Else
      MyTo = MyTo & "; " & ActiveCell.Value
      ActiveCell.Offset(1, 0).Select
    End If
  Else
    ActiveCell.Offset(1, 0).Select
  End If
Loop

Range("A1").Select
'You can remove the below messagebox. I just put it in to show you how it works
MsgBox MyTo, vbOKOnly, ""

End Sub