Creating a randomized footer for Outlook templates

Posted on 2006-05-04
Last Modified: 2012-05-05
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?

Question by:butterhook
    LVL 76

    Expert Comment

    by:David Lee
    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.

    LVL 1

    Author Comment

    Thank you
    LVL 104

    Expert Comment

    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.

    LVL 76

    Expert Comment

    by:David Lee

    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
    LVL 1

    Author Comment

    Well that is very exciting. Thank you - I will try this in the near future.
    LVL 1

    Author Comment

    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.
    LVL 76

    Accepted Solution

    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)

    LVL 1

    Author Comment

    BDF, obviously your previous answer was the meat of the solution, the later one was the icing on the cake. Thanks.
    LVL 76

    Expert Comment

    by:David Lee
    You're welcome.  Glad I could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Outlook can send email in three different formats: HTML, Rich Text, and Plain Text. Rich Text format is problematic though as it automatically wraps all formatting into an attachment called Winmail.dat. It also tends to pack actual file attachments …
    Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now