sábado, 30 de junio de 2018

Microsoft Outlook desde Excel (III): Alertas a través del correo electrónico

Como ya he comentado en otros artículos, en los foros/comunidades de Excel entre las preguntas más frecuentes están las relacionadas a cómo enviar mensajes de correo electrónico a través de Macros de Excel. Muchas de dichas preguntas incluso tienen carácter más puntual y no es raro que las personas pregunten por “alertas” automáticas cuando, por ejemplo, se cumplen ciertas fechas que se consideran límites. En esta ocasión vamos a dar algunos consejos para esos casos y usando Microsoft Outlook.

Primero, por si acaso, recordemos como enviar mensajes a través de Outlook: Enlace_1, luego también recordemos como enviar mensajes masivos: Enlace_2, algo que nos será útil en esta ocasión. Ahora vamos a suponer que tenemos los siguientes datos:

Lo que vamos a hacer que la macro se active a través de evento “Open” del libro, por lo que pondremos lo siguiente en el módulo del libro (que lleva por defecto el nombre “ThisWorkBook”):

Private Sub Workbook_Open() 
Call EnviarAlertas
End Sub  
De ese modo la macro se activará automáticamente al abrir el archivo. Luego, a través de un bucle (For – Next) recorremos la columna de la fecha de vencimiento y si es inferior al día en que se abrió el archivo, entonces se enviará un mensaje de correo a la persona recordándole su deuda. Incluiremos una copia para uno mismo. En la columna “E” (“Notificado”) agregaremos un “Sí” después de que se envíe el mensaje, de ese modo al volver a abrir nuevamente el archivo, a través de un “If” se verificará dicha columna y de haber dicha palabra (ojo, cualquiera en realidad en éste caso), a esa persona no se le volverá a enviar el mensaje.

Entonces, dicho todo eso, nuestra macro debería quedar así:

Sub EnviarAlertas() 
Dim OutlookApp As Outlook.Application 
Dim objItem As MailItem 
Dim UltimaFila As Long, x As Long 
Dim FechaV As Date 

Set OutlookApp = CreateObject("Outlook.Application") 
Let UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row 

For x = 2 To UltimaFila
     Let FechaV = Range("D" & x).Value
     If FechaV < Date And Range("E" & x).Value = "" Then
         Set objItem = OutlookApp.CreateItem(olMailItem)
         With objItem
             .To = Range("B" & x).Value
             .cc = "tucorreo@correo.com"
             .Subject = "Deuda vencida"
             .Body = "Estimado/a señor/a " & Range("A" & x) & " su cuota de " & FormatCurrency(Range("C" & x).Value) & " venció el día " & Range("D" & x).Value
             .Send
         End With
         Set objItem = Nothing
         Range("E" & x).Value = "Sí"
     End If
Next x
Set OutlookApp = Nothing
MsgBox "Cuentas revisadas"
End Sub  

No olvides activar la referencia a "Microsoft Outlook 15.0 Object Library" en el editor de VBA (en donde el 15.0 puede variar dependiendo de tu versión de Excel).

Y listo, eso es todo en esta ocasión, solo adáptalo a tus necesidades. Hasta la próxima.

Abraham Valencia

jueves, 28 de junio de 2018

Microsoft Outlook desde Excel (II): Enviar mensajes masivos

Entre las preguntas más seguidas que se encuentra en los foros de Excel están aquellas relacionadas al envío de mensajes de correo electrónico a través de macros. Hay mucha información en internet al respecto, tanto usando Microsoft Outlook como usando la librería “Collaboration Data Objects” (CDO). En esta ocasión vamos a centrarnos en usar Microsoft Outlook.
Si bien, como ya mencioné, hay mucha información respecto al envío de mensajes, no necesariamente es tan fácil encontrar cosas específicas como el realizar envíos masivos y además adjuntando algún archivo y precisamente eso es lo que mostraremos como hacer.

Lo primero es que, por si acaso, den una leidita a esto: Enlace. Ahora, vamos a suponer que tenemos los datos, incluyendo el correo electrónico, de aquellas personas a las que queremos enviar el mensaje. Los datos para el ejemplo tendrán: Nombre, correo, fecha (de un supuesto reporte), ruta (del supuesto archivo/reporte pdf).

Sabemos entonces en que columna está cada tipo dato. Vamos a suponer también que las filas de datos pueden ir incrementándose, por lo que será necesario averiguar la última fila de manera automática para lo que usaremos la variable “UltimaFila”. Como son varios registros usaremos un bucle del tipo “For – Next” que comience a recorrer todo desde la fila 2 ya que la 1 es la de los encabezados.

Como ya expliqué en otro artículo, cuando se usa el objeto "Outlook", y justamente se nota más cuando se hacen envíos masivos, lo que ocurre es que se van colocando los mensajes en la "Bandeja de salida" así que es recomendable que se tenga el Microsoft Outlook configurado para el envío automático cuando se abre/cierra para que el proceso continúe cuando se use otra vez dicho programa ya que probablemente la macro no termine de enviar los mensajes. Igual vamos a usar “Application.Wait” para que haga una pausa de 10 segundos a la macro y esperar que sea tiempo suficiente para que adjunten los archivos por completo y se envíe el mensaje antes de que siga el bucle (que irá acumulando en la “fila” de la bandeja de salida del Outlook).

Entonces, dicho todo eso, nuestra macro debería quedar así:
Sub EnviarMensajeOutlook()

Dim OutlookApp As Outlook.Application 
Dim objItem As MailItem 
Dim UltimaFila As Integer, x As Integer 

Set OutlookApp = CreateObject("Outlook.Application") 
Let UltimaFila = Cells(Rows.Count, 2).End(xlUp).Row 

For x = 2 To UltimaFila 
Set objItem = OutlookApp.CreateItem(olMailItem) 
With objItem     
  .To = Range("B" & x).Value     
  .Subject = "Reporte de compras"     
  .Body = "Estimado/a señor/a " & Range("A" & x) & " le enviamos el repote del día " & Range("C" & x).Value     
  .Attachments.Add Range("D" & x).Value      
Application.Wait (Now + TimeValue("00:00:10"))          
  .Send 
End With 
Set objItem = Nothing 
Next x 

Set OutlookApp = Nothing 
MsgBox "Todo enviado" 

End Sub 

No olvides activar la referencia a "Microsoft Outlook 15.0 Object Library" en el editor de VBA (en donde el 15.0 puede variar dependiendo de tu versión de Excel).

Y listo, eso es todo, solo adáptalo a tus necesidades. Hasta la próxima.
Abraham Valencia

jueves, 31 de mayo de 2018

Las tablas en Excel

En muchas ocasiones tenemos datos en nuestras hojas en Excel y en base a esos datos tenemos fórmulas, tablas dinámicas u otras. Normalmente lo tenemos hecho tipo tablas, algo así por ejemplo:

Si para ese caso quisiéramos saber el total de ventas tendríamos que usar una fórmula de este tipo:

=SUMA(D2:D15)

Del mismo modo para crear una tabla dinámica, elegiríamos el rango “A1:D15”. Pero ¿qué pasa en esos casos cuando agregamos un registro? Es decir, si agregamos datos a la fila 16. Pues dicho registro no estará entre los datos que muestra la tabla dinámica y la fórmula anterior no tomará en cuenta el nuevo dato de la columna, es decir la celda “D16”. Podríamos, por ejemplo, usar rangos dinámicos con nombre (usando por ejemplo la función DESREF) pero existe una forma mucho más simple: Usar las tablas de Excel.

Las tablas son un conjunto de filas y columnas con datos y que Excel puede relacionar entre sí. La primera fila de una tabla contiene los encabezados de cada columna y eso ayudará a identificarlas. Las tablas facilitan el trabajo de datos con Excel.

Para convertir un rango en “Tabla” de Excel, colocamos el cursor en cualquier celda del rango en donde tenemos nuestros datos, luego elegimos, en la cinta de opciones, la pestaña “Insertar” y en el grupo “Tablas” damos click al botón “Tabla”, Excel seleccionará todo nuestro rango de datos y nos mostrará un cuadro de dialogo llamado “Crear tabla”. Ahí verificamos que todos los datos estén seleccionados y elegimos, de ser el caso, que “La tabla tiene encabezados”.

Los datos pasaran a tener esta apariencia:

Puedes cambiar los colores/estilo del diseño, el nombre de la tabla, exportar los datos, etc., haciendo uso de las “Herramientas de tabla”.

Si tenemos los datos como tabla, podríamos usar fórmulas de este tipo:

=SUMAR.SI(Tabla1[Local],"A",Tabla1[Venta])
=SUMA(Tabla1[Venta])

De agregar más datos a la tabla, si comenzamos por la primera fila sin datos inmediata inferior a dicha tabla y usamos las mismas columnas, automáticamente el Excel hará que la tabla extienda su rango. De esa forma las fórmulas seguirán actuando sobre los datos si necesidad de que tengamos que volver a modificarlas. Lo mismo ocurrirá, por ejemplo, con las tablas dinámicas cuyo origen de datos sea una tabla. Además de eso, las tablas, en cada columna cuentan con filtros que podemos usar de ser necesario:

Y eso es todo en esta ocasión. Hasta la próxima.

Abraham Valencia

miércoles, 23 de mayo de 2018

Trabajar, desde Excel-VBA, con otros programas de Microsoft

Cuando programas, una de las grandes ventajas de trabajar con Excel y toda la suite de Microsoft es la compatibilidad entre su lenguaje Visual Basic for Applications (VBA). De este modo podemos trabajar con más de uno de sus programas a la vez y/o interactuar entre ellos solo debemos aprender, tal como en el caso de Excel, los objetos, propiedades y otros de cada programa.

Una forma de abrir o crear archivos desde el VBA es usar la función “CreateObject”. La sintaxis de dicho método es la siguiente:

CreateObject (clase, nombre del servidor)

En donde “clase” es el nombre de la aplicación y/o la clase del objeto para crear y “nombre del servidor” es justamente el nombre del servidor de red en donde se crea el objeto. Si el nombre de servidor se deja en blanco, el objeto se creará en el equipo desde donde se usa la función.

Vamos a dejar de lado lo del servidor y centrarnos en la creación de objeto en nuestro propio equipo. Por ejemplo, vamos a suponer que queremos crear un archivo de Word nuevo entonces lo primero que vamos a hacer es declarar una variable con la que nombraremos a nuestro objeto “Word”, después crearemos el objeto, lo haremos visible (por defecto se crea el objeto como no visible) y agregaremos un documento nuevo:

Sub CreandoWord()
Dim MiappWord As Object 
Set MiappWord = CreateObject("Word.Application") 
MiappWord.Documents.Add 
MiappWord.Visible = True 
Set MiappWord = Nothing 
End Sub

La línea “MiappWord.Documents.Add”, que es la que agrega el documento nuevo de Word, puede ser obtenida fácilmente con la grabadora de macros de Word, aunque lo menciono solo como referencia ya que ese sería un tema ajeno a Excel.

Supongamos ahora lo que queremos es abrir un archivo ya existente, pues usaríamos algo como esto:

Sub CreandoWord() 
Dim MiappWord As Object 
Set MiappWord = CreateObject("Word.Application") 
MiappWord.Documents.Open ("D:\Filatelia\Actividades CAF 2018.docx") 
MiappWord.Visible = True 
Set MiappWord = Nothing 
End Sub

Una forma en que el editor de VBA nos puede “ayudar” con los objetos, propiedades y métodos de, en este caso, Word, es si declaramos la referencia respectiva:

No olviden que el 15.0 puede variar dependiendo de la versión de Office que estén usando. Luego cambiamos la declaración de variable a tipo “Word.Application” y tendremos el siguiente resultado:

También podemos, por ejemplo, abrir una presentación hecha en Power Point del siguiente modo:

Sub AbrirPPT() 
Dim appPpt As Object 
Set appPpt = CreateObject("Powerpoint.Application") 
appPpt.Presentations.Open Filename:=ThisWorkbook.Path & "\Hola.pptx" 
appPpt.ActivePresentation.SlideShowSettings.Run 
appPpt.Visible = True 
Set appPpt = Nothing 
End Sub 

En este caso el archivo de Power Point está en la misma carpeta que el archivo Excel. La línea que abren dicho archivo y la que activa la presentación, son parte del manejo de objetos y métodos de dicho programa pero lamentablemente en este caso dicho programa ya no cuenta con grabadora de macros que nos ayude pues Microsoft decidió retirarla desde la versión de Office 2007.

Es casi el mismo procedimiento para Infopath, Publisher, etc., pero no olvidemos que puede servir para otros programas como por ejemplo Internet Explorer:

Sub Guglear() 
Dim ie As ObjectDim 
PaginaWeb As String 
PaginaWeb = "www.google.com.pe" 
Set ie = CreateObject("InternetExplorer.Application") 
ie.Navigate PaginaWeb 
Do     
 DoEvents 
Loop Until ie.readyState = 4 
ie.Visible = True 
Set ie = Nothing 
End Sub  

Y eso es todo en esta ocasión. Hasta la próxima.

Abraham Valencia