• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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?

  • 4
  • 4
1 Solution
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
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
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)

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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now