How to send email using excel VBA

Question

I have data contained in column A to H. The data in column A are email addresses. I put a check box in another cell. What I want is when I tick the check box it proceeds to send email to the email account placed in last row column A. But the below code only works for cell A1.

Private Sub CheckBox1_Click()

    Dim Email As String

    Row = 1
    Email = Sheet1.Cells(Row, 1)
    Do Until Sheet1.Cells(Row, 1) = ""
        Row = Row + 1
    Loop

    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = Email
        .Subject = score
        .HTMLBody = "This is a contain of Email Message"
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing

End Sub

Answer 1

In your section below, where you are trying to get the last row and then the email from that cell:

Row = 1
Email = Sheet1.Cells(Row, 1)
Do Until Sheet1.Cells(Row, 1) = ""
    Row = Row + 1
Loop

you are taking the Email from the first row, and after in the Do Until loop you are checking for the last row, but not modifying the Email variable.

Resolve: There is no need to have a loop to find the last row with a valid email address in Column A, you can simply find it with the following lines below:

Dim LastRow As Long

' get last row with data in Column A (don't skip blank cells in the middle)
LastRow = Sheet1.Range("A1").End(xlDown).Row
Email = Sheet1.Range("A" & LastRow).Value

The rest of your code works just fine.