Select Email Template from a Userform drop down me

2019-07-30 00:33发布

问题:

I'm trying to create an Outlook Userform, where via a drop down menu an operator can select an email template.

Using this example, this is the code for the Outlook form which works fine.

Private Sub UserForm_Initialize()
    With ComboBox1
        .AddItem "Test"
        .AddItem "Template 2"
        .AddItem "Template 3"
        .AddItem "Template 7"
        .AddItem "Template 5"
        .AddItem "Template 6"
    End With
End Sub

Private Sub btnOK_Click()
    lstNum = ComboBox1.ListIndex
    Unload Me
End Sub

This is the code I've started to put together, to select the template. When I use the drop down menu to select the "Test Template" I receive a error here "Test.Select" highlighting an object is required.

Public lstNum As Long

Public Sub ChooseTemplate()

    Dim oMail As Outlook.MailItem
    Dim oContact As Outlook.ContactItem

    Dim strTemplate As String
    UserForm1.Show

    Select Case lstNum
    Case -1
    '  -1 is what you want to use if nothing is selected
         strTemplate = "Test"
    Case 0
         strTemplate = "template-1"
    Case 1
        strTemplate = "template-2"
    Case 2
         strTemplate = "template-3"
    Case 3
         strTemplate = "template-4"
    Case 4
         strTemplate = "template-5"
    End Select

    Test.Select
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = cell.Value
        .Subject = "Test Facility"
        .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Hi " You recently confirmed you require continued use of the test facility
            "<p>Many thanks and kind regards</p></BODY>" & Signature
        .Sensitivity = 2
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing

    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

    wb.Close savechanges:=True

    End If

    Set oMail = Nothing

End Sub

回答1:

To generate mail from a template see https://msdn.microsoft.com/VBA/Outlook-VBA/articles/application-createitemfromtemplate-method-outlook

Set MyItem = Application.CreateItemFromTemplate("C:\statusrep.oft")

Run this code in Outlook to see how to use the selection.

Public lstNum As Long

Public Sub ChooseTemplate()

    Dim outMail As Outlook.MailItem

    UserForm1.Show

    Select Case lstNum

    ' Following the listbox entries

    Case -1
    '  -1 is what you want to use if nothing is selected
        Set OutMail = CreateItemFromTemplate("Path\to\test.oft")

    Case 0
        Set OutMail = CreateItemFromTemplate("Path\to\test.oft")

    Case 1
        Set OutMail = CreateItemFromTemplate("Path\to\template-2.oft")

    Case 2
        Set OutMail = CreateItemFromTemplate("Path\to\template-3.oft")

    Case 3
        Set OutMail = CreateItemFromTemplate("Path\to\template-7.oft")

    Case 4
        Set OutMail = CreateItemFromTemplate("Path\to\template-5.oft")

    Case 5
        Set OutMail = CreateItemFromTemplate("Path\to\template-6.oft")

    End Select

    ' Use for a specific purpose not randomly
    ' On Error Resume Next

    With OutMail
        .To = "cell.Value"    ' For this Outlook demo

        ' This should be in the template
        ' .Subject = "Test Facility"
        ' .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Hi " You recently confirmed you require continued use of the test facility
        '       "<p>Many thanks and kind regards</p></BODY>" & Signature
        ' .Sensitivity = 2

        .Display
    End With

    ' On Error GoTo 0

    cleanup:
        Set OutMail = Nothing

  End Sub