Log in  \/ 
Register  \/ 


unanswered Auto Email sent based on value of a single cell.

1 year 2 months ago #1512 by griffd12
Hello. I am using the below vb script.
Cell C:5 is a validation column that you can select from a drop down list. I need to send automatic emails based off of 3 possible selections from the drop down list. (New, Escalate, Closed)

How can i get this vb script to look for each of these text and when selected a separate email with separate subject and body needs to be created.(i.e. a email for new cases, a email for escalated cases etc.)

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("C5"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value = "New" 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 = "griffd12@hotmail.com"
.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

Please Log in or Create an account to join the conversation.

Moderators: jaychivoExcelfansqiuhongkun