Send a mail notification when the value of the cell is completed

[unknownymous]

Hi Guys,

So I have below data in excel and I'm just wondering if the macro can send an email notification to me once the user click Completed (drop down type - Completed/Incomplete).


[TABLE="width: 500"]
<tbody>[TR]
[TD]No[/TD]
[TD]Username[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sam[/TD]
[TD]Completed/Incomplete[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tin[/TD]
[TD]Completed/Incomplete[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Josh[/TD]
[TD]Completed/Incomplete[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ana[/TD]
[TD]Completed/Incomplete[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ben[/TD]
[TD]Completed/Incomplete[/TD]
[/TR]
</tbody>[/TABLE]


Email Notification details:

Subject: Task Completed
Body: The task has been completed by the user as of "today's date"

Also, I need to BCC some participants for the email notif.

PS: I'm using microsoft outlook 2010


Any help will be much appreciated.


Thank you!

 

[ranman256]

in a module ,paste the following code.
NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE(Alt-F11); menu,tools, references, Microsoft Outlook XX Object library

Public Sub EmailNotice(ByVal pvKey, pvUser)
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim olMailItem

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.createitem(olMailItem)

   With oMail
            .to = "fMulder@xfiles.fbi.gov"
            .CC = ""
            .BCC = ""
            .Subject = "Project closed"
            .Body = pvKey & " was completed by " & pvUser
            
              'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            
            .Send      'or use     .Display
   End With
End Sub

Now, paste the trigger event:


open the VBE window: Alt-F11,
in the far left PROJECT window pane,
under the VBAProject (your workbook)
dbl-click the ThisWorkbook object,
in the far right pane (code section)
paste this code in there

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim isct As Range
Dim vProj, vUser

Set isct = Application.Intersect(Target, Range("C2:C5000"))
If (Not (isct Is Nothing)) Then
   if target.value = "Completed/Incomplete" then
      vProj = Target.Offset(0, -2).Value
      vUser = Environ("Username")
      EmailNotice vProj, vUser
   endif 

End If
Set isct = Nothing
End Sub

 

[unknownymous]

Thank you!

 

[unknownymous]

Hello,

So I have below codes but I also want to add my outlook signature. How can I incorporate that?


' Create a new mail message item.
Set NewMail = OlApp.CreateItem(olMailItem)
With NewMail
.Subject = Range("E" & i).Value
.To = Range("B" & i).Value
.CC = Range("C" & i).Value
.BCC = Range("D" & i).Value
.Body = Range("F" & i).Value
If Range("H" & i).Value <> "" Then .Attachments.Add Range("H" & i).Value
If Range("I" & i).Value <> "" Then .Attachments.Add Range("I" & i).Value
If Range("J" & i).Value <> "" Then .Attachments.Add Range("J" & i).Value
If Range("K" & i).Value <> "" Then .Attachments.Add Range("K" & i).Value
If Range("L" & i).Value <> "" Then .Attachments.Add Range("L" & i).Value

End With

NewMail.Send


Any help will be much appreciated. Thanks!