Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping an invalid email error in Excel VBA code
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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping an invalid email error in Excel VBA code
On Thu, 16 Jun 2005 04:53:11 -0700, "keepITcool"
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 __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping an invalid email error in Excel VBA code
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" wrote in message ... 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 __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping an invalid email error in Excel VBA code
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" 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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL ERROR: This workbook contains INVALID references. ? | Excel Discussion (Misc queries) | |||
How to Sort Invalid Email Addresses? | New Users to Excel | |||
Error trapping | Excel Programming | |||
Error Trapping Options Change in Excel 1997 | Excel Programming |