Consulting

Results 1 to 4 of 4

Thread: VBA script - List box of OFT templates in a file folder

  1. #1
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    2
    Location

    VBA script - List box of OFT templates in a file folder

    Looking to run a script that produces a list box of all the templates in a file folder (must be dynamic). Then user can choose template and select OK, and template opens as new email.

    Any ideas on where to start?
    THANKYOU!

  2. #2
    That sounds simple enough.
    Create a userform in the Outlook VBA editor (see https://www.gmayor.com/Userform.htm)
    Add a list box and two command buttons.
    In the userform code add the following. Change the path to the folder where the templates are stored. - This will usually be Word's user templates folder. To access the default location, if you have not changed it, enter or copy%appdata%\Microsoft\Templates
    to the address window of Windows Explorer and you will be taken to the folder. Note that this folder is normally hidden.

    Option Explicit
    'Graham Mayor - https://www.gmayor.com - Last updated - 16 Feb 2021 
    Const strPath As String = "C:\Path\" 'the folder with the templates
    Private strFile As String
    
    
    Private Sub CommandButton1_Click()
    Dim strTemplate As String
    Dim olItem As Object
        strTemplate = strPath & ListBox1.Text
        Set olItem = CreateItemFromTemplate(strTemplate)
        Hide
        olItem.Display
        Unload Me
    End Sub
    
    
    Private Sub CommandButton2_Click()
        Unload Me
    End Sub
    
    
    Private Sub ListBox1_Click()
    Dim i As Integer
        With ListBox1
            If .ListCount > 0 Then
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then
                        CommandButton1.Enabled = True
                        Exit For
                    End If
                Next i
            End If
        End With
    End Sub
    
    
    Private Sub UserForm_Initialize()
        With Me
            .Caption = "Create Message from Template"
            .CommandButton1.Enabled = False
            .CommandButton1.Caption = "Create Message"
            .CommandButton2.Caption = "Cancel"
            strFile = Dir$(strPath & "*.oft")
            While strFile <> ""
                .ListBox1.AddItem strFile
                strFile = Dir$()
            Wend
        End With
    End Sub
    Create a macro to call the userform e.g.
    Sub CreateMessagefromTemplate()    UserForm1.Show
    End Sub
    and add a button to the ribbon or QAT (Quick Access Toolbar) to call that macro.
    The code uses the default names for the userform and the various controls. You can change these as required.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Feb 2021
    Posts
    2
    Location
    WOW, this is SO great! Thankyou!
    I did note, I think you missed an ENTER in your last two lines of code :

    Sub CreateMessagefromTemplate()    UserForm1.Show
    End Sub
    didn't work, so I tried:

    Sub CreateMessagefromTemplate()    
    UserForm1.Show
    End Sub
    and that worked great! One last question (I'm being greedy), any chance to add to the code that rather than having to select the template and then clicking the open button, you could also just double click the template for it to open in a new email? Is that very difficult to add that functionality? Either way, thanks again! This is really awesome.

  4. #4
    Sorry about the missing line break. This sometimes happens when pasting code into this forum and I didn't spot it . I did however spot and fix it in the following

    As for the double click, add the following to the userform code

    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
        With ListBox1
            If .ListCount > 0 Then
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then
                        CommandButton1_Click
                        Exit For
                    End If
                Next i
            End If
        End With
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •