send email based on cell content, email address varies per submission

[Blanchetdb]

I presently have the following coding:

Sub Mail_emsg7()
Dim iName As String
Dim PRI As String
Dim StaffType As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)


iName = Worksheets("Sheet1").Cells(10, "E").Value
StaffType = Worksheets("Sheet1").Cells(36, "C").Value
PRI = Worksheets("Sheet1").Cells(10, "I").Value


xMailBody = "Hi," & vbNewLine & vbNewLine & _
"A Staffing request has been submitted for the following employee: " & vbNewLine & vbNewLine & _
"Employee name: " & iName & vbNewLine & _
"PRI (if applicable): " & PRI & vbNewLine & _
"Staffing Type: " & StaffType & vbNewLine & vbNewLine & _
"If this employee is either new to the Agency or new to your unit, please refer to the OnBoarding Checklist" & vbNewLine & _
"(see link below)" & vbNewLine & vbNewLine & _
"http://merlin.cfia-acia.inspection....ation-program/eng/1490881312251/1490881312252"
On Error Resume Next
With xOutMail
.To = "John.Smith@canada.ca"
.CC = ""
.BCC = ""
.Subject = "OnBoarding of a New Employee"
.Body = xMailBody
.Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub


Presently, the email is sent to John Smith every time. I would like to change this so that the email is sent to an email inserted by the client in cell D24

not sure how to change the coding in order to do so

 

[Logit]

.

.To = Sheets("Sheet1").Range("D24").Value

 

[Blanchetdb]

worked great.....thanks

didn't realize that it was that simple

 

[Logit]

.
You are welcome.

I wish everything about Excel / VBA were that simple.