E-mail Excel worksheet based upon selection of combo box

[MWerblan]

I'm using the form toolbar combo-box with a pre-defined list that is
on a hidden sheet.

The list starts at 1, not 0.

The goal that I am trying to reach is if a particular plant is
selected in the combo-box (plant 1, plant 2, plant 3), when the e-mail
button is clicked, it will send it to the person under that plant.

I tried several different options including:

Try 1:
If ActiveSheet.Shapes("dd_plant").ListIndex = 2 Then
.SendMail "(e-mail address removed)", "DP Routing Create/
Update/Change Form - Monroe", yes
ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 3 Then
.SendMail "(e-mail address removed)", "DP Routing Create/
Update/Change Form - Auburn", yes
ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 4 Then
.SendMail "(e-mail address removed)", "DP Routing Create/
Update/Change Form - Jeannette", yes
ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 5 Then
.SendMail "(e-mail address removed)", "DP Routing Create/
Update/Change Form - Dupo", yes
ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 6 Then
.SendMail "(e-mail address removed)", "DP Routing Create/
Update/Change Form - Columbus", yes
Else
On Error GoTo 0
.Close SaveChanges:=False
End If


Try 2:

If ActiveSheet.Shapes("dd_plant") = 2 Then
.SendMail "(e-mail address removed)", "Create/Update/Change Form - Plant
1", yes
ElseIf ActiveSheet.Shapes("dd_plant") = 3 Then
.SendMail "(e-mail address removed)", "Create/Update/Change Form - Plant
2", yes
End If

Try 2:

If ActiveSheet.Shapes("dd_plant").ListIndex = 2 Then
.SendMail "(e-mail address removed)", "Create/Update/Change
Form - Plant 1", yes
ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 3 Then
.SendMail "melissa.werblan", "Create/Update/Change Form - Plant
2", yes
End If



I have used the following code for resetting the list to the first
field of the selected, which is blank:

ActiveSheet.Shapes("dd_plant").Select
With Selection
.ListIndex = 1
End With

Is it possible to set a selection based upon the combo-box who the e-
mail goes to?

 

[Douglas J. Steele]

You might be better off asking this in a newsgroup related to Excel.

This newsgroup is for questions about Access, the database product that's
part of Office Professional. Forms in Access are very different than forms
in Excel.