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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[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