Creating a randomized footer for Outlook templates

Hi Everyone,

I have created an email template in HTML which contains images, a table and some style sheets. I have then opened this in outlook and saved it as an Outlook template. This is fine and works very nicely.

For sales purposes, I would lilke to randomise/pseudo-randomise the footer (text) at the bottom of the email to something like the following options:

'Did you know that we offer a zebra painting service'
'The best in the industry'
'Quick job turnaround'
'Optimised fantastic experience'
'The best bacon sandwiches ever - Dean Gaffney'

Is there any way to create a little app that would add a random footer to a specified table cell in the email, selecting the items from a database table?

Who is Participating?
David LeeCommented:
In your template insert some replaceable text where the quote needs to go.  Use the REPLACE command to replace that text with your quote.  Assuming the replaceable text is *MYQUOTE* it be something like this:

    Replace(olkInspector.CurrentItem.HTMLBody, "*MYQUOTE*", strFooter)

David LeeCommented:
Hi butterhook,

> Is there any way to create a little app that would add a random footer
> to a specified table cell in the email, selecting the items from a
> database table?

Yes, there are several potential ways of doing this.  Let me test a couple of things to see which way is simplest and I'll get back to you.

butterhookAuthor Commented:
Thank you
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

My first though is to use one of the quote generators. There are a number of those around that will put a random quote in to the email message. All you would have to do is remove the supplied quotes and include your own.

David LeeCommented:

Here's the code for doing this.  This doesn't read from a database, although I can convert it to do so if that's essential.  The approach I took here is to read from an array.  You can add more items to the array easily.  Follow these instructions to use this.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
4.  Copy the code below and paste it into the right-hand pane of the VB Editor window
5.  Click the diskette icon on the toolbar to save the changes
6.  Close the VB Editor
7.  Click Tools->Macro->Security
8.  Set the Security Level to Medium.  
9.  Close Outlook
10.  Start Outlook
11.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.
12.  Test the macro by sending a message.

'Code Begins Here
Private WithEvents olkInspectors As Outlook.Inspectors, _
    WithEvents olkInspector As Outlook.Inspector
Private Sub Application_Quit()
    Set olkInspector = Nothing
    Set olkInspectors = Nothing
End Sub

Private Sub Application_Startup()
    Set olkInspectors = Application.Inspectors
End Sub

Private Sub olkInspector_Activate()
    Dim strFooter As String
    strFooter = GetNewFooter()
    If olkInspector.CurrentItem.Class = olMail Then
        If olkInspector.CurrentItem.Subject = "" Then
            If olkInspector.CurrentItem.BodyFormat = olFormatHTML Then
                olkInspector.CurrentItem.HTMLBody = Replace(olkInspector.CurrentItem.HTMLBody, "</BODY>", "<p>" & strFooter & "</p>" & vbCrLf & "</BODY>")
                olkInspector.CurrentItem.Body = vbCrLf & vbCrLf & strFooter
            End If
        End If
    End If
    Set olkInspector = Nothing
End Sub

Private Sub olkInspectors_NewInspector(ByVal Inspector As Inspector)
    Set olkInspector = Inspector
End Sub

Function GetNewFooter() As String
    Dim arrFooters As Variant, _
        intMax As Integer, _
        intIndex As Integer
    arrFooters = Array("Did you know that we offer a zebra painting service", _
        "The best in the industry", _
        "Quick job turnaround", _
        "Optimised fantastic experience", _
        "The best bacon sandwiches ever - Dean Gaffney")
    intMax = UBound(arrFooters) + 1
    intIndex = Int((intMax * Rnd) + 1) - 1
    GetNewFooter = arrFooters(intIndex)
End Function
butterhookAuthor Commented:
Well that is very exciting. Thank you - I will try this in the near future.
butterhookAuthor Commented:
This is great - here are the points. But how do I insert the randomised value in a specific table cell in a specific format? I am also comfortable about doing the database bit myself, thank you.
butterhookAuthor Commented:
BDF, obviously your previous answer was the meat of the solution, the later one was the icing on the cake. Thanks.
David LeeCommented:
You're welcome.  Glad I could help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.