Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

E-mail Excel worksheet based upon selection of combo box

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MWerblan@gmail.com - 18 Mar 2008 23:20 GMT
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 "asdf@gmail.com", "DP Routing Create/
Update/Change Form - Monroe", yes
   ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 3 Then
       .SendMail "asdf@gmail.com", "DP Routing Create/
Update/Change Form - Auburn", yes
   ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 4 Then
       .SendMail "asdf@gmail.com", "DP Routing Create/
Update/Change Form - Jeannette", yes
   ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 5 Then
       .SendMail "adf@gmail.com", "DP Routing Create/
Update/Change Form - Dupo", yes
   ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 6 Then
       .SendMail "asdf@gmail.com", "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 "a...@gmail.com", "Create/Update/Change Form - Plant
1", yes
      ElseIf ActiveSheet.Shapes("dd_plant") = 3 Then
      .SendMail "a...@gmail.com", "Create/Update/Change Form - Plant
2", yes
      End If

Try 2:

If ActiveSheet.Shapes("dd_plant").ListIndex = 2 Then
      .SendMail "asdf@gmail.com", "Create/Update/Change
Form - Plant 1", yes
      ElseIf ActiveSheet.Shapes("dd_plant").ListIndex = 3 Then
      .SendMail "asdf@gmail.com", "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?
Ron de Bruin - 18 Mar 2008 23:46 GMT
You can try this

Dim MailStr As String

   Select Case ActiveSheet.Shapes("dd_plant").ListIndex
   Case 2: MailStr = "aa@ron.nl"
   Case 3: MailStr = "bb@ron.nl"
   Case 4: MailStr = "cc@ron.nl"
   Case 5: MailStr = "dd@ron.nl"
   End Select

Then use MailStr in the SendMail code line

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> I'm using the form toolbar combo-box with a pre-defined list that is
> on a hidden sheet.
[quoted text clipped - 58 lines]
> Is it possible to set a selection based upon the combo-box who the e-
> mail goes to?

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.