Comunicación con Outlook

Continuando con la interacción entre los distintos programas de Microsoft con Excel esta vez le toca el turno a Outlook.

El procedimiento que viene a continuación nos permite crear mensajes (mails) Outlook y enviar el libro activo como dato adjunto.

Antes de ejecutar el procedimiento deberemos de seleccionar la referencia Microsoft Outlook 14.0 Object Librery desde el menú herramientas.

A continuación copiamos el procedimiento en un modulo.

Sub EnviarCorreo()

Dim appOutlook As Outlook.Application

Dim mensage As Outlook.MailItem

Set appOutlook = CreateObject("outlook.application")

Set mensage = appOutlook.CreateItem(olMailItem)

With mensage

    .Subject = "Prueba"

    .Body = " Prueba con datos adjuntos..... "

    .BodyFormat = olFormatHTML

    .Recipients.Add ("Nombre1")

    .Recipients.Add ("Nombre2")

    .Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name

    .Display

    .Send

End With

appOutlook.Quit

Set appOutlook = Nothing

End Sub

Para probar el ejemplo debes remplazar la palabra nombre(x) por nombres que figuren en la lista de contactos de Outlook.

De este procedimiento cabe destacar el método CreateItem (olMailItem)   que crea un nuevo elemento (mensaje de correo electrónico) de Microsoft Outlook.


El resto de propiedades y métodos no nos extenderemos más ya que los hemos utilizado en otras ocasiones…

Tablas dinámicas

Las tablas dinámicas son muy útiles para estructurar datos que se presentan en bruto, resumirlos y crear informes. Normalmente, una tabla de datos de Excel no puede cambiar de estructura. Podemos ordenar los datos, filtrarlos, añadir subtotales, pero la estructura en filas y columnas es inalterable. Uno de los objetivos de las tablas dinámicas es la posibilidad de alterar esa estructura y a la vez resumir datos.

El siguiente procedimiento establece las bases principales de utilización de VBA para crear una tabla dinámica. El código que utilizamos puede resultar un poco confuso.

A continuación explico brevemente algunos objetos relevantes:

PivotCaches:  Representa todas la memoria caché de tabla dinámica del libro especificado.

PivotTable:  Colección de todos los objetos de la hoja de cálculo especificada.

PivotFields: Representa un solo campo de tabla dinámica.

DisplayFieldCaptions: Controla si se muestran o no los botones de filtro y los títulos PivotField para filas y columnas en la cuadrícula.

Sub PivotTable()

Dim PTCache As PivotCache

Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Create _

(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)

Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _

TableDestination:=Range("F1"), TableName:="Ventas")

With PT

    .PivotFields("Zona").Orientation = xlPageField

    .PivotFields("Mes").Orientation = xlColumnField

    .PivotFields("Nombre").Orientation = xlRowField

    .PivotFields("Ventas").Orientation = xlDataField

    .DisplayFieldCaptions = False

End With

End Sub

La última sección del código se añade los campos a la tabla dinámica y se especifica su localización dentro de ella.

El código también podría ser más general si se utiliza índices en lugar de cadenas literales para las colecciones PivotFields. De esta forma, si el usuario cambia los encabezados de las columnas, el código seguirá funcionando.

Barra de Progreso

Una de las necesidades más comunes para un programador de Excel es indicar visualmente al usuario el progreso de la ejecución de una tarea y para ello utilizan la barra de progreso.

La barra de progreso es el elemento (normalmente representado por un formulario) que nos permiten mostrar de forma gráfica (parecido a un termómetro) el estado del avance de una tarea o proceso. Generalmente el gráfico está acompañado de información en forma de texto para medir con exactitud el avance.

Un indicador de progreso ralentiza un poco la ejecución de la macro, por eso si la rapidez de la macro es crucial en nuestro libro será mejor no utilizarla.

El siguiente procedimiento genera  mediante bucles una serie de números aleatorios, según se ejecutan realizan una serie de cambios en el formulario que hemos creado con anterioridad y que nos quedaría de la siguiente forma.

El  formulario Progreso contiene los siguientes elementos:

• Control de marco (Frame1)
• Control de etiqueta (Lavel1)  que simula la barra de progreso.
• Control de etiqueta (Lavel2) opcional

En un modulo copiamos los siguientes procedimientos:

Sub GeneradorNumeros()

Const RowMax As Integer = 500

Const ColMax As Integer = 40

Dim Cuenta As Integer

Dim r As Integer, c As Integer

Dim Pct As Single

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    Cells.Clear

    For r = 1 To RowMax

        For c = 1 To ColMax

            Cells(r, c) = Int(Rnd * 49)

            Cuenta = Cuenta + 1

        Next c

        Pct = Cuenta / (RowMax * ColMax)

        Call Progreso(Pct)

    Next r

Unload UserForm1

End Sub

Sub Progreso(n)

With UserForm1

    .Frame1.Caption = Format(n, "0%")

    .Label1.Width = n * (.Frame1.Width - 10)

    .Repaint

End With

End Sub

Sub ShowDialog()

UserForm1.Label1.Width = 0

UserForm1.Show

End Sub

Insertamos el siguiente procedimiento en la ventana de código del UserForm1, este procedimiento solo invoca al generador de números y nos colorea la etiqueta (Lavel1) con el color del tema de nuestro libro.

Como os podéis dar cuenta este procedimiento se escribe en el evento Activate del formulario que se desencadena cuando se muestra el formulario.  

Private Sub UserForm_Activate()

UserForm1.Label1.BackColor = ActiveWorkbook.Theme. _

ThemeColorScheme.Colors(msoThemeAccent1)

Call GeneradorNumeros

End Sub

Para iniciar el proceso ejecutamos la macro ShowDialog desde el cuadro de dialogo de Macro en la ficha programador.

Cuadros de dialogo Segunda Parte

Enlace de la primera parte.

Método GetOpenFilename:

Método GetOpenFilename es un objeto de Application, que obtiene un nombre de archivo correcto a través de un cuadro de dialogo. Este método muestra el habitual cuadro de dialogo de Abrir, pero no abre realmente el archivo especificado, el método devuelve la cadena que contiene la ruta y el nombre del archivo seleccionado por nosotros.

La sintaxis para este método es la siguiente: (todo es opcional)

GetOpenFilename ( FileFilter , FilterIndex , Título , ButtonText , MultiSelect )

En este ejemplo se muestra el cuadro de diálogo con el filtro de archivos definido como archivos de Word.

Sub SelectArchivo()

Dim RutaArchivo As String

    MsgBox ("Seleccione el archivo de Word que desee.")

    RutaArchivo = Application.GetOpenFilename _

    ("Documento de texto  (*.docx), *.docx")

On Error GoTo Control:

    MsgBox ("El archivo seleccionado es:  " & RutaArchivo)

Control:

End Sub

Método GetSaveAsFilename:

El método GetSaveAsFilename es muy similar al GetOpenFilename muestra el cuadro de diálogo estándar de Guardar como y obtiene el nombre de archivo seleccionado por nosotros  sin guardar realmente ningún archivo.

La sintaxis para este método es la siguiente: (todo es opcional)

GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Como diferencia al anterior metodo la opción InitialFilename nos sugiere el nombre de archivo, si este argumento se omite, Microsoft Excel usará el nombre del libro activo.

Sub SaveArchivo()

Dim RutaArchivo As String

    RutaArchivo = Application.GetSaveAsFilename _

    (FileFilter:="Documento de Excel  (*.xlm), *.xlm")

On Error GoTo Control:

    ActiveWorkbook.SaveAs (RutaArchivo)

    MsgBox "El archivo :  " & RutaArchivo & _

    vbCrLf & " se ha guardado correctamente.", vbInformation

Control:

End Sub

Crear un menú contextual

Con Excel VBA podemos crear nuestro propio menú contextual y hacer que aparezcan como respuesta a un evento determinado. El código que presento a continuación  crea un sencillo menú con tres  elementos.

Sub CrearMenu()

    Set myBar = CommandBars.Add _

      (Name:="MyMenu", Position:=msoBarPopup, _

       Temporary:=True)

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Formato Numero..."

        .OnAction = "ShowFormatNumber"

        .FaceId = 1554

    End With

       

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Alineacion..."

        .OnAction = "ShowFormatAlignment"

        .FaceId = 217

    End With

    Set myItem = myBar.Controls.Add(Type:=msoControlButton)

    With myItem

        .Caption = "&Fuente..."

        .OnAction = "ShowFormatFont"

        .FaceId = 291

    End With

End Sub

CommandBars  representa la barra de menús y todas las barras de herramientas de Microsoft Office.

La propiedad OnAction Devuelve o establece el nombre de un procedimiento de Visual Basic que se ejecutará cuando hacemos clic al valor de un control de nuestro menú.

La función FaceId  establece el número de Id. de la imagen de un control de nuestro menú.

Después de crear nuestro menú contextual, podemos mostrarlo mediante el método ShowPopup.

El siguiente procedimiento localizado en el modulo de código de un objeto Worksheet, se ejecuta cuando hacemos clic con el botón derecho en una celda.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

CommandBars("MyMenu").ShowPopup

End Sub

Nos quedaría como la siguiente imagen: