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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,712
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

gempazz

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

Thanks for this. Here is a screenshot of the current file. Please let me know if any further information would be helpful.

Capture.PNG
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,712
You have attached a picture and pictures are hard to work with. Please use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture). Also, I don't see any email address in your data.
 

gempazz

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

ADVERTISEMENT

Resource requirement
Date submittedBrief descriptionProposerCommercial teamAdditional informationProduct type(s)Number of productsNumber of suppliersExisting and approved OB supplier site(s)Artwork requirementCategory technologistArtworkerProduct assessment teamKey timings (dates)Technologist assignedAdditional comments (AL)Project statusTech email
09/10/2020Frozen Veg TenderProposer1Food & Drink (PG)Frozen Veg Tender 2020Frozen Veg in bag126YesAmend existing artwork(s)YesYesNo11/2020Person3Person3@email.com
09/10/2020Palm oil update ChipsPropoer2Food & Drink (PG)Removal of palm to sunlfower oil, new coating + plastic reduction + OPRL LOGOSFrozen Chips, Wedges, Potatos211YesAmend existing artwork(s)YesYesNoJan 2021- May 2021Person1Person1@email.com
09/10/2020S&B Coconut MilkProposer3Food & Drink (PG)Change of supplierCoconut Milk31NoAmend existing artwork(s)YesYesNo12/2020Person2Person2@email.com


I hope the XL2BB above worked.

Hi,
I've made the column for emails visible now (column S).

My goal is when someone assign a value in column O (e.g Person3), an outlook window will pop-up with the recipient to be value from Column S (Person3@email.com).
The body text message will be: "This is a notification regarding a new assigned project: [text in column B e.g Frozen Veg Tender.]. Please log into the tracker to update your project status".

Many thanks,
 

gempazz

New Member
Joined
Oct 26, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Technologist Allocation Tracker.xlsm
ABCDEFGHIJKLMNOPQRS
1Resource requirement
2Date submittedBrief descriptionProposerCommercial teamAdditional informationProduct type(s)Number of productsNumber of suppliersExisting and approved OB supplier site(s)Artwork requirementCategory technologistArtworkerProduct assessment teamKey timings (dates)Technologist assignedAdditional comments (AL)Project statusTech email
309/10/2020Frozen Veg TenderProposer1Food & Drink (PG)Frozen Veg Tender 2020Frozen Veg in bag126YesAmend existing artwork(s)YesYesNo11/2020Person3Person3@email.com
409/10/2020Palm oil update ChipsPropoer2Food & Drink (PG)Removal of palm to sunlfower oil, new coating + plastic reduction + OPRL LOGOSFrozen Chips, Wedges, Potatos211YesAmend existing artwork(s)YesYesNoJan 2021- May 2021Person1Person1@email.com
509/10/2020S&B Coconut MilkProposer3Food & Drink (PG)Change of supplierCoconut Milk31NoAmend existing artwork(s)YesYesNo12/2020Person2Person2@email.com
Tracker
Cell Formulas
RangeFormula
S3:S5S3=WorkTracker[@[Technologist assigned]]&"@"&"email.com"
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q1,Q3:Q1048576Cell Valuecontains "Completed"textNO
Q1,Q3:Q1048576Cell Valuecontains "Project underway"textNO
Q1,Q3:Q1048576Cell Valuecontains "Not yet started"textNO
Cells with Data Validation
CellAllowCriteria
O3:O5List=TechList
Q3:Q5List=StatusList


This is probably a better version
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,712

ADVERTISEMENT

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column O and press the RETURN key.
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 tracker and update your project status."
        .Display
    End With
    Application.ScreenUpdating = True
End Sub
 

gempazz

New Member
Joined
Oct 26, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi thank you very much! This works perfectly!

For the email body, is there any way I can also insert the hyperlink to the file and insert the link to the word "tracker" in the body message?
 

gempazz

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

It looks something like this
"\\private\Shared Data\Central\Workload Allocation\Technologist Allocation Tracker.xlsm"
 

Watch MrExcel Video

Forum statistics

Threads
1,112,797
Messages
5,542,558
Members
410,560
Latest member
1ndependent
Top