ID .To Address

[princejay22]

I have been working on this for a while now. I am trying to get a code where I do not have to keep editing the .to address to send an email through excel. I have a form on excel I fill out for work. I have inserted a command button on the form, so it can automatically send an email once I complete it. I have a database of emails on another workbook, and they all have IDs. I will like to place the ID on the form to indicate what email address it need to be sent to.

As of now, I always have to go in a change the .to adding the email addresses. I am tired of doing that. Is there any way to use my IDs to locate the email addresses on my database?

 

[JLGWhiz]

You would have to have code that tells VBA to go to the list of IDs in the other workbook, match the one you put on the form, then put the corresponding address in email as: .to = [address], with the address being either a variable representing the address of a reference to the workbook.Sheet.Range.Value where the address resides. I don't know if your form is a group of worksheet cells or a UserForm with textboxes, but assuming that it is a group of cells, and that you add cell G10 as the ID number and your IDs are listed in Workbook2, Sheet1, Column A, with addresses in column B, then the code would look something like this.

Dim fn As Range, wb As Workbook, sh As worksheet
Set wb = Workbooks(2) 'assumes it is already open
Set sh = wb.Sheets(1)
Set fn = sh.Range("A:A").Find(ActiveSheet.Range("G10").Value, , xlValues, xlWhole)
	If Not fn Is Nothing Then
		'Your With statement for email reference here
			.to = fn.Offset(, 1).Value
                'etc.
               'End With
	End If

The location of the If...Then statement lines can be varied depending on how your other code is written.