Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Email authentication (XL97/WinNT4)

    Does anyone know of a way to check a list of email addresses in an XL spreadsheet to ensure that each one is valid as an email address? I'm not asking to check if they're *real* addresses, just that they are valid, ie they have an @ sign, a domain suffix, no illegal characters such as &, etc.

    We have a bulk email sender in XL whereby you paste a list of email addresses in and it sends the same email to each address - but if one of the addresses is not valid, Outlook complains, the whole thing falls over and the only way to get out of it is to do a hard reboot.

    Checking them manually is not really feasible (we're talking 100s, soon to be 1000s, at a time) and unfortunately the original entries were manually typed into the database from which the list is extracted, by people who don't seem to have been very up on email, judging by the number of times the spreadsheet crashed yesterday ...

    Any help would be gratefully appreciated!
    Beryl M


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email authentication (XL97/WinNT4)

    #####EDITED: ADDED CHECK FOR INVALID CHARACTERS#######

    This function determines whether an address is valid, following these rules (these are not the exact rules, but I guess they should work in most cases):

    1. The address must contain an @
    2. Before the @, at least one character must be present: "a@"
    3. After the @, at least two characters must be there: "a@ab"
    4. After that a dot is needed, followed by at least two characters: a@ab.ab
    5. The address should not contain a space.

    Paste this into a normal module of excel:

    <pre>Option Explicit
    Const sInvalid As String = "<>?/[]{}|~`'!#$%^&=+"
    Function IsAddressValid(sAddress As String)
    Dim iCount As Integer
    IsAddressValid = sAddress Like "*?[@]*??[.]??*"
    For iCount = 1 To Len(sInvalid)
    IsAddressValid = IsAddressValid And InStr(sAddress, Mid(sInvalid, iCount, 1)) = 0
    Next
    End Function
    </pre>


    Note you should check the list of invalid charcters yourself!!!

    Now to check whether an address in cell A1 is valid, simply type this into a cell:

    =IsAddressValid(A1)

    Or in your code use:

    Msgbox IsValidAddress("a@bc.cd")

    or:

    sAddress="a@bc.cd"
    Msgbox IsValidAddress(sAddress)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Email authentication (XL97/WinNT4)

    Jan, you're a marvel! Your original code worked perfectly and is now copied into our bulk email sender to be used next time.

    Many, many thanks

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •