vba condition email

[ashani]

Hi,

I'm using the below syntax in VBA to send multiple emails with attachments and it's working fine - however I would like the VBA to only send email to those recipients if in Column Y says Yes, if it says No then don't send email. Please could someone guide me. Thank you

Option Explicit

Sub send_multiple_emails()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("Outlook.application")

Dim i As Integer
Dim last_row As Integer

last_row = Application.WorksheetFunction.CountA(sh.Range("H:H"))

For i = 2 To last_row
Set msg = OA.createitem(0)

msg.to = sh.Range("H" & i).Value
msg.cc = sh.Range("I" & i).Value
msg.Subject = "Sales Report"
msg.body = sh.Range("M" & i).Value

If sh.Range("J" & i).Value <> "" Then
msg.attachments.Add sh.Range("J" & i).Value
End If

msg.display


Next i


End Sub

 

[Micron]

perhaps

For i = 2 To last_row
  If ActiveSheet.Range("Y" & i) = "Yes" Then
     Set msg = OA.createitem(0)
     ' more code
  End If
Next

When you don't include the beginning and ending lines (Sub... End Sub) assumptions have to be made. In this case, perhaps ActiveSheet is not correct.

 

[ashani]

Thank you
If the first row says No then it come up with the error message

 

[Micron]

You need to show what line raises an error. Your code does not error for me, so it might help if you post some data that you're using for this. You can edit it to protect privacy. Copy something from a sheet and paste it into a post?