Solved: E-Mail from Address found in Worksheet

[craigos]

Hi All,

I have a form which needs to be sent to a recipient and I have code to send a workbook based on case select and a recipient e-mail address that is coded in vba. For this I need to code every address myself.

What I am looking for is to have vba find the e-mail address on sheet 2 based on a Friendly Name selected in a ComboBox on sheet 1.

The reason for this is to allow users to add e-mail addresses which are pertinent to their area - the form is the same but the person receiving it can be anywhere in the country...I don't want to maintain a central form which needs constantly updating, just allow users to modify for their region.

Example attached of current workbook.

Work: XP / 2003
Home: 7 and 2007

Any pointers will be gratefully received

Many Thanks

Craig

[BrianMH]

Try this

Private Sub CommandButton4_Click()
Dim sRecipient
Dim sLookup As String
Application.ScreenUpdating = False

Unload UserForm1
a = MsgBox("Send", vbOKCancel + vbQuestion, "Form")
If a = vbCancel Then Exit Sub

With ActiveWorkbook
sLookup = ActiveWorkbook.Sheets("Sheet1").OLEObjects("ComboBox1").Object.Value
sRecipient = Application.WorksheetFunction.VLookup(sLookup, .Sheets("Sheet2").Range("A:B"), 2, False)
.SendMail Recipients:=Recipient, Subject:="Form " & Format(Date, "dd mmm yyyy")
End With

Msg = "Form sent" _
& vbNewLine & "" _
& vbNewLine & "This form will now clear all data" _
& vbNewLine & "and close without saving changes " _
& vbNewLine & "" _
& vbNewLine & "Remember to delete from your Sent Items " _

Ans = MsgBox(Msg, vbExclamation + vbOKOnly, "Notice")


Application.ScreenUpdating = True

ActiveWorkbook.Close SaveChanges:=True
End Sub

[Bob Phillips]

Have you just tried changing

ActiveWorkbook.SendMail Recipients:="someone@somewhere.com", Subject:="Form " & Format(Date, "dd mmm yyyy")

to

ActiveWorkbook.SendMail Recipients:=recp, Subject:="Form " & Format(Date, "dd mmm yyyy")

[craigos]

Thanks BrianMH......got it sorted.