Looping through sheet to end emails from excel

[chcoder]

Hi I'm trying to set up emails to send out based on the list of names, email addresses and body text in the excel sheet. each line is for a new email recipient. I created and tested code that works perfectly for sending to just this one email but now I want code that would loop and would send more emails if i had another employee in line 3, 4, ..ect.

.

this is the code that worked for me:


Sub sending_the_email_worked()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)



Dim ToName As String

Dim EmpName As String

Dim strMsg As String


ToName = Range("A2").Value
EmpName = Range("B2").Value
strMsg = Range("C2").Value

With OutlookMail

.To = ToName
.CC = ""
.BCC = ""
.Subject = EmpName & ", Your timesheets needs attention!"

.Display

.HTMLBody = "<p style='font-family:calibri;font-size:16'>Hi " & EmpName & "," & "<br><br>" & strMsg & "<br><br>" & "Thank you,</p>" & .HTMLBody


.Send



End With
End Sub

 

[DanteAmor]

Try this:

Sub sending_the_email_worked()
  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem
  Dim i As Long

  For i = 2 To Range("A" & Rows.Count).End(3).Row
    Set OutlookApp = New Outlook.Application
    Set OutlookMail = OutlookApp.CreateItem(olMailItem)
    With OutlookMail
      .To = Range("A" & i).Value
      .Subject = Range("B" & i).Value & ", Your timesheets needs attention!"
      .Display
      .HTMLBody = "<p style='font-family:calibri;font-size:16'>Hi " & Range("B" & i).Value & "," & _
        "<br><br>" & Range("C" & i).Value & "<br><br>" & "Thank you,</p>" & .HTMLBody
      .Send
    End With
  Next
End Sub

 

[chcoder]

Thank you this worked!
I just want to understand this code - what does the .End(3).Row mean ?

For i = 2 To Range("A" & Rows.Count).End(3).Row

 

[DanteAmor]

For i = 2 To Range("A" & Rows.Count).End(3).Row

It goes from row 2 to the last row with data from column "A"

what does the .End(3).Row mean ?

From the last row of the excel sheet (rows.count) upwards (end (3))
take row number (.row)