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, September 20, 2022
  1 Replies
  6.3K Visits
Hi, I'm very new to excel and was wondering if it's possible to set up an excel code in a way that would send an email to a specific person when a value in a column is marked as completed. For example, if JobX is in A2, and in the same row the project managers initials are in that row, B2, this job gets marked completed in C2, when column C gets marked completed, an email should be sent to the PM whose initials are in that row. I found a code that can send an email when a column's cell gets marked as completed, but was wondering if I can be more specific like sending an email to a specific person when certain conditions are met. Thanks,
Chris
2 years ago
·
#3076
Hi there,

Please try the code below :)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Range("c:c"), Target) Is Nothing Then Exit Sub
If Target.Value = "done" Then
Set xRg = Target.Offset(0, -1) 'Find email address
Call Mail_small_Text_Outlook(xRg.Value)
End If

End Sub

Sub Mail_small_Text_Outlook(ByVal xTo As String)
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 = xTo
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use
' .Send
End With
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

You mentioned that you want to send an email to the PM whose intitials as in the same row that marked as completed. Is his/her email address on the same row? The code in the 6th row helps to find the project managers initials, you can change it to make it find the email address.

Please change the string "done" in the 5th row to the actual string you use to mark the job completed.

Note that you can change the snippet below to your needs.
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = xTo
.CC = ""
.BCC = ""
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use
' .Send
End With


If you have any questions, please don't hesitate to ask me.

Amanda
  • Page :
  • 1
There are no replies made for this post yet.