Forum Discussion

Elcten's avatar
Elcten
Copper Contributor
Mar 14, 2022

Value from Cell to Autofill Email

Hi, I'm going to start by saying I'm a total noob on excel so apologies if this is a dumb question. Basically I'm trying to make a tool where you fill in a form, click a button and the data from...
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Mar 14, 2022

Elcten 

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"

Edg38426's avatar
Edg38426
Brass Contributor
Jun 15, 2022

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?

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 15, 2022

    Edg38426 

    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.

    • Edg38426's avatar
      Edg38426
      Brass Contributor
      Jun 15, 2022
      What 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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jun 15, 2022

        Edg38426 

        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

Resources