Including Date Worked in Range D:D when Value in R:R Changes

Sharonca

New Member
Joined
Jan 9, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have the following Macro that generates an email when the user changes the value in a cell in column R to Approved.. I would like to include the Date Worked (in column D) when the value changes to Approved in the body of the email. So if R3 changes to Approved the statement "Date Worked value (value in Cell D3)" is included in the body of the email. This would only be for the 2nd level email.

I am kind of stuck on how to work the Range designation.

My code is as follows.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)


'BBU
Dim xMailBody As String


On Error Resume Next
    
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


If Target.Cells.CountLarge > 1 Then Exit Sub


If Not Intersect(Target, Range("R:R")) Is Nothing Then


    If Target.Value = "Approved" Then
            xMailBody = "Cell(s) " & Target.Address(False, False) & _
            " were modified on " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " by " & Environ$("username") & vbNewLine & vbNewLine & "Date worked"




        With CreateObject("outlook.application").CreateItem(0)
             .To = "xxxx"
             .Subject = "BBU Invoice Approved - Level 1"
             .Body = xMailBody
             .display
        End With
        
            Columns.AutoFit
            Cells(Target.Row, "T").Value = UCase(Split(Application.UserName, ",")(0))
            Cells(Target.Row, "S").Value = Now
        End If
        
 '2nd level
ElseIf Not Intersect(Target, Range("V:V")) Is Nothing Then
 
    If Target.Value = "Approved" Then
      
            xMailBody = "Cell(s) " & Target.Address(False, False) & _
            " were modified on " & _
            Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
            " by " & Environ$("username")
    


        With CreateObject("outlook.application").CreateItem(0)
             .To = "xxx"
             .Subject = "BBU Invoice Final Approved"
             .Body = xMailBody
             .display
        End With
        
        Columns.AutoFit
            Cells(Target.Row, "X").Value = UCase(Split(Application.UserName, ",")(0))
            Cells(Target.Row, "W").Value = Now
        End If


End If
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top