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]

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]

Hi,

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

 

[mumps]

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]

										Resource requirement
Date submitted	Brief description	Proposer	Commercial team	Additional information	Product type(s)	Number of products	Number of suppliers	Existing and approved OB supplier site(s)	Artwork requirement	Category technologist	Artworker	Product assessment team	Key timings (dates)	Technologist assigned	Additional comments (AL)	Project status		Tech email
09/10/2020	Frozen Veg Tender	Proposer1	Food & Drink (PG)	Frozen Veg Tender 2020	Frozen Veg in bag	12	6	Yes	Amend existing artwork(s)	Yes	Yes	No	11/2020	Person3				Person3@email.com
09/10/2020	Palm oil update Chips	Propoer2	Food & Drink (PG)	Removal of palm to sunlfower oil, new coating + plastic reduction + OPRL LOGOS	Frozen Chips, Wedges, Potatos	21	1	Yes	Amend existing artwork(s)	Yes	Yes	No	Jan 2021- May 2021	Person1				Person1@email.com
09/10/2020	S&B Coconut Milk	Proposer3	Food & Drink (PG)	Change of supplier	Coconut Milk	3	1	No	Amend existing artwork(s)	Yes	Yes	No	12/2020	Person2				Person2@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]

Technologist Allocation Tracker.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1											Resource requirement
2	Date submitted	Brief description	Proposer	Commercial team	Additional information	Product type(s)	Number of products	Number of suppliers	Existing and approved OB supplier site(s)	Artwork requirement	Category technologist	Artworker	Product assessment team	Key timings (dates)	Technologist assigned	Additional comments (AL)	Project status		Tech email
3	09/10/2020	Frozen Veg Tender	Proposer1	Food & Drink (PG)	Frozen Veg Tender 2020	Frozen Veg in bag	12	6	Yes	Amend existing artwork(s)	Yes	Yes	No	11/2020	Person3				Person3@email.com
4	09/10/2020	Palm oil update Chips	Propoer2	Food & Drink (PG)	Removal of palm to sunlfower oil, new coating + plastic reduction + OPRL LOGOS	Frozen Chips, Wedges, Potatos	21	1	Yes	Amend existing artwork(s)	Yes	Yes	No	Jan 2021- May 2021	Person1				Person1@email.com
5	09/10/2020	S&B Coconut Milk	Proposer3	Food & Drink (PG)	Change of supplier	Coconut Milk	3	1	No	Amend existing artwork(s)	Yes	Yes	No	12/2020	Person2				Person2@email.com
Tracker

Cell Formulas
Range	Formula
S3:S5	S3	=WorkTracker[@[Technologist assigned]]&"@"&"email.com"

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q1,Q3:Q1048576	Cell Value	contains "Completed"	text	NO
Q1,Q3:Q1048576	Cell Value	contains "Project underway"	text	NO
Q1,Q3:Q1048576	Cell Value	contains "Not yet started"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O3:O5	List	=TechList
Q3:Q5	List	=StatusList

This is probably a better version

 

[mumps]

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.

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]

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?

 

[mumps]

What is the hyperlink to the file?

 

[gempazz]

Hi,

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