Sent mass email based on cell values

[floggingmolly]

I have a list of employee ID's in column A. In column B is either YES or NO based on a vlookup verifying if they entered data in a schedule sheet. I need to send an email to any employee ID with a NO in column B. I would like to send them all in one email as a bcc since there are 180 employees. Is this possible using vba? I was able to send an email but only to one at a time. Any help would be greatly appreciated.

 

[DanteAmor]

Let's start with this:

Sub Send_Mass_Email()
  Dim i As Long, dam As Object
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, "B").Value = "No" Then
      Set dam = CreateObject("outlook.application").createitem(0)
      dam.To = "email@yahoo.com"
      dam.Bcc = "mail2@gmail.com; mail3@gmail.com"
      dam.Subject = "subject.."
      dam.Body = "body ..."
      dam.Display 'use .Send to send
    End If
  Next
  MsgBox "Sent"
End Sub

 

[floggingmolly]

Thanks for the quick reply. Here's where I am stuck. Column A has the employee ID #. Column B has YES or NO. Column C has the employees email address. When column B is a NO I would like to generate a mass email to all the addresses that have a NO. Is there a way to automatically enter all NO emails into one email? In the code example above the emails are already in the code. I hope this makes sense.

 

[DanteAmor]

Thanks for the quick reply. Here's where I am stuck. Column A has the employee ID #. Column B has YES or NO. Column C has the employees email address. When column B is a NO I would like to generate a mass email to all the addresses that have a NO. Is there a way to automatically enter all NO emails into one email? In the code example above the emails are already in the code. I hope this makes sense.

Try this

Sub Send_Mass_Email()
  Dim i As Long, dam As Object, wMail As String
  For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, "B").Value = "No" Then wMail = wMail & Cells(i, "C").Value & "; "
  Next
  Set dam = CreateObject("outlook.application").createitem(0)
  dam.To = "email@yahoo.com"
[COLOR=#0000ff]  dam.Bcc = wMail[/COLOR]
  dam.Subject = "subject.."
  dam.Body = "body ..."
  dam.Display 'use .Send to send
  MsgBox "Sent"
End Sub