Creación de un sistema de alertas por correo desde Excel con VBA
Palabra clave principal: Creación de un sistema de alertas por correo desde Excel con VBA
Definición del problema
En el ámbito de la gestión de operaciones y la cadena de suministro, la detección temprana de desviaciones es crítica para evitar pérdidas económicas y retrasos en la producción. Consideremos el caso de una Empresa de Distribución de Componentes Electrónicos. Esta empresa maneja un inventario de más de 500 SKUs. El proceso actual de monitoreo de stock se realiza manualmente revisando hojas de cálculo al final del día.
El reto operativo es el siguiente: Necesitamos un sistema automatizado que revise diariamente el inventario de componentes críticos. Si la cantidad de un producto específico cae por debajo de su Nivel de Stock Mínimo de Seguridad (NSMS), el responsable de compras debe ser notificado inmediatamente. La dependencia de la revisión manual genera latencia, lo que puede provocar paradas de línea de producción o incumplimiento de pedidos.
El objetivo de este instructivo es implementar una solución robusta utilizando VBA (Visual Basic for Applications) en Excel para que, al ejecutarse un macro, verifique las condiciones de stock y, si se detecta una alerta, envíe un correo electrónico automatizado al departamento de compras.
Explicación técnica
La solución se basa en la integración de tres componentes principales de Excel y VBA:
- Estructura de Datos (Excel): Se utilizará una hoja de cálculo estructurada (Tabla de Inventario) que contendrá campos clave como ID del Producto, Nombre, Stock Actual, NSMS y Proveedor.
- Lógica de Condición (VBA): El código VBA recorrerá fila por fila la tabla de inventario. Para cada fila, ejecutará una sentencia condicional (
If...Then...Else) que comparará el valor de «Stock Actual» con el valor de «NSMS». - Comunicación (VBA & Outlook/SMTP): Si la condición se cumple (Stock Actual < NSMS), el código invocará la funcionalidad de correo electrónico. Generalmente, esto se logra interactuando con la aplicación Microsoft Outlook instalada en la máquina del usuario (usando objetos de Outlook) o configurando un servidor SMTP externo. Para este tutorial, asumiremos la integración con Outlook por su simplicidad en entornos de oficina.
Conceptos clave: Looping (recorrer registros), Conditional Logic (evaluación de condiciones) y Object Model Interaction (manipulación de aplicaciones externas como Outlook).
Guía paso a paso
Paso 1: Preparación del Entorno y Datos de Ejemplo
Primero, debemos crear la base de datos de inventario. Abrir Excel y configurar la hoja de trabajo.
- Acción: En la Hoja1, ingresar los encabezados en la fila 1: A1=»ID Producto», B1=»Nombre Producto», C1=»Stock Actual», D1=»NSMS», E1=»Email Compras».
- Por qué: Definir la estructura de datos que el código VBA leerá y procesará.
- Resultado: Una tabla lista para recibir datos.
A continuación, se simula la carga de datos iniciales para el caso práctico:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID Producto | Nombre Producto | Stock Actual | NSMS | Email Compras |
| 2 | P001 | Resistor 10k | 1500 | 500 | [email protected] |
| 3 | P002 | Capacitor 1uF | 350 | 500 | [email protected] |
| 4 | P003 | IC Microcontrolador | 80 | 100 | [email protected] |
Paso 2: Habilitar la Pestaña Desarrollador y Abrir el Editor VBA
- Acción: Ir a Archivo > Opciones > Personalizar cinta de opciones. Marcar la casilla «Desarrollador». Luego, hacer clic en la pestaña «Desarrollador» y seleccionar «Visual Basic» (o presionar Alt + F11).
- Por qué: El entorno VBA es donde escribiremos la lógica de automatización.
- Resultado: Se abre el Editor de Visual Basic para Aplicaciones (VBE).
Paso 3: Insertar el Módulo y Escribir el Código Base
- Acción: En el VBE, ir a Insertar > Módulo. Pegar el siguiente código (se explica a continuación).
- Por qué: Los módulos son contenedores donde residen las macros que ejecutan la lógica.
- Resultado: Un nuevo módulo en el explorador de proyectos listo para contener la macro.
Código VBA (Ejemplo):
Sub VerificarAlertasStock()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Hoja1") ' Asegurarse que el nombre de la hoja es correcto
Dim ultimaFila As Long
Dim i As Long
Dim stockActual As Long
Dim nsms As Long
Dim emailDestino As String
Dim alertaEncontrada As Boolean
' Determinar la última fila con datos (asumiendo que los datos empiezan en la fila 2)
ultimaFila = ws.Cells(Rows.Count, "C").End(xlUp).Row
alertaEncontrada = False
' Recorrer desde la fila 2 hasta la última fila
For i = 2 To ultimaFila
' Leer los valores de las celdas
stockActual = ws.Cells(i, 3).Value ' Columna C
nsms = ws.Cells(i, 4).Value ' Columna D
emailDestino = ws.Cells(i, 5).Value ' Columna E
' Lógica de Alerta: Si el stock es menor que el NSMS
If stockActual < nsms Then
alertaEncontrada = True
' Construir el cuerpo del correo
Dim asunto As String
Dim cuerpo As String
asunto = " ALERTA CRÍTICA DE INVENTARIO: " & ws.Cells(i, 2).Value
cuerpo = "Estimado equipo de Compras, nnEl componente '" & ws.Cells(i, 2).Value & "' ha alcanzado un nivel crítico de stock.n" & _
"Stock Actual: " & stockActual & " | NSMS: " & nsms & "n" & _
"Por favor, gestionar la reposición inmediatamente."
' Llamar a la función de envío de correo (Se requiere configuración de Outlook)
Call EnviarCorreo(emailDestino, asunto, cuerpo)
End If
Next i
If Not alertaEncontrada Then
MsgBox "Verificación de inventario completada. No hay alertas críticas activas.", vbInformation
End If
End Sub
' Función auxiliar para enviar el correo (Requiere referencia a Microsoft Outlook Object Library)
Sub EnviarCorreo(destinatario As String, asunto As String, cuerpo As String)
On Error Resume Next
Dim outlookApp As Object
Dim outlookMail As Object
Set outlookApp = CreateObject("Outlook.Application")
If Not outlookApp Is Nothing Then
Set outlookMail = outlookApp.CreateItem(0) ' 0 = olMailItem
With outlookMail
.To = destinatario
.Subject = asunto
.Body = cuerpo
.Send ' O .Display para revisar antes de enviar
End With
Set outlookMail = Nothing
End If
Set outlookApp = Nothing
End Sub
Paso 4: Configuración de Referencias y Ejecución
- Acción: En el VBE, ir a Herramientas > Referencias. Buscar y marcar la casilla «Microsoft Outlook XX.X Object Library».
- Por qué: Esto permite que VBA «entienda» los comandos específicos de Outlook (como .Send o .To).
- Resultado: El código puede interactuar correctamente con la aplicación Outlook.
- Acción: Volver a Excel. Ir a la pestaña «Desarrollador» y hacer clic en «Macros». Seleccionar
VerificarAlertasStocky hacer clic en «Ejecutar». - Por qué: Ejecutar la macro para que procese los datos de la hoja.
- Resultado: Si el stock de P002 (350 < 500) o P003 (80 < 100) es menor al NSMS, se abrirá un correo electrónico listo para ser enviado (o se enviará directamente si se usa .Send).
Ejercicio propuesto
Para consolidar el aprendizaje, se propone una extensión al sistema de alertas:
Objetivo: Implementar una alerta secundaria para productos que están «cerca» del límite, pero aún no lo han cruzado. Definir un Umbral de Advertencia (UA), que sea el 80% del NSMS.
Tarea: Modificar el bucle For i = 2 To ultimaFila en el código VBA. Añadir una segunda condición ElseIf que verifique si Stock Actual < (NSMS * 0.8). Si esta condición se cumple, en lugar de enviar un correo de «Alerta Crítica», debe registrar un mensaje en una celda específica de la Hoja2 (ej. Celda A1 de Hoja2) indicando el producto y el nivel de advertencia, sin enviar correo.
Esto simula la necesidad de un sistema de priorización de notificaciones.
Errores habituales
- Error: «Compile error: User-defined type not defined»
Causa: Olvidar marcar la referencia a la «Microsoft Outlook Object Library» en el VBE (Paso 4). VBA no sabe qué es un objeto de Outlook.
Solución: Ir a Herramientas > Referencias y asegurarse de que la librería de Outlook esté marcada.
- Error: El código se ejecuta, pero no envía correo.
Causa: El código está configurado para usar
.Send, pero el usuario no tiene permisos de envío o la aplicación Outlook no está instalada o configurada correctamente en la máquina.Solución: Cambiar la línea
.Sendpor.Display. Esto abrirá el correo en la bandeja de salida de Outlook para que el usuario pueda revisarlo y enviarlo manualmente, confirmando que la lógica de la alerta es correcta. - Error: El bucle no procesa los datos correctamente.
Causa: La determinación de la última fila (
ultimaFila = ws.Cells(Rows.Count, "C").End(xlUp).Row) está mal, o los datos no están en la columna esperada. Además, si los datos son texto en lugar de números, la comparaciónstockActual < nsmsfallará.Solución: Verificar que las columnas C y D contengan valores numéricos puros. Si hay encabezados o texto en las filas de datos, ajustar el inicio del bucle (
For i = 2 To ultimaFila) y asegurarse de que la lectura de celdas (ws.Cells(i, 3).Value) sea precisa.
Deja una respuesta