run-time error '-2147467259 (80004005)' when sending emails via vba

[drexcel7]

I'm basically writing a macro to send emails to people when their tasks are overdue. I'm getting multiple problems with this. I have wrote the following code:

Sub OverdueProject()

Dim row_number As Integer
Dim Overdue As String
Dim x As Long
Dim mydate1 As Date
Dim mydate2 As Long
Dim lastrow As Long

lastrow = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row

For x = 8 To lastrow

mydate1 = Cells(x, 9)
mydate2 = mydate1

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem


If mydate2 <= Date And Sheet1.Cells(x, 11) = "" And (Sheet1.Cells(x, 15) = "" Or (Sheet1.Cells(x, 15).Value + 7 <= Date)) Then

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = Cells(x, 13)
    olMail.Subject = "Overdue: " + " " + Cells(x, 1) + " " + "-" + " " + Cells(x, 2)
    olMail.Body = ("Dear " + Cells(x, 14)) & Chr(10) & "" & Chr(10) & ("Your task is: " + Cells(x, 12) + ".") & Chr(10) & ("Here is the link to the action spreadsheet: *spreadsheet link will be inserted here*") & Chr(10) & "" & Chr(10) & ("Kind Regards") & Chr(10) & "" & Chr(10) & ("drexcel7")
    olMail.Send

Sheet1.Cells(x, 15).Value = Date

End If
Next

End Sub

The email looks at the email in column 13 and sends an email to them based on the conditions wrote in the code. I have set it up so that it runs whenever the spreadsheet opens. The macro DOES do what it is intended, however, I get an error on the line "olMail.Send" saying "run-time error '2147467259 (80004005): we need to know who to send this to. Make sure you enter at least one name."

Is there any reason for this? Whilst it does do what I need to as is, I don't like the error that's popping up, and may cause others to panic should they use this spreadsheet.

[Arkadi]

Have you checked what the value of x is when the error ocurrs? It is possible that there is a blank cell in M on a row? (lastrow for example may be a lower row than the last email address in your M column)

[drexcel7]

It says the value of x is 23, but I only have 22 rows in the table currently. It also says that last row is 30...I've tried to "delete" these rows to see if anything is in there but nothing changed.

I decided to add rows up to row 31 to see if this changed anything. The same error occurred, except it says the value of x is 31 and last row is 39. Exact same thing happened when I added rows up to 107 (x = 108 and lastrow 115).

Funnily, when I delete the rows, it still says last row is 115. So maybe this is a formatting issue?

But you are correct. There is a blank cell in M on a row which is causing the error.

[Arkadi]

Without the sheet I can't troubleshoot the "last row" issue, but you could always implement a check and skip any row where M is blank:

If mydate2 <= Date And Sheet1.Cells(x, 11) = "" And (Sheet1.Cells(x, 15) = "" Or (Sheet1.Cells(x, 15).Value + 7 <= Date)) And Sheet1.Cells(x, 13) <> "" Then

If the email column can be relied on to determine what the last row is, then you could use:

lastrow = Sheet1.Range("M" & Rows.Count).End(xlUp).Row

It might still be good to leave the check for blank M rows though, so that if a row in the middle doesn't have an email address, you won't get an error.

[drexcel7]

Wow this works perfectly, thank you so much!

[Arkadi]

Glad to hear it
Thanks for the rep, the feedback, and marking the thread as solved!

[Yoanna]

Hello ! I am trying do deploy a similar vba code. The end goal is to load an excel file and extract some data in it including some emails. After that another macro is triggered which sends mails. Unfortunately, when there are no email addresses populated into the file, I get the same error message. Could please help me solve this issue and modify my macro. I use the following code:

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


   Dim olapp As Outlook.Application
   Dim olemail As Outlook.MailItem
   
   
   Set olapp = New Outlook.Application
   Set olemail = olapp.CreateItem(olMailItem)
   

   Dim last_row As Integer
   
   With olemail
     .BodyFormat = olFormatHTML
     .HTMLBody = mail_body
     
     .To = what_address
     .Subject = subject_line
     .BodyFormat = olFormatPlain
     
      .Send
     End With
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 Body_Difference As String
   
   mail_body_message = Sheet1.Range("N1")
   full_name = Sheet1.Range("D" & row_number)
   Body_Difference = Sheet1.Range("J" & row_number)
   mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
   mail_body_message = Replace(mail_body_message, "Body_Difference_replace", Body_Difference)
   
   
   Call Sendemail(Sheet1.Range("E" & row_number), "This is a test email", mail_body_message)
   
Loop Until row_number = Sheet1.Range("E99999").End(xlUp).Row
  Loop

End Sub

[Arkadi]

Hi Yoanna,

We’ll be happy to help but you’ll have to start your own thread please.

[Yoanna]

HI, Arkadi!
Thanks for your answer. I started a new thread