Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, February 14, 2023
  0 Replies
  3.1K Visits
I'm new to VBA. I'm looking to send an email when a cell K has "Yes" entered.... I've got this far but struggling with the next step.

I'd like the email body to read as below:

Hi, project proposal summary below.

Project name: *Info from cell A*
Description: *cell B*
Solution: *cell C*
Benefits: *Cell D*
Cost: *Cell F*
Time: Cell *G*
Risk: Cell *H*
Customer(s): Cell I
Brand(s): Cell J

Kind Regards,

*Cell L*


Below is the code I have used so far:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If (Not Intersect(Target, Range("K:K")) Is Nothing) And (Target.Value = "Yes") Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "email address"
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Thank you, in advance, to anyone who can help me.
There are no replies made for this post yet.