Send email to specific recipient based on specific cell value

[gempazz]

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):

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]

I'm not sure if this will work but give it a try:

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

 

[gempazz]

Hi,

It works perfectly! Thanks a lot

 

[mumps]

You are very welcome.