COMBINAR CORRESPONDENCIA EN EXCEL Y GUARDAR EN PDF

Hola a todos!, espero que todo os vaya bien 🙂

Llevo varias semanas que no publico nuevo material, el motivo no es otro que la falta de tiempo. Lo cierto es que entre la temporada veraniega, la reincorporación al trabajo y temas pendientes, se hace complicado sacar un momento para escribir nuevas entradas.

Estos días sin embargo, tuve tiempo para preparar un post bastante interesante. Como suele ser habitual, el tema surgió a raíz de una consulta de una lector. Me preguntaba acerca de la posibilidad de realizar combinar correspondencia en Excel y poder pasar cada uno de los documentos a PDF de forma individual.

Normalmente, el proceso de combinar correspondencia se inicia desde Microsoft Word y se accede a Excel para buscar la base de datos en la que tenemos la información necesaria para enviar la correspondencia. Prácticamente se pueden realizar todo tipo de envíos masivos y enviar la información a través de correo electrónico. También se pueden imprimir los documentos (es posible que con paciencia se puedan imprimir con algún programa a PDF de uno en uno, especificándolo en el cuadro de diálogo de la impresora).
Para más información sobre combinar correspondencia os dejo el siguiente enlace: COMBINAR CORRESPONDENCIA

Pues bien, en casi la totalidad de los casos podremos solucionar nuestras necesidades con Word. Pero si queremos pasar cada uno de los archivos que queremos enviar a PDF y guardarlos en un directorio, Excel es una buena solución. Para hacerlo, debemos construir nuestra propia aplicación de combinar correspondencia.

Después de este pequeño comentario, ya estamos listos para comenzar. Como siempre, vamos a ver la base de datos que hemos confeccionado para enviarles una comunicación, pestaña “DATOS”:

combinar-correspondencia-en-excel-y-guardar-en-pdf

El siguiente paso ahora es crear la plantilla que vamos a utilizar para incorporar los datos a enviar. La plantilla la vamos a crear directamente en una hoja Excel, lo haremos teniendo en cuenta los formatos que podemos darle al texto en cada línea de la hoja, y además crearemos una serie de marcadores que luego vamos a utilizar para trasladar los datos de cada persona. Esta es la plantilla:

combinar-correspondencia-en-excel-y-guardar-en-pdf1

Como podéis observar, aquellos campos que hacen referencia a los datos que iremos incorporando en cada carta los marcaremos entre corchetes “<>”, luego en la macro haremos referencia a ellos para reemplazarlos.

Es importante que vayáis configurando en cada línea lo datos de la forma en la que saldrán finalmente, aunque esto lo podéis hacer realizando varias pruebas para depurar el diseño.

La plantilla “GENERAR” no contiene datos, será la hoja en la que se vuelque una copia de “PLANTILLA” y en la que iremos colocando cada registro de la hoja “DATOS”. Lo que sí es importante es que en la hoja “GENERAR”, las líneas y las columnas tengan en mismo ancho que la hoja “PLANTILLA”, o por lo menos tener en cuenta que el PDF final tendrá el mismo formato que la hoja “GENERAR”:

combinar-correspondencia-en-excel-y-guardar-en-pdf2

Ahora que ya tenemos la carta incorporada en nuestro archivo y las hojas creadas, ya podemos ir a la programación, debemos incluir esta macro:

Sub COMBINAR_CORRESPONDENCIA()
Dim i As Double
Dim ruta As String
Application.ScreenUpdating = False
'Activamos nuestro libro
ThisWorkbook.Activate
Sheets(2).Name = "GENERAR"
'seleccionamos hoja "GENERAR"
Sheets("GENERAR").Select
'Contamos el número de casos
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Elegimos la carpeta donde queremos guardar los archivos
On Error Resume Next
With CreateObject("shell.application")
ruta = .browseforfolder(0, Titulo, 0).Items.Item.Path
End With: On Error GoTo 0
'Si no elegimos la carpeta de destino, la macro se para
If ruta = Empty Then
MsgBox "DEBES SELECCIONAR UNA CARPETA DE DESTINO, PULSA DE NUEVO EL BOTÓN GENERAR", vbExclamation
Exit Sub
End If
'Iniciamos un for
For i = 2 To Fin
'Creamos variables para cada uno de los datos a incorporar en la hoja "GENERAR"
Nombre = Sheets("DATOS").Cells(i, 1)
Apellidos = Sheets("DATOS").Cells(i, 2)
Lugar = Sheets("DATOS").Cells(i, 3)
Fecha = Format(Sheets("DATOS").Cells(i, 4), "[$-C0A]d ""de"" mmmm ""de"" yyyy;@")
ExcelSignum = Sheets("DATOS").Cells(i, 5)
Email = Sheets("DATOS").Cells(i, 6)
Firma = Sheets("DATOS").Cells(i, 7)
'Llamamos a la macro Actualiza
Call ACTUALIZA
'Damos nombre a la hoja activa, que es GENERAR
ActiveSheet.Name = Sheets("DATOS").Cells(i, 1) & " " & Sheets("DATOS").Cells(i, 2)
With ActiveSheet
'Reemplazamos los datos en los marcadores que hemos creado en Plantilla
Cells.Replace What:="<NOMBRE>", Replacement:=Nombre, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<APELLIDO>", Replacement:=Apellidos, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<LUGAR>", Replacement:=Lugar, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<FECHA>", Replacement:=Fecha, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<EXCEL SIGNUM>", Replacement:=ExcelSignum, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<EMAIL>", Replacement:=Email, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<FIRMA>", Replacement:=Firma, LookAt:=xlPart, SearchOrder:=xlByRows
'Si queréis dar formato de hipervínculo a las celdas A6 y A10
'Solo tenéis que descomentar la parte indicada entre puntos:
'-----------------------------------------------------------
.Range("A6,A10").Select
With Selection
.Font.Color = RGB(0, 0, 255)
.Font.Underline = xlUnderlineStyleSingle
End With
'-----------------------------------------------------------
End With
'Publicamos en PDF, sin propiedades en el documento y sin abrir cada vez que se genere el PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ruta & "\" & ActiveSheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Volvemos a renombrar la hoja2 como "GENERAR"
Sheets(2).Name = "GENERAR"
Next
End Sub

Con esta macro debemos adjuntar esta otra, a la que hacemos referencia en la macro principal:

Sub ACTUALIZA()
Dim Shape As Excel.Shape
'Limpiamos contenidos en hoja "GENERAR"
Sheets("GENERAR").Select
Columns("A:A").ClearContents
'Eliminamos imagenes en la hoja Generar
For Each Shape In Sheets("GENERAR").Shapes
Shape.Delete
Next
'Copiamos la plantilla base desde la hoja "PLANTILLA" a "GENERAR"
'Seleccionamos el rango de FILAS hasta donde tenemos texto o un rango superior
Sheets("PLANTILLA").Select
Rows("1:50").Select
Selection.Copy
Sheets("GENERAR").Select
Rows("1:50").Select
ActiveSheet.Paste
End Sub

Cuando la macro se ejecute, directamente nos va a preguntar por una ubicación (directorio) en la que queremos guardar cada uno de los PDF generados. Si no elegimos, saldremos la macro y se parará el proceso. Si la elegimos, la macro pasará los datos a la hoja “GENERAR” mediante el comando reemplazar y sustituirá cada uno de registros marcados a través de un bucle for-next.

La otra macro, lo que hace es limpiar de todo contenido la hoja “GENERAR” incluso objetos, como firmas escaneadas, imágenes, logos, etc y traslada el texto de la hoja “PLANTILLA”.

Finalmente, guardamos la hoja “GENERAR” denominando a cada PDF con el nombre de las personas, el resultado es este:

combinar-correspondencia-en-excel-y-guardar-en-pdf3

y la comunicación esta:

combinar-correspondencia-en-excel-y-guardar-en-pdf4

De esta forma tendremos todos los archivos en PDF y guardados en el mismo directorio.

En la macro, os he dejado parte del código comentado, ese código sirve para dar formato a los hipervínculos que se mostrarán en el PDF si queremos que se destaquen en azul y subrayados. Es solo una medida puramente estética, el hipervínculo funciona perfectamente.

Y esto es todo, espero que os sea de utilidad.  🙂

Importante: la macro ha sido probada en Excel 2010, 2013 y 2016. En 2007 debería funcionar correctamente, dado que permite pasar documentos a PDF. En versiones anteriores no funcionará.

Descarga el archivo de ejemplo pulsando en: COMBINAR CORRESPONDENCIA EN EXCEL Y GUARDAR PDF

 

Anuncios

20 pensamientos en “COMBINAR CORRESPONDENCIA EN EXCEL Y GUARDAR EN PDF

    • Hola Christ,

      La macro HIPERVINCULO no se ejecuta con la macro, estaba en un módulo a parte. Sin embargo, he repasado el post y he cambiado la macro por otra y la he integrado en la macro principal.

      .Range("A6,A10").Select
      With Selection
      .Font.Color = RGB(0, 0, 255)
      .Font.Underline = xlUnderlineStyleSingle
      End With

      Simplemente nos limitamos a dar formato a los hipervínculos (color azul y subrayado). Está comentado, si quieres que te funciona solo tienes que descomentarlo quitando las comillas simples.

      Un saludo!!

      Me gusta

  1. Buenos días,

    Esta macro me viene genial, me resuelve casi todo lo que necesito, muchas gracias!!
    Solo hay una cosa que no me resuelve, me gustaría poder mandar los diferentes archivo guardado por Email a sus diferentes destinatarios (el Email de los destinatarios estarían guardado en la hoja DATOS,(Columna H por ejemplo)

    Esto se puede hacer?

    Gracias de nuevo

    Me gusta

    • Hola Adrien,

      En efecto, se puede hacer. Para ello es necesario, que declares estas tres variables en el código

      Dim olApp As Object
      Dim objMail As Object
      Dim Archivo As String

      y pegas esto después de haber guardado el pdf en la macro, es decir
      ……… OpenAfterPublish:=False

      y ahora pegas esto:
      Archivo = ruta & "\" & ActiveSheet.Name
      Set olApp = CreateObject("Outlook.Application")
      Set objMail = olApp.CreateItem(0)
      With objMail
      .Subject = "hola"
      .To = Sheets("DATOS").Cells(i, 8).Value
      .Attachments.Add Archivo & ".pdf"
      .send
      End With
      Set objMail = Nothing
      Set olApp = Nothing

      Envía el pdf a la dirección de correo que pongas en la columna H

      Saludos.

      Me gusta

  2. Hola, muchas gracias por esta macro, es casi lo que necesitaba, solo una cuestión, cuando realizo un filtro sobre los datos crea pdfs de todos los registros y no sólo de lo filtrado, hay alguna manera de que únicamente combinara y creará pdf de lo filtrado.
    Muchas gracias.

    Raquel.

    Me gusta

    • Hola Raquel,

      Prueba lo siguiente, has de modificar la macro:

      Justo después del bucle: For i = 2 To Fin
      Pones esto: If Worksheets("DATOS").Rows(i).Hidden = False Then

      Y cierras el condicional justo después de: Sheets(2).Name = “GENERAR”
      Casi al final de la macro, poniendo: end if

      Simplemente le decimos a Excel que cuando una fila esté visible, genere combinar correspondencia, y si está oculta simplemente no hará nada.

      Saludos.

      Me gusta

  3. Hola esta macro esta excelente, me funciona de maravilla, pero tengo un inconveniente, no me esta creando todas las cartas de los registros que tengo; tengo 10 y solo me crea 8, yo tengo el nombre de la persona en una sola celda y asi mismo se guarda pero nose cual sera la parte que falta para que me siga generando los otros

    Me gusta

    • Hola Tatiana:

      Tendría que ver la hoja con los datos que tienes. Aunque deberías verificar que no tienes filas en blanco y que los datos a combinar comienzan justo después del encabezado, en la fila 2. De lo contrario tendrías que modificar la macro según la distribución de los datos que tengas.

      Sin ver el documento, diría que tienes dos o más encabezados o filas en blanco. Puedes enviarme un ejemplo de como tienes la estructura de los datos, la hoja DATOS, y echo un vistazo.

      Saludos.

      Me gusta

  4. Holaaa muchas gracias ya pude arreglar el problema, ahora quiero colocar el link de las cartas creadas en una celda para poderlas adjuntar y enviar al correo de cada persona; lo que no he podido hacer es esa parte pues las cartas están guardadas con el nombre completo y así mismo quiero que se ubiquen en su respectivo lugar. Podrías ayudarme

    Me gusta

    • Hola Tatiana:

      Me alegro que hayas podido solucionar el problema. Sobre la nueva solicitud, fíjate en el comentario que hace Adrién (un poco más arriba) y la solución que le indico. Creo que es lo que necesitas.

      Saludos.

      Me gusta

  5. Hola ya pude hacer lo de las cartas, quedo super bien el trabajo de acuerdo a lo que necesitaba, muchas gracias tu macro me ayudo mucho y el codigo lo explicas todo a las mil maravillas… Mil gracias

    Me gusta

  6. Holaa De nuevo yo por aqui, ahora traigo un nuevo interrogante, tengo dos tipos de carta pero como hacer que se genere la carta respectiva dependiendo de una condicion o un dato que se encuentra en la hoja DATOS.
    Agradezco tu colaboración otra vez 😉

    Me gusta

  7. Buenos días:
    Me ha servido muchísimo esta macro! Pero tengo un problema, intento importar un campo llamado “Temario”, es un campo con bastante texto, y me da fallo.
    No obstante si meto solo 3 o 4 lineas como temario todo correcto. Alguien sabe que sucede? Muchas gracias por la ayuda!

    Me gusta

    • Hola Álvaro:

      Podrías enviar a excelsignum@yahoo.es ese ejemplo en concreto que te está ocasionando el error?, de esa forma se podría averiguar cual es el problema.

      Ten en cuenta que en la macro y en el ejemplo los campos nombre y apellidos se usan también para nombrar la pestaña de la hoja y esta tiene una serie de limitaciones:

      – El nombre de la hoja no puede superar los 31 caracteres.
      – No ha de contener los siguientes caracteres: \ / ¿ ? * [ ]
      – No puede quedar el nombre en blanco.

      Si esto sucede mostrará un error en la siguiente línea:

      ActiveSheet.Name = Sheets("DATOS").Cells(i, 1) & " " & Sheets("DATOS").Cells(i, 2)

      La solución es utilizar o introducir un campo nuevo, como la firma etc … que no está nombrando las hojas.

      Saludos

      Me gusta

  8. Hola, me parece maravillosa esta marco, no soy experto en excel y no se como solucionar el siguiente problema. Cuando doy clic en el botón de genera, sale un error “DEBE SELECCIONAR PRIMERO EL DESTINO…”, pero no se como hacerlo. De antemano muchas gracias por su ayuda. Saludos

    Me gusta

¿Te ha gustado?. Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s