Help with combobox VBA code outlook

[mostafa_e90]

Hi all,

Im trying to write a vba code with a userform combobox. Basically, I want an inputbox to popup with a predefined droplist. And depending on which option the user chooses from the list, I want to send an email to a specific recipient. However, when I run my code now, the combobox appears with the dropdown list, but when I choose an option from the list nothing happens. I would really appreciate if someone can point out what's wrong with my code. Thx in advance!


Public Sub CommandButton1_Click()
lstNum = ComboBox1.ListIndex
Unload Me
End Sub

Public Sub UserForm_Initialize()



With ComboBox1
.AddItem "Option 1"
.AddItem "Option 2"
.AddItem "Option 3"
End With

End Sub

Public Sub mostafa_code()


Dim objMsg As MailItem
Set objMsg = Application.CreateItem(olMailItem)


Select Case lstNum

Case 0
With objMsg
.To = "user1 email address"

.Subject = "This is the subject 1"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing
End Select

Case 1
With objMsg
.To = "user2 email address"

.Subject = "This is the subject 2"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing

Case 2
With objMsg
.To = "user3 email address"

.Subject = "This is the subject 3"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing

End Select
End Sub

[gmayor]

Essentially what is wrong with your code is that the command button sets a variable (undeclared) lstNum and then unloads the form - job over.
You need to call your macro before unloading the form. Had your main macro run, then it would have fallen foul of the extra end select command.
The following should work.
This code goes in the userform

Option Explicit

Private Sub CommandButton1_Click()
Dim lstNum As Integer
lstNum = ComboBox1.ListIndex
If lstNum > -1 Then
Me.Hide
Else
MsgBox "Select a value"
Me.ComboBox1.SetFocus
End If
End Sub

Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "Option 1"
.AddItem "Option 2"
.AddItem "Option 3"
.MatchRequired = True
.ListIndex = -1
End With
End Sub


This code goes in an ordinary module. Run this macro to complete the process.

Option Explicit

Sub mostafa_code()
Dim objMsg As MailItem
Dim ofrm As New UserForm1
Dim lstNum As Integer
With ofrm
.Show
lstNum = .ComboBox1.ListIndex
End With
Unload ofrm
Set objMsg = Application.CreateItem(olMailItem)
Select Case lstNum
Case 0
With objMsg
.To = "user1 email address"

.Subject = "This is the subject 1"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing

Case 1
With objMsg
.To = "user2 email address"

.Subject = "This is the subject 2"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing

Case 2
With objMsg
.To = "user3 email address"

.Subject = "This is the subject 3"
.Categories = "Test"
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Set objMsg = Nothing
End Select
Set ofrm = Nothing
End Sub

[mostafa_e90]

Much appreciated, thank you!

[gmayor]

Given that it is a very quiet Sunday morning, I thought it might be useful to point out that personally I would have used a multi-column combobox and filled it with the values you have inserted in your case statements, and called the message creation only once. e.g. replace the main macro with the following.
Sub mostafa_code()
Dim objMsg As MailItem
Dim ofrm As New UserForm1
Dim lstNum As Integer
With ofrm
With .ComboBox1
.ColumnCount = 4
.ColumnWidths = .Width - 4 & ",0,0,0"
.AddItem "[Select an Option]"

.AddItem
.List(.ListCount - 1, 0) = "Option 1"
.List(.ListCount - 1, 1) = "someone@somewhere.com"
.List(.ListCount - 1, 2) = "This is Subject 1"
.List(.ListCount - 1, 3) = "Test"

.AddItem
.List(.ListCount - 1, 0) = "Option 2"
.List(.ListCount - 1, 1) = "someoneelse@somewhere.com"
.List(.ListCount - 1, 2) = "This is Subject 2"
.List(.ListCount - 1, 3) = "Test"

.AddItem
.List(.ListCount - 1, 0) = "Option 3"
.List(.ListCount - 1, 1) = "anothersomeone@somewhere.com"
.List(.ListCount - 1, 2) = "This is Subject 3"
.List(.ListCount - 1, 3) = "Test"

.MatchRequired = True
.ListIndex = 0
End With
.Show
lstNum = .ComboBox1.ListIndex
End With
Set objMsg = Application.CreateItem(olMailItem)
With objMsg
.To = ofrm.ComboBox1.Column(1)
.Subject = ofrm.ComboBox1.Column(2)
.Categories = ofrm.ComboBox1.Column(3)
.BodyFormat = olFormatPlain ' send plain text message
.Display
End With
Unload ofrm
Set objMsg = Nothing
Set ofrm = Nothing
End Sub
Note that this populates the userform and the only code you then need in the form is that for the button(s) e.g. as follows. The check is moved to avoid listitem 0 which is the prompt text.
Option Explicit

Private Sub CommandButton1_Click()
Dim lstNum As Integer
lstNum = ComboBox1.ListIndex
If lstNum > 0 Then
Me.Hide
Else
MsgBox "Select an Option"
Me.ComboBox1.SetFocus
End If
End Sub