Trapping an invalid email error in Excel VBA code

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
__________________________

you may need to use the resolve method
on each recipient in the recipients collection

or ResolveAll on the recipients collection





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Richard Buttrey wrote :

> 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
> __________________________

On Thu, 16 Jun 2005 04:53:11 -0700, "keepITcool"
<[email protected]> wrote:

>
>
>you may need to use the resolve method
>on each recipient in the recipients collection
>
>or ResolveAll on the recipients collection

Hi,

Thanks for the quick response.

Could you explain a little further please. I'm unfamiliar with the
terminology ResoveAll or the recipients collection.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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" <[email protected]> wrote in
message news:[email protected]...
> 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
> __________________________

well.. you ARE working with outlook.

so check help

those are methods of the outlook library to
check validity of email adresses against the address book.

if the eamil addresses are not necessarily
included in the address book then it would be of no use.

and rereading your original question:
try something like..very rough hope you get the gist..

dim SendErrors as collection
set SendErrors = new collection
on error goto SendError
for each x in y
x.send
next
on error goto 0
blah blah

if SendErrors.count >0 then
dim sMsg$,itm
for each itm in SendErrors
sMsg = sMsg & vblf & itm
next
msgbox sMsg
end if
exit sub

SendError:
select case err.Number
case 123 'Adapt to suit
SendErrors.add x
err.clear
case else
debug.print err.number,err.description
stop
end select
err.clear
resume next

end sub







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Richard Buttrey wrote :

> On Thu, 16 Jun 2005 04:53:11 -0700, "keepITcool"
> <[email protected]> wrote:
>
> >
> >
> > you may need to use the resolve method
> > on each recipient in the recipients collection
> >
> > or ResolveAll on the recipients collection
>
> Hi,
>
> Thanks for the quick response.
>
> Could you explain a little further please. I'm unfamiliar with the
> terminology ResoveAll or the recipients collection.
>
> Rgds
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________