How to make excel generate emails when a new data set is started and then closed

[RileyMurray]

Hello, I work for a company that does business with a contractor for outdoor services. We have a spreadsheet that has all of the details for each project and then whether or not the project is open or closed. The goal is to have the spreadsheet send an email to a list of recipients when a new project is opened, then another one to the same list when the project is closed. We have tried using the track changes feature, however, there is no way to let everyone know when there is a new item, not just changing details. What would be the best way to go about generating these emails?

This system runs entirely on Microsoft Office 2007


​Thank you for your assistance

 

[apo]

Hi..

You should be able to integrate this into your Workbook...

Probably best to recreate the sample i used (using the cell values shown below in the screenshots).. then integrate it once you know what it is doing..

Paste this code into Sheet1 in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTo As String, Subj As String, Bdy As String
Dim rList As String
Dim rng As Range


rList = "=Sheet2!$A$1:$A$2"


Set rng = Sheets("Sheet1").Range("F2:F" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)


For Each c In rng
Set r = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation), ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible))
If Intersect(c, r) Is Nothing Then


   'Cell has no validation
    With c.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=rList
    End With
    
End If
Next c


If Target.Column <> 6 Then Exit Sub
If Target.Value = "OPEN" Or Target.Value = "CLOSE" Then


strTo = ""
strTo = Sheets("Sheet2").Cells(1, 2) & ";" & Sheets("Sheet2").Cells(2, 2) & ";" & Sheets("Sheet2").Cells(3, 2)
Subj = Target.Offset(0, -5).Value
Bdy = "Project Name is: " & Target.Offset(0, -5).Value & Chr$(10) & "Project Status is " & Target.Value
    
'Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
        
'Display the email message
With objMailItem
.to = strTo
.Subject = Subj
.Body = Bdy
.Display 'Change to .Send if you want to just send it.
End With


'Release object variables from system memory.
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing


End If
End Sub

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Project</td><td style="font-weight:bold; ">Detail1</td><td style="font-weight:bold; ">Detail2</td><td style="font-weight:bold; ">Detail3</td><td style="font-weight:bold; ">Detail4</td><td style="font-weight:bold; ">Status</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >P1</td><td >D1P1</td><td >D2P1</td><td >D3P1</td><td >D4P1</td><td >OPEN</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >P2</td><td >D1P2</td><td >D2P2</td><td >D3P2</td><td >D4P2</td><td >CLOSE</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >P3</td><td >D1P3</td><td >D2P3</td><td >D3P3</td><td >D4P3</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >P4</td><td >D1P4</td><td >D2P4</td><td >D3P4</td><td >D4P4</td><td >OPEN</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >P5</td><td >D1P5</td><td >D2P5</td><td >D3P5</td><td >D3P5</td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:200px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >OPEN</td><td style="color:#0000ff; text-decoration:underline; ">test1@gmail.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >CLOSE</td><td style="color:#0000ff; text-decoration:underline; ">test2@gmail.com</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="color:#0000ff; text-decoration:underline; ">test3@gmail.com</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[apo]

Sorry.. i should have explained better..

I am using a Data Validation List (dropdown) to allow the user to select either "OPEN" or "CLOSE".. the Data Validation list source is taken from A1:A2 on Sheet2..

In the example provided.. whenever you add a new project in Column A.. a new Data validation drop down will be added to the cell in Column F for that row..
I have added it all to the WorkSheet_Change Event.. (targeting column 6 .. which is column F).. if the change event is NOT in Column F.. it exits the Sub..

So.. basically... whenever you select OPEN or CLOSE.. an email will be created in Outlook with the To: and Subject and Body populated..

Like i say.. if you recreate the saem sample.. you wil be able to integrate it into your Workbook..

 

[RileyMurray]

I have set up the macro in my spreadsheet, however, the email box doesn't pop up. Do you have any advice?

 

[apo]

Hi..

There could be a couple of reasons why the code is not firing..

Did you put the code into the correct sheet in the VB Editor?

Did you change the column configuration to what is in my sample.. the email will only be generated if you choose OPEN or CLOSE in Column G.

Best thing to do is to post your code as it is and include a screenshot of your sheet layout(s) using Excel Jeanie.. (like i did in my first reply)..

Excel Jeanie Html

 

[RileyMurray]

That solved it, thank you for your help.