Forum Discussion
Value from Cell to Autofill Email
That would be
emailItem.Body = "Hello," & vbNewLine & vbNewLine & "Please be advised quote number: " & Range("D4").Value & " has been accepted in our system"
Note that I added spaces in the strings before and after the value of D4.
If the name Lyle (or whatever) is in E4, it could be
emailItem.Body = "Hello " & Range("E4").Value & "," & vbNewLine & vbNewLine & "Please be advised quote number: " & Range("D4").Value & " has been accepted in our system"
Is it possible to use a worksheet event (such as entering a date into a cell) to then use a dynamic reference (rather than an absolute "D4" ref) to send an email unique to a particular table row?
- HansVogelaarJun 15, 2022MVP
Let's say you have dates in B4:B50, and you enter a date in B2.
The code could then search for that date in B4:B50 and look up the value in column D in the row where the date is found.
- Edg38426Jun 15, 2022Brass ContributorWhat I would like is for when a date is entered into, for example, cell B10 ("work order completed"), to then send an auto email which includes the data from cell D10 ("work order number"). And then any time a date is entered into column B, the email would contain the corresponding information from column D in that same row. Would this do that?
- HansVogelaarJun 15, 2022MVP
Code could look like this. It will work best if Outlook is already running.
Private Sub Worksheet_Change(ByVal Target As Range) Dim objOL As Object Dim objMsg As Object ' Get out if multiple cells have been changed If Target.CountLarge > 1 Then Exit Sub ' Get out if the changed cell is in row 2 or not in column B If Target.Row = 1 Or Target.Column <> 2 Then Exit Sub ' Get out if the changed cell does not contain a date If Not IsDate(Target.Value) Then Exit Sub ' Get out if the cell in column D in the same row is empty If Target.Offset(0, 2).Value = "" Then Exit Sub ' Start Outlook Set objOL = CreateObject("Outlook.Application") ' Create a new message Set objMsg = objOL.CreateItem(0) ' olMailItem ' Recipient objMsg.To = "an email address" ' Subject objMsg.Subject = "This is the subject" ' Body text objMsg.Body = "Work order number " & Target.Offset(0, 2).Value & _ " was completed on " & Format(Target.Value, "mm/dd/yyy") ' Use only ONE of the following two lines objMsg.Display ' show the message so that the user can inspect it objMsg.Send ' send the message End Sub