Send email to specific recipient based on specific cell value

gempazz

New Member
Joined
Oct 26, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I just started learning VBA a couple weeks ago and still very new to this. I have a project, and I need to make something that send email notifications to newly assigned project owners.

Background: Column O contains a drop list of the names of the recipients (which will be project owners). These cells are empty and are only selected by the project allocator when the project details are entered in column A to M. There are only 5 options, so I thought I could automate the recipient emails. The main things I'm looking for is, once the recipient name is selected column O, how to:
  • make a Outlook window pop up with recipient's email (e.g Person1, Person2, Person3...).>>This is the main thing I need for the project.
  • In the body message, include details of the project in column B along with some other written text (N.B "This is a notification regarding a new assigned project: (Text in column B). Please log into the tracker and update your project status.">> I can do without this but would be nice to have.
There are many things I'm not sure about:
  • how to make sure it will only trigger the email when the NEW value is assigned in column O
  • how to make the recipient email pop up based on the new value in column O
  • how to write the body text based on the value in column B
So far I've got (almost nothing very automated really):

VBA Code:
Sub Notification()
If ....

    'Send notification
    Dim OlApp As Object
    Dim NewMail As Object
    Dim xMailBody As String

    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)
    xMailBody = "This is a notification regarding a new assigned project. Please log in to the tracker and update your project status"

    On Error Resume Next
    With NewMail
        .To = "email@email.com"
        .Subject = "New project notification"
        .Display
    End With
    On Error GoTo 0

    Set NewMail = Nothing
    Set OlApp = Nothing
End Sub


I would greatly appreciate if someone can help me with this code, or point me in a direction where I can research on how to make this happen.


Many thanks.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,712
I'm not sure if this will work but give it a try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, xMailBody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = Range("S" & Target.Row)
        .Subject = "New project notification"
        .HTMLBody = "This is a notification regarding a new assigned project: " & Range("B" & Target.Row) & "<br><br>" & _
            "Please log in to the " & "<a href=""\\private\Shared Data\Central\Workload Allocation\Technologist Allocation Tracker.xlsm"">tracker</a> and update your project status."
        .Display
    End With
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top