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

Blanchetdb

Board Regular
Joined
Jul 31, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
.
You are welcome.

I wish everything about Excel / VBA were that simple. :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top