Finding last row with data to send Mass Email

[MikeSta4ord]

Hi, I'm new to VBA coding and I'm having trouble with the last aspect of my code to send mass emails to everyone in an excel database. The Code operates correctly as its sending emails when I specify which row to loop until. Though, When I try to change the loop until statement to loop until it finds an empty cell it keeps returning an error that reads: "Outlook does not recognize one or more names". Not sure why Outlook is recognizing any names since everything is specified through excel.

Here's the code I'm using:

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


    Dim olMail As Outlook.MailItem
    Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = what_address
    olMail.Subject = subject_line
    olMail.Body = mail_body
    olMail.Send


End Sub


Sub SendMassEmail()


row_number = 1




Do
DoEvents
    row_number = row_number + 1
    Dim mail_body_message As String
    Dim full_name As String
    Dim project_name As String
    Dim offer_number As String




   
    mail_body_message = Sheet10.Range("G2")
    full_name = Sheet10.Range("D" & row_number)
    project_name = Sheet10.Range("B" & row_number)
    offer_number = Sheet10.Range("C" & row_number)
    mail_body_message = Replace(mail_body_message, "Offer_number", offer_number)
    mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
    mail_body_message = Replace(mail_body_message, "Project_replace", project_name)
      MsgBox mail_body_message
    Call SendEmail(Sheet10.Range("A" & row_number), "Following up on our offer", mail_body_message)


    
  Loop Until offer_number = ""
  
    MsgBox "Emails Sent!"


'
End Sub

Any errors you can identify would be greatly appreciated as I have no Idea where to take it from here.

Thank-you

[SamT]

That code sends mail one more mail even after the empty cell is encountered.

Sub SendMassEmail()
     
    row_number = 2
     
    Do While Sheet10.Range("C" & row_number) <> ""
        DoEvents
       Dim mail_body_message As String
        Dim full_name As String
        Dim project_name As String
        Dim offer_number As String
         
        mail_body_message = Sheet10.Range("G2")
        full_name = Sheet10.Range("D" & row_number)
        project_name = Sheet10.Range("B" & row_number)
        offer_number = Sheet10.Range("C" & row_number)
        mail_body_message = Replace(mail_body_message, "Offer_number", offer_number)
        mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
        mail_body_message = Replace(mail_body_message, "Project_replace", project_name)
        MsgBox mail_body_message
        Call SendEmail(Sheet10.Range("A" & row_number), "Following up on our offer", mail_body_message)
         
         row_number = row_number + 1
    Loop
     
    MsgBox "Emails Sent!"
End Sub

[MikeSta4ord]

Thank-you Sam T. That's the code I was looking for, though its still giving me a runtime error of "Outlook doesn't recognize one or more names" when I add the first portion of my Code since it wont let me execute just the second portion. It highlights "Olmail.Send" when I hit debug after the error appears...any ideas?

[SamT]

After you hit "Debug" hover your mouse over "what_address" to see its value and confirm that it is a valid Email address.

[MikeSta4ord]

Perfect, Thank-you very much for the insight. Would never have realized that error.