Re: Trapping an invalid email error in Excel VBA code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

What you need is the "Resume Next" command at the end of your error handler.
Also, put the "On Error Goto " _inside_ the loop. Ideally you should only
trap the line that you think is going to cause a problem, so that other
errors aren't sent to your handler.

Try the following:

Sub ErrorHandledLoop(Arguments)

' Declare and populate variables

For Each x In y ' Alternatively: For i = a to b, Do [While condition |
Until condition]
' Code
On Error Goto ErrHandler
' Do some stuff on [x | i] that needs Error Trapping
On Error Goto 0
' More code
Next ' Or Loop [While condition | Until condition]

Exit Sub

ErrHandler:
' Handle error - You can call external subrouties and functions here if
desired
Err.Clear ' probably not needed, but good practise
Resume Next
End Sub

"Richard Buttrey" <chaos.theory.nospam.removethis@xxxxxxxxx> wrote in
message news:fao2b192eki93cihevcmgfb6nc2lcp29o1@xxxxxxxxxx
> Hi,
>
> I have a list of email names in a worksheet
> I also have a subroutine which loops through these names, creates an
> Outlook email with the "OLMail.Send" command, and sends the message.
>
> Where the email name is not recognised by Outlook, an error is
> returned and the macro halts. I'd now like to build some error
> trapping code into the macro so that the invalid email name is marked
> accordingly and the subroutine then carries on looping.
>
> I can see thatan On Error Goto command can send program control to an
> error handling routine at the end of the subroutine, but as this is
> outside the For..Next loop, once the error is handled the macro stops.
>
> Can anyone suggest an alternative approach? Is there such a thing as
> an OnError redirection command which could call another subroutine and
> then return back to the loop?
>
> TIA
>
> Rgds
>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________


.

Relevant Pages

  • Re: Macro unexpectedly jumps to other macros and functions
    ... > I've spent hours trying to figure out why the following subroutine ... What would automatically trigger this macro to jump into ... > subroutine in this workbook with the line Private Sub ...
    (microsoft.public.excel)
  • Re: Application.Calculation gives run time error
    ... In Designer mode (top left button on the controls toolbox menu) right click ... You should now be able to run your macro or move that calculation line into ... > Button_x_Click event to start my subroutine. ... > Private Sub Button_X_Click ...
    (microsoft.public.excel.programming)
  • Re: Is $A = $B = n; valid?
    ... sub number ... Why the overhead of a subroutine call? ... Just use a macro which gets removed at compile time: ...
    (comp.lang.perl.misc)
  • Re: Inadvertent macro
    ... sub Macro1 ... "Dean" wrote: ... By writing this as a subroutine, ... Can you rewrite your macro to do that? ...
    (microsoft.public.excel.programming)
  • RXParse module v.90 (by robic0)
    ... When I release version 1 of RXParse, I anticipate that I will provide a ftp site ... sub original_content ... then call content handler with $content ... # call start tag handler with $2 ...
    (comp.lang.perl.misc)