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.