separar datos y enviar como archivos Excel independientes (y 2)

Manos a la obra. Continúo con el tema de separar los datos y enviar a cada uno lo suyo (ver primera parte).

Para simplificar los bucles, vamos a definir algunos nombres en el libro, por ejemplo definimos el nombre ListaDestinatarios con el rango que contiene los nombres que luego aplicaremos en el filtro.

definir nombre con rango de destinatarios

También defino un nombre Datos que se refiere al rango donde se encuentran los datos.

Es muy recomendable que los datos estén en una tabla, así al añadir nuevos registros, la tabla se adapta al nuevo tamaño y no hay que perder el tiempo rehaciendo fórmulas.

Vamos a preparar una hoja auxiliar, que será el modelo que se enviará a los destinatarios.

En esta hoja vamos a poner los criterios de filtrado (así quien lo reciba sabrá qué se le está mandando). Para este ejemplo el único criterio va a ser el nombre del destinatario.

modelo de destino de los datos filtrados

También defino unos nombres:
CeldaCriterio que es la celda que contendrá el nombre del destinatario
RangoCriterios que contiene los rangos de filtrado, en nuestro caso la celda con el nombre del campo (destinatario) y la celda de debajo, que hemos llamado CeldaCriterio.
emaildestinatario en una celda de al lado, mediante BUSCARV hago que Excel busque el email del destinatario.
y EncabezadoResultado que contiene los encabezados dato1, dato3, dato3 de los datos que queremos extraer al filtrar.

El filtro

Si ejecutásemos el filtro avanzado a mano, haríamos algo así (aprovechando los nombres que hemos definido, que queda todo más claro)

Escribiría el nombre del destinatario en la celda criterio y luego aplicaría este filtro

filtro avanzado aplicado manualmente 

Hay que tener en cuenta una cosa, y es que el filtro avanzado debe ejecutarse en la hoja de destino, en nuestro caso, debe ejecutarse desde la hoja que contiene el modelo de los datos filtrados.

Truco para insertar un nombre definido, utiliza la tecla [F3]

Ahora con una macro

Vamos ahora a ver cómo sería una macro que aplicase este filtro (no os voy a engañar, he grabado una macro mientras ejecutaba el filtro a mano).

Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "RangoCriterios"), CopyToRange:=Range("EncabezadoResultado"), Unique:=False

Copiar la hoja y enviarla

Lo que hay que hacer a continuación es copiar esta hoja a un nuevo libro y después enviar el libro al destinatario (también he grabado una macro para esto)

Sheets("ModeloDatosFiltrados").Select
Sheets("ModeloDatosFiltrados").Copy

Para enviar el email, al grabar la macro emplea Application.Dialogs(xlDialogSendMail).Show, pero esto muestra el cuadro de diálogo para enviar el correo, así que no nos sirve, porque tendríamos que elegir al destinatario a mano.

Vamos a utilizar mejor el método sendmail del libro activo

Activeworkbook.SendMail  "emaildestinatario", "asunto"

Al enviar un email desde programación es probable que el programa de correo muestre una advertencia de seguridad. En el caso de Outlook es algo así, y puede evitarse modificando las opciones del centro de confianza (cada programa de correo tiene sus opciones).

El programa de correo deberá estar abierto para que empiece a mandar los emails.
Para más información sobre enviar correos desde Excel mira aquí y aquí.

El bucle que lo envuelve todo

No hay que olvidar que queremos repetir este filtro para cada destinatario de la lista. Pues vamos a hacer un bucle que recorra toda esta lista.

Los destinatarios están en el rango que hemos llamado ListaDestinatarios, así que recorremos las celdas de este rango con un bucle For Each...

y quedaría algo así.

Sub SepararYEnviar()
'macro que filtra datos y envía a cada destinatario una copia con lo suyo
Dim Celda As Range
Dim EmailDestino As String
'Activamos la hoja destino
Sheets("ModeloDatosFiltrados").Select
    For Each Celda In Range("ListaDestinatarios").Cells
        Range("CeldaCriterio").Value = Celda.Value       'ponemos el  criterio
        EmailDestino = Range("emaildestinatario").Value  'tomamos el email del destinatario
        ' aplicamos el filtro avanzado  
        Range("datos").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "RangoCriterios"), CopyToRange:=Range("EncabezadoResultado"), Unique:=False
       
        Sheets("ModeloDatosFiltrados").Copy ' copiamos la hoja a un nuevo libro
        ' el nuevo libro es ahora el activo
        ActiveWorkbook.SendMail EmailDestino,"envío de datos" 'enviamos el correo
        ActiveWorkbook.Close False  ' cerramos el nuevo libro sin guardar los cambios
    Next
    ' siempre es bueno avisar de que has terminado
    MsgBox "Mensajes enviados", vbInformation + vbOKOnly, "Ya sé Excel, pero necesito más"
End Sub 

Temática: 

Etiquetas: 

Comentarios

Re: separar datos y enviar como archivos Excel independientes (y

Imagen de visitante

Hola Paco:

Estoy intentando seguir tus pasos pero no me sale. Defino los nombres: [ListaDestinatarios] y [Datos] en mi fichero y el resto en la plantilla Modelo con los datos Filtrados.

Intento hacer el filtro avanzado (desde la plantilla, según tus indicaciones el filtro avanzado debe ejecutarse en la hoja de destino) y me dice que: Este comando requiere al menos dos filas de datos. 

¿como salgo de aquí?

gracias

 

Re: separar datos y enviar como archivos Excel independientes (y

Imagen de pacomegia

La hoja con el modelo de los datos filtrados está en el mismo libro que los datos, luego la copiaremos a un nuevo libro después de filtrar.

Excel trata de identificar dónde están los datos a partir de la celda activa y si no es capaz muestra ese error de las dos filas de datos.
Para que no te de el error el formulario del filtro avanzado selecciona un rango de celdas, por ejemplo, selecciona los encabezados de destino y la fila de debajo (aunque estén en blanco), con esto evitas el error y ya puedes editar los rangos y poner los nombres o lo que quieras.

 


 

------
Ya sé Excel, pero necesito más.

Re: separar datos y enviar como archivos Excel independientes (y

Imagen de visitante

Hola

 

En primer lugar comentar que me parece un tutorial excelente muchas gracias por tu tiempo.

 

Quiero comentarte un problemilla que tengo, hago todo lo que dices y consigo generar una hoja que se copia a un libro con datos, pero solo consigo que se copie la primera linea del conjunto marcado como DATOS, en otra ocasion he conseguido que se copien todas pero es como si no hiciese el recorrido por la "listadestinatarios" y fuese filtrando y copiando en cada paso del bucle.

no se si me puedes dar alguna pista de porque pasa esto .

 

muchas gracias de antemano.

así sin más pistas...

Imagen de pacomegia

así sin más pistas no sé lo que puede ser.

Puede ser la definición del nombre, el filtro, la macro, ...

Si pudieras adjuntar un ejemplo del archivo le puedo echar un vistazo.

(para poder adjuntar archivos hay que iniciar sesión antes)

 

------
Ya sé Excel, pero necesito más.

Error en el método "advancedfilter" de la clase range

Imagen de Jhonattan Cabrales Lara

Cordial saludo, excelente tutorial amigo.

A partir de esto uso intensivamente tu tutorial.

Sin embargo me firgura el error comentado por el compañero sobre las "dos filas de datos". Si el rango de extracción solo está compuesto por los encabezados, y la segunda línea está vacía me genera el error del asunto. Si lo hago con la grabadora de macros, me arroja el error las famosas "dos líneas". 

Supongamos que el rango de extracción sea A1:D1, para poder evitar ese error entonces agrego una línea de código:

    Range("A2").FormulaR1C1 = "." 'escribo lo que sea solamente para que el rango de extracción tenga dos líneas.

Pero estoy más que seguro que se podría evitar esta línea.

pd. ya hice lo de "expandir" el rango de extracción al encabezado más la línea de abajo tal cual como dijiste. Pero nada... solo puedo evitar el error añadiendo esa línea de código. 

Agradezcl cualquier ayudita!

 

los datos en una tabla

Imagen de pacomegia

Con los datos en una tabla quiero decir que utilices una Tabla,
está en Insertar>Tablas>Tabla:

Insertar una tabla en Excel

desde mi punto de vista, esto es lo mejor de Excel 2007.

 


 

------
Ya sé Excel, pero necesito más.

MsoEnvelope.Introduction

Imagen de pacomegia

prueba con el objeto MsoEnvelope

tiene la propiedad Introduction que permite añadir un texto al mensaje.

Si quieres control total sobre los mensajes de correo, écha un vistazo a esta página http://www.rondebruin.nl/cdo.htm

 

 

------
Ya sé Excel, pero necesito más.

Re: separar datos y enviar como archivos Excel ...

Imagen de pacomegia

¿qué error te da? y ¿dónde te da ese error?

la macro supone que hay ciertos nombres definidos en el libro:

Datos, ListaDentinatarios, CeldaCriterio

también una hoja llamada  ModeloDatosFiltrados 

¿tienes esos nombres en tu libro?

 

------
Ya sé Excel, pero necesito más.

Re: separar datos y enviar como archivos Excel ...

Imagen de joejones

Hola no se si es un criterio que tengo mal definido, los criterios que creé son.

 

Hoja datos: Datos y ListaDestinatarios

Hoja ModeloDatosFiltrados: RangoCriterios , Celda Criterio, emaildestinatario y EncabezadoResultado

 

 Nose si me falta algun criterio mas, el error que aparece dice

 

 " Se ha producido un erro '1004' en tiempo de ejecución: Erro definido por la aplicacion o el objeto"

 

Gracias por tu ayuda.

Re: separar datos y enviar como archivos Excel ...

Imagen de pacomegia

no sé muy bien por qué, pero no reconoce el nombre CeldaCriterio si no haces referencia a la hoja

prueba a modificar la línea

Range("CeldaCriterio").Value = Celda.Value    'esto da el error

añadiendo ActiveSheet, así:

ActiveSheet.Range("CeldaCriterio").Value = Celda.Value    'así no da error

 

------
Ya sé Excel, pero necesito más.

Hola, muchas gracias por el

Imagen de Eneri

Hola, muchas gracias por el aporte! Estaba justo buscando la manera de hacer esto pero la macro me da error 1004 y por más que compruebo los nombres y demás, no sé qué estoy haciendo mal. Si me pudieras echar una mano, te lo agradecería muchísimo. Muchas gracias de antemano, Eneri.

como en el comentario

Imagen de pacomegia

como en el comentario anterior, según la versión de Excel que utilices, puede que necesites hacer referencia explícita a la hoja donde está el rango, anteponlo a la llamada al rango que da error a ver si ahora funciona.

Worksheets("Sheets1").Range("ListaDestinatarios").Cells

 

------
Ya sé Excel, pero necesito más.

DIFICULTAD

Imagen de IUSUGA

Buenos días,

Primero darte mil gracias por compartir todo tu conocimiento, y  segundo apliqué esta macro, la cual me funciono muy bien, pero cuando tengo varios correos para un solo destinatario me muestra error, mil gracias!!!

DIFICULTAD

Imagen de IUSUGA

Muchas gracias por su pronta respuesta, también lo cambie a coma y me genera el mismo error  Triste

array

Imagen de pacomegia

Hola:

parece que Sendmail espera un array de direcciones, así que puedes convertir tu texto con varias direcciones de correo separadas por comas en un array mediante la función SPLIT

modifica la línea con sendmail, y pon lo siguiente:

ActiveWorkbook.SendMail Split(EmailDestino,","),"envío de datos" 'enviamos el correo

he puesto una coma como separador, pero puedes pner el separador que quieras.

 

------
Ya sé Excel, pero necesito más.

Perfecto si me sirvió,!!!! Es

Imagen de Iusuga

Perfecto si me sirvió,!!!! Es posible que cree archivos de excel y que estos se les asigne el nombre de acuerdo a lo descrito en alguna columna de la tabla? Y q estos archivos sean los q adjunte al mensaje? Mil gracias, saludos desde Colombia

No me funciona la macro

Imagen de Jessica

Estimado, por mas qu ehe aplicado todas las modificaciones indicadas al ejecutar me aparece error 400 y no especifica en que parte de la macro sucede el error.

 

Por favor su ayuda.

De antemano gracias.

pregunta en el foro

Imagen de pacomegia

Lo mejor es que crees un tema nuevo en el foro, adjuntes un libro de ejemplo con el código que estar utilizando para poder verlo.

 

------
Ya sé Excel, pero necesito más.

No recorre la lista destinatarios

Imagen de alvaroc

Hola Paco, muchas gracias por el manual.

Mi problema es que cuando ejecuto la macro me envía tantos mails como destinatarios tengo en la lista pero todos con la misma tabla. Cuando salta un error al final de la ejecución de la macro y le doy a Debug me señala la línea:
EmailDestino = Range("emaildestinatario").Value  'tomamos el email del destinatario

Gracias por tu ayuda,

Álvaro

Múltiples criterios

Imagen de Jorge Esquivel

Hola!

Primero felicitarte por el post está excelente, solo que en mi casi tengo 4 criterios, y al ejecutar esta macro les asigna a los 4 el mismo valor y me trae datos erroneos, ¿me podrías apoyar?.

 

Saludos!

Sí, pregúntalo en el foro y

Imagen de pacomegia

Sí, pregúntalo en el foro y adjunta un ejemplo de lo que estás haciendo y de lo que quieres conseguir para ver qué parte del código está mal o cómo estás definiendo tus criterios para el filtro avanzado.

 

 

------
Ya sé Excel, pero necesito más.

Agregar texto dinamico + nombre al archivo

Imagen de Paulo

Que excelente tutorial, me funciona, pero necesito agregar algunas funciones.

1. Incorporar texto del mensaje de forma dinamica, por ejemplo:

Le enviamos el detalle de sus ventas sr (destinatario)

2. El archivo adjunto tenga nombre de acuerdo al destinatario...

3. Incorporar un CC

Como quedaria aquel codigo? debo crear un for nuevo? o debo incluirlo dentro del for??

 

Muchisimas gracias