Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como VALIDAR Listado de EMAIL CORREO ELECTRONICO en Excel VBA #590

Validar listado de correos electronicos validacion mail

VALIDAR LISTADO DE DIRECCIONES DE EMAIL O CORREOS ELECTRONICOS

En este post se muestra como validar un listado existente de correos electrónicos o emails, la macro permite determinar si un mail ya escrito en un listado es correcto o no utilizando una macro programada con VBA en Excel.

Aprender a operar Excel en forma efeicaz, click acá, si quieres aprender sobre Excel en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet, te lo recomiendo no te arrepentirás.

  
 

Mira la macro en acción, una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestro canal de You Tube, mira el playlist con  vídeos relacionados donde podrás ver la macros relacionadas en acción con una explicación en forma visual que ayudará a entender el ejemplo en forma más fácil.

DETERMINAR SI LAS DIRECCIONES DE MAIL SON CORRECTAS

Para verificar como funciona el ejemplo, recomiendo descargar el archivo, se debe presionar el botón verde y se empezará a ejecutar una macro que valida si están bien escritas o no las direcciones de correo electrónico que se encuentran en la columna C.

Desde ya les digo que las direcciones de mail que figuran son todas inventadas y a los fines de mostrar como funciona el ejemplo, ello es los fines de que evitan tratar de hacer una base de datos de email con dicho listado.

La macro compara la cadena de caracteres que conforman la dirección del email contra un patrón establecido que permite validar mails, si coincide entonces pinta o colorea la celda con verde y en la columna D fila correspondiente escribe «OK».

Por el contrario si la cadena de caracteres que conforman la dirección de correo electrónico, no coincide con el patrón establecido, entonces pinta la celda de color rojo y en la columna D fila correspondiente se muestra la leyenda «ERROR».

El patrón utilizado para contrastar y determinar si en la celda analizada se encuentra inserta una dirección de mail es el siguiente:

«^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,4}$»

Explicación del código para Validar un listado de Emails

El código está en el módulo 1, primero se crea un objeto con la hoja donde se está trabajando, a los fines de facilitar la escritura del código solamente, así:

Set a = Sheets(«Hoja1»)

Se determinar la última fila con datos para realizar un bucle hasta ficha fila, con el siguiente código:

uf = a.Range(«A» & Rows.Count).End(xlUp).Row

Se hace un bucle For … Next para analizar los datos de la fila 2 hasta al última fila con datos, se usa el código: 

For x = 2 To uf

… código de la macro

Next x

Luego se crea un objeto de expresiones regulares, para usar el patrón que establece si la cadena de caracteres analizada se condice un una dirección de mail, el código es:

With CreateObject(«vbscript.regexp»)
… código para establecer si es mail 
End With


Luego se establece el patrón con el cual se comparará lo escrito en la celdal con el siguiente código:

.Pattern = «^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,4}$»

Luego se analiza lo escrito, si coincide o no con el patrón dado en caso de ser verdadero, significa que se ha escrito una dirección de email correcta.


If .test(a.Cells(x, «C»)) Then
a.Cells(x, «C»).Interior.Color = 65280
a.Cells(x, «D») = «OK»

En caso de ser negativo se ha escrito una dirección de correo electrónico incorrecta, el código usado es:

Else
a.Cells(x, «D») = «ERROR»
a.Cells(x, «C»).Interior.Color = 255
End If

Luego de analizar la cadena de caracteres y compararla con el patrón para establecer si se ha escrito una dirección de mail correcta, se finaliza la macro.

Código VBA para Determinar si las direcciones de Email de un Listado son Correctas

Sub ValidaMail()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set a = Sheets(«Hoja1»)
uf = a.Range(«A» & Rows.Count).End(xlUp).Row

For x = 2 To uf
With CreateObject(«vbscript.regexp»)
.Pattern = «^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,4}$»
If .test(a.Cells(x, «C»)) Then
a.Cells(x, «C»).Interior.Color = 65280
a.Cells(x, «D») = «OK»
Else
a.Cells(x, «D») = «ERROR»
a.Cells(x, «C»).Interior.Color = 255
End If
End With
Next x

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub



Descarga el Libro Como Validar Listado de Correos Electrónicos

Descarga desde acá el archivo usado como ejemplo en este post y en el vídeo explicativo, el mismo es totalmente gratuito y libre su uso, solicito aportar para sostener esta web, si está dentro de tus posibilidades, desde ya muchas gracias.

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Donate:
Cuenta Paypal: https://paypal.me/programarexcel
Cuenta Bitcoin: 1KBGGb8fyDzyR3X1Rie6m7VguzaAfngNbd
Cuenta Ether: 0x41Bbd24556914C83a31217eBb3BC49789b66e407

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (20)

discover our fine collections of swiss luxury rolex copy watches and exclusives timepieces for ladies and gents.

Responder

I’m not sure why but this site is loading incredibly slow for me. Is anyone else having this problem or is it a issue on my end? I’ll check back later on and see if the problem still exists.

Responder

We stumbled over here from a different website and thought I might as well check things out. I like what I see so i am just following you. Look forward to looking over your web page for a second time.

Responder

I am really enjoying the theme/design of your web site. Do you ever run into any browser compatibility problems? A handful of my blog readers have complained about my website not operating correctly in Explorer but looks great in Firefox. Do you have any recommendations to help fix this issue?

Responder

Spot on with this write-up, I truly think this website needs way more consideration. I’ll probably be again to read way more, thanks for that info.

Responder

Love watching sunset !

Responder

I love what you guys tend to be up too. This kind of clever work and exposure! Keep up the great works guys I’ve you guys to our blogroll. munchkin kittens for sale visit us http://fanceat.com/

Responder

Youre so cool! I dont suppose Ive read anything like this before. So nice to find any person with some authentic ideas on this subject. realy thank you for beginning this up. this web site is one thing that’s needed on the web, somebody with somewhat originality. helpful job for bringing one thing new to the internet!

Responder

You made some respectable points there. I regarded on the internet for the issue and found most people will associate with with your website.

Responder

When you take a trip minimizes stress, Preparation in advance. This is especially important for a diabetic. These 5 diabetes mellitus travel tips are simple to execute and also essential to your diabetic management. They are specifically essential if you are taking a trip abroad.

Responder

A variety of info on Technology is to be located by searching on the net, along with making use of the internet search engine. Taking your first actions – this is what the knowledge we have actually offered is fixated. So what you require to do now is take your primary steps, recognize that you will not achieve everything over night, however that you are making use of the actions required to get where you intend to go. It’s all about writing down your objectives, advising on your own daily of what you want to achieve, as well as arriving through persistence as well as willpower. A lot of standards and also approaches can be obtained at Our Technology Site, specifically in regard to aiding you to complete your objectives. Any individual that goes to this internet site can easily learn something from it, and despite having the large quantity of information, it will aid you ultimately.

Responder

It is so critical, possibly important, for you to have actually a service oriented approach when working with Technology. Undeniably, thousands of people are presently achieving this, yet managing Technology is not the simplest matter on this planet. Anybody that needs assistance with this should certainly absolutely go over to Our Technology Site to get added support in addition to suggestions.

Responder

A large range of info on Technology is to be found by searching on the web, as well as using the online search engine. Taking your initial steps – this is what the understanding we have offered is centered on. So what you need to do currently is take your very first steps, comprehend that you will not achieve everything overnight, yet that you are making use of the steps required to obtain where you plan to go. It’s all about listing your objectives, advising on your own daily of what you intend to accomplish, and getting there using tenacity in addition to willpower. A lot of standards and approaches can be acquired at Our Technology Site, particularly in regard to aiding you to complete your goals. Any individual that goes to this web site can quickly learn something from it, and despite having the huge quantity of details, it will certainly assist you in the end.

Responder

Your manner of addressin this subject is both fantastic and motivating.

Responder

Often when you are first gazing out, Technology can actually feel irritating, yet do not allow that quit you. Despite the fact that there is not any magic formula that will enable you to get to every objective by simply waving your magic stick, if you put in the job as well as use some basic as well as essential concepts all your objectives will certainly be obtainable. You have actually additionally got a great quantity of resources that can aid you as well as a great place to start is with Our Technology Site. You can in addition find help by checking out Our Technology Site.

Responder

Hi there, You have done a great job. I’ll certainly digg it and personally recommend to my friends. I’m confident they’ll be benefited from this site.

Responder

Hi there! This post couldn’t be written any better! Looking through this article reminds me of my previous roommate! He always kept talking about this. I will forward this article to him. Pretty sure he’s going to have a good read. I appreciate you for sharing!

Responder

Great post, now that so many people are working remotely and working from home.

Responder

It is a great deal greater than possible to invest all your time looking into Technology and also below is something which will certainly enable you to gather all the realities you need that are one of the most important to your objectives. If you would love to obtain good outcomes that really issue, you can’t avoid the essential steps. In the event that you aren’t clear about a detail or more, return as well as take a look at it once more or ask a individual who you count on. You don’t desire tiny problems to hold you down. Among the areas you can go when you wish to find out more is Our Technology Site, which has assisted a lot of individuals who are where you are.

Responder

I have been browsing online more than three hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as you did, the web will be much more useful than ever before.

Responder