Userform in Outlook

gmayor · #2 03-12-2016, 10:27 PM

There is nothing particularly complex about your request, though you have not indicated where the data is to come from. Note a combobox is intended to display a single item from the contents. If you want to show more than one item, use a list box.

An Excel worksheet is probably the best place to hold the data, but if there are only a few counties to list, you can store them in the macro code.

Although aimed at Word, the principles at http://www.gmayor.com/Userform_ComboBox.html are the same for Outlook.

Writing directly to the body of an e-mail requires a knowledge of Word ranges, but a simple workaround is to create a message template that contains your message text with a couple of tags for the name and case number items from the form i.e. <Name> and <Case> which can be repaced from the form data. You can use the following as a basis for the code to create the message, which uses the xlfilllist function from the linked web page to fill the userbox from a two column Excel sheet which has the counties and the email addresses, with a header row.

Code:

Option Explicit

Sub CreateMessageFromTemplate()
Dim olItem As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim oFrm As New UserForm1
Dim strName As String, strCase As String
Dim strTo As String
Const strWorkbook As String = "C:\Path\workbookname.xlsx"
Const strSheet As String = "Sheet1"
    With oFrm
        xlFillList .ComboBox1, 1, strWorkbook, strSheet, True, True, "[Select County]"
        .Show
        If .Tag = 0 Then GoTo lbl_Exit
        strTo = .ComboBox1.Column(1)
        strName = .TextBox1.Text
        strCase = .TextBox2.Text
    End With
    Unload oFrm

    Set olItem = Application.CreateItemFromTemplate("C:\Path\Message.oft")
    With olItem
        .To = strTo
        .Subject = "The message subject"
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="<Name>")
                oRng.Text = strName
                oRng.collapse 0
            Loop
        End With
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="<Case>")
                oRng.Text = strCase
                oRng.collapse 0
            Loop
        End With
        .Display 'This line is required
        '.Send 'Restore this line after testing
    End With
lbl_Exit:
    Set olItem = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

The userform code is simply as follows with CommandButton1 being the Continue button and CommandButton2 the Cancel button. I have used default names. You should use more meaningful names for the variopus elements.

Code:

Option Explicit

Private Sub CommandButton1_Click()
    Me.Hide
    Me.Tag = 1
lbl_Exit:
    Exit Sub
End Sub

Private Sub CommandButton2_Click()
    Me.Hide
    Me.Tag = 0
lbl_Exit:
    Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
Outlook and userform	Vibov	Excel Programming	2	03-19-2015 04:06 AM
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM
How to get Outlook 2007 userform into template?	Royzer	Outlook	0	04-13-2012 10:41 AM
Outlook userform validation help	aiwnjoo	Outlook	0	12-08-2010 12:57 AM