Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Notify if an email is not sent using VBA

0

Hi

I have created a VBA to send emails from Excel with Outlook. 

My question: is there a way to create a message in cell to state if that email is sent?

I am sending 120+ emails 

Thanks 


Mark

Answer
Discuss

Answers

0
Selected Answer

Mark

Take a look at this Microsoft document: Err object. If there's an error in sending one of your 120+ emails, you can use Resume Next and modify the code given in that article to keep looping through/ sending your other emails:

Dim Msg
' If an error occurs, construct an error message
On Error Resume Next    ' Defer error handling.
Err.Clear
Err.Raise 6    ' Generate an "Overflow" error.
' Check for error, then show message.
If Err.Number <> 0 Then
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Chr(13) & Err.Description
    MsgBox Msg, vbMsgBoxHelpButton, "Error", Err.Helpfile, Err.HelpContext
End If

but instead of the MsgBox line, write Msg to your cell.

REVISION

Please check sheet 184-Anneli Piir; looks like it fails your third-level IF test:

f Not IsError(traderName) And Not IsError(traderEmail) Then

because cell B1 says 184 rather than '184.

Hope this helps.

Discuss

Discussion

Hi John

Thank you for your fast answer. There is no error message as such. I click the button I created and it loops through to the end but then on this occasion it has sent 123 instead of 124 emails, the email that was not sent was not the last email in the list. 

Noot sure if I have explained it properly. :)

I was wondering if I can create something to appear in the cell next to each email address in thhe list to state 'sent' or 'not sent'

Thanks again
Mark Battistini (rep: 4) Jul 1, '22 at 9:57 am
Mark.

I assume the Outbox is empty, right?

It's hard to guess without seeing your code / see you loop c.f. your data. Please edit your question to add an Excel file and I'll try to look in a few hours time. 
John_Ru (rep: 3677) Jul 1, '22 at 10:08 am
I have added it I think 

Thanks
Mark Battistini (rep: 4) Jul 1, '22 at 12:22 pm
Thanks Mark. Will look at your file later 
John_Ru (rep: 3677) Jul 1, '22 at 12:50 pm
Mark. please see my revised answer (and, if it fixes your problem, please select it)
John_Ru (rep: 3677) Jul 1, '22 at 2:10 pm
Thanks again John

Have a great weekend
Mark Battistini (rep: 4) Jul 2, '22 at 5:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login