Hi,
I have some code that sends emails based on a range of cells however I want to make the reference indirect as sometimes there will be 5 rows to send and others there will be 25 rows. The below code works fine I just need help on the row that says " For Each cell In ws.Range("A2:A2")", how do I make this dynamic to only pick up rows with data in them from row 2 down? So if Column A is populated then it gets picked up?
Sub SendMail()
Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet
Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet
For Each cell In ws.Range("A2:A2")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = cell.Value
.Subject = cell.Offset(0, 2).Value
.Body = cell.Offset(0, 3).Value
.Attachments.Add cell.Offset(0, 4).Value
.CC = cell.Offset(0, 1).Value
.Send
End With
Set objMail = Nothing
Next cell
Set ws = Nothing
Set objOutlook = Nothing
End Sub
thanks
I have some code that sends emails based on a range of cells however I want to make the reference indirect as sometimes there will be 5 rows to send and others there will be 25 rows. The below code works fine I just need help on the row that says " For Each cell In ws.Range("A2:A2")", how do I make this dynamic to only pick up rows with data in them from row 2 down? So if Column A is populated then it gets picked up?
Sub SendMail()
Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet
Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet
For Each cell In ws.Range("A2:A2")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = cell.Value
.Subject = cell.Offset(0, 2).Value
.Body = cell.Offset(0, 3).Value
.Attachments.Add cell.Offset(0, 4).Value
.CC = cell.Offset(0, 1).Value
.Send
End With
Set objMail = Nothing
Next cell
Set ws = Nothing
Set objOutlook = Nothing
End Sub
thanks