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

No	Username	Status
1	Sam	Completed/Incomplete
2	Tin	Completed/Incomplete
3	Josh	Completed/Incomplete
4	Ana	Completed/Incomplete
5	Ben	Completed/Incomplete

<tbody>
</tbody>

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!