Test for email addresses
 

I have a large (~10,000) list of former clients in an excel file that was
created when I pulled a report from my third party software. I've contracted
with a newsletter company to help stay in touch with them. The newsletter
company charges by entry so I need to delete any rows that aren't email
addresses. I've already found the marco that deletes duplicate entries, and I
can easily sort to get rid of entries with no email address, now I need some
way to delete entries that have something other than an email address in the
"email address" column.

The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't been
able to get anything to work yet. Any help is appreciated.

Regards,

Test for email addresses
 

Here is a function that tests it


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


just add =ValidEmail(A1) in B1 say.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
I have a large (~10,000) list of former clients in an excel file that was
created when I pulled a report from my third party software. I've
contracted
with a newsletter company to help stay in touch with them. The newsletter
company charges by entry so I need to delete any rows that aren't email
addresses. I've already found the marco that deletes duplicate entries,
and I
can easily sort to get rid of entries with no email address, now I need
some
way to delete entries that have something other than an email address in
the
"email address" column.

The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't been
able to get anything to work yet. Any help is appreciated.

Regards,

Test for email addresses
 

Ok, I'm lost. I copied your code, right-clicked my tab name and then pasted it.

What do you mean, "just add =ValidEmail(A1) in B1 say."
Where do I add it?

Thanks,


"Bob Phillips" wrote:

Here is a function that tests it


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


just add =ValidEmail(A1) in B1 say.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
I have a large (~10,000) list of former clients in an excel file that was
created when I pulled a report from my third party software. I've
contracted
with a newsletter company to help stay in touch with them. The newsletter
company charges by entry so I need to delete any rows that aren't email
addresses. I've already found the marco that deletes duplicate entries,
and I
can easily sort to get rid of entries with no email address, now I need
some
way to delete entries that have something other than an email address in
the
"email address" column.

The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't been
able to get anything to work yet. Any help is appreciated.

Regards,

Test for email addresses
 

You shouldn't store it in the sheet code module, but in a standard code
module, it is a UDF.

You use it in the worksheet by, assuming that the address are in column A,
in B1 put =ValidEmail(A1) and copy down, then filter column B by False
and delete the visible rows.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
Ok, I'm lost. I copied your code, right-clicked my tab name and then
pasted it.

What do you mean, "just add =ValidEmail(A1) in B1 say."
Where do I add it?

Thanks,


"Bob Phillips" wrote:

Here is a function that tests it


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern =
"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


just add =ValidEmail(A1) in B1 say.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
I have a large (~10,000) list of former clients in an excel file that
was
created when I pulled a report from my third party software. I've
contracted
with a newsletter company to help stay in touch with them. The
newsletter
company charges by entry so I need to delete any rows that aren't
email
addresses. I've already found the marco that deletes duplicate
entries,
and I
can easily sort to get rid of entries with no email address, now I
need
some
way to delete entries that have something other than an email address
in
the
"email address" column.

The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't
been
able to get anything to work yet. Any help is appreciated.

Regards,

Test for email addresses
 

That did it. Thanks

"Bob Phillips" wrote:

You shouldn't store it in the sheet code module, but in a standard code
module, it is a UDF.

You use it in the worksheet by, assuming that the address are in column A,
in B1 put =ValidEmail(A1) and copy down, then filter column B by False
and delete the visible rows.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
Ok, I'm lost. I copied your code, right-clicked my tab name and then
pasted it.

What do you mean, "just add =ValidEmail(A1) in B1 say."
Where do I add it?

Thanks,


"Bob Phillips" wrote:

Here is a function that tests it


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern =
"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


just add =ValidEmail(A1) in B1 say.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sb1920alk" wrote in message
...
I have a large (~10,000) list of former clients in an excel file that
was
created when I pulled a report from my third party software. I've
contracted
with a newsletter company to help stay in touch with them. The
newsletter
company charges by entry so I need to delete any rows that aren't
email
addresses. I've already found the marco that deletes duplicate
entries,
and I
can easily sort to get rid of entries with no email address, now I
need
some
way to delete entries that have something other than an email address
in
the
"email address" column.

The answer could be several things: a helper column that tests for
wildcard@wildcard, or a macro that tests and deletes rows. I haven't
been
able to get anything to work yet. Any help is appreciated.

Regards,