Automating Outlook Using Custom Tags in Email Templates

brncao

Board Regular
Joined
Apr 28, 2015
Messages
147
I'm trying to create a userform dashboard in Outlook that utilizes several templates (.oft) with custom tags in them. For many of us, our emails are quite redundant: open a template or recycle a previous email, change some stuff, and send.

What I'm hoping to achieve is eliminate as much redundant work as possible. Using custom tags inside these templates could potentially save time.

The tags look something like this: {arg1, arg2, arg3,...}. For example, when you have this in your template, {Date, mm/dd/yyyy, 1}, it tells the macro to get today's date, add 1, and formatted to mm/dd/yyyy. "{" signals that this may be a tag.

I'm trying to write code to find and parse these tags. I don't know how to replace these tags with the appropriate information. I'm only familiar with Excel VBA.

Here is the code so far (make sure Microsoft Word 16.0 Object Library is checked in References):
Run the Example macro.
VBA Code:
Option Explicit
Public Const TEMPLATE_FOLDER As String = "C:\Wherever your templates are saved\"
Sub Example()
    Dim objMail As MailItem
    Set objMail = CreateItemFromTemplate(TEMPLATE_FOLDER & "Example.oft")
    ParseTag objMail
    objMail.Display
End Sub

VBA Code:
Public Function IsArrayAllocated(Arr As Variant) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayAllocated
' Returns TRUE if the array is allocated (either a static array or a dynamic array that has been
' sized with Redim) or FALSE if the array is not allocated (a dynamic that has not yet
' been sized with Redim, or a dynamic array that has been Erased). Static arrays are always
' allocated.
'
' The VBA IsArray function indicates whether a variable is an array, but it does not
' distinguish between allocated and unallocated arrays. It will return TRUE for both
' allocated and unallocated arrays. This function tests whether the array has actually
' been allocated.
'
' This function is just the reverse of IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
On Error Resume Next

' if Arr is not an array, return FALSE and get out.
If IsArray(Arr) = False Then
    IsArrayAllocated = False
    Exit Function
End If

' Attempt to get the UBound of the array. If the array has not been allocated,
' an error will occur. Test Err.Number to see if an error occurred.
N = UBound(Arr, 1)
If (Err.Number = 0) Then
    ''''''''''''''''''''''''''''''''''''''
    ' Under some circumstances, if an array
    ' is not allocated, Err.Number will be
    ' 0. To acccomodate this case, we test
    ' whether LBound <= Ubound. If this
    ' is True, the array is allocated. Otherwise,
    ' the array is not allocated.
    '''''''''''''''''''''''''''''''''''''''
    If LBound(Arr) <= UBound(Arr) Then
        ' no error. array has been allocated.
        IsArrayAllocated = True
    Else
        IsArrayAllocated = False
    End If
Else
    ' error. unallocated array
    IsArrayAllocated = False
End If

End Function

VBA Code:
Option Explicit
Option Private Module
Public Const O_BRACKET As String = "{"
Public Const C_BRACKET As String = "}"

Sub ParseTag(ByRef msg As Outlook.MailItem)
    Dim tags As New Collection
    Dim sTag As String, arg() As String
    Dim arrTags() As Variant
    Dim element As Variant, instance As Variant
    Dim wrdDoc As Word.Document
    Dim wrdSel As Word.Selection
    Dim i As Long: i = 0
   
    If msg.GetInspector.EditorType = olEditorWord Then
        Set wrdDoc = msg.GetInspector.WordEditor
        arrTags = GetOpenBracket(wrdDoc)
       
        If IsArrayAllocated(arrTags) Then
            For Each element In arrTags
                'Code here: Replace open bracket with seq numbers
                i = i + 1
            Next element
        End If
    End If
End Sub

Function GetOpenBracket(ByRef wrdDoc As Word.Document) As Variant
    Dim i As Long
    Dim arrSelection() As Variant
    i = 0
   
    With wrdDoc.Windows(1).Selection
        With .Find
            .ClearFormatting
            .Text = O_BRACKET
            .Forward = True
            .Wrap = wdFindStop
            .Format = False
            .Execute
        End With
           
        Do While .Find.Found
            .Select
            .Collapse wdCollapseEnd
            .Find.Execute
            ReDim Preserve arrSelection(i)
            Set arrSelection(i) = Selection.Range
            i = i + 1
        Loop
    End With
   
    GetOpenBracket = arrSelection
End Function
I'm storing the Selection.Range into an array as it loops through looking for "{". The code then retrieves the "location" of these open brackets and the goal is to replace them with a number in sequence, just so I can see that it's working. If you guys know of a better way, then I'm all ears.

I will probably ask about handling nested tags, missing close brackets, and escape characters at a later time. It's akin to the way Excel handles "(" and ")" inside formulas.

Thank you,
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top