SendMail to linked Drop Down value?

[ABGirl]

Hi All,

Now I don't know if this is even possible & think it's 2 (or maybe 3!) queries! But here goes!

I have a user form that I want to be sent onto an 'approver' once completed who would be selected from a drop down list. Once the approver is happy they need to click on a submit button to send it onto a further processing mailbox.
I already have the 'Submit' to processing areas bit sorted - but ideally want that button to be hidden until it gets to the 'approver'.
Also I don't really want a list of email addresses in the drop down 'select approver' box, would prefer to have a list of names which then translate to email addresses.

So the 3 things are:

• Can I create a drop down list with actual names (e.g. BOB, DAVE, IAN etc) that then translate to email addresses once selected?
• Can I then have a 'Send to Approver' button which doesnt rely on over 100 if statements to send the mail?
• Can I 'hide' the Submit to processing area button until the sheet reaches the Approver?

Am hoping this makes some sense!

Thanks in advance

http://www.excelforum.com/editpost.php?do=editpost&p=2543046​

 

[Trevor G]

Welcome to the Forum,

Create a list of names in one coulmn and then the email addresses in the next then you can refer to the names and then use an offset to send to the email address.

Does your form indicate an approver, if so then it is a simple case of using a hide method until an approver has been selected.

This sample code will fill a combo box

Private Sub UserForm_Initialize()
Sheets("Sheet1").Activate
Range("B2").Select
Do Until ActiveCell.Value = ""
Me.cboNames.AddItem ActiveCell.Value
ActiveCell.Offset(1, 0).Select

Loop
End Sub

Based on this sample sheet

Excel Workbook
	B	C
2	Names	Email
3	John	john.smith@bbc.co.uk
4	Bill	bill.jones@bbc.co.uk
5	Allan	allan.white@bbc.co.uk
Sheet1

 

[ABGirl]

Fab - thanks for this - currently the 'approver' is in a drop down list on the front page of the form - but there was no link to the email address - now I just need to work out the next bits of the puzzle!

Thanks for the help

 

[Trevor G]

Take a look at this, it uses 2 columns in a combo box, so you will see the name and email address to check to see if works, then you can refer to the second column to send the emails, but hide the column or reduce the size to zero. This code goes behind the form on initialize or activate.

Dim iRow As Long
Dim myCell As Range
With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2
For iRow = 1 To 20 Step 3
Set myCell = Worksheets("sheet1").Cells(iRow, 1)
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
Next iRow
End With