Add variable into body of text

[mattmcclements]

Hi,

I want to add a variable into the body of text to say, you have an investigation to complete on "x" but I'm unsure how to do so. The information is stored in A. This is the code I have so far, I'm still at very early stages of VBA so any help would be greatly appreciated.

Thank you

Dim Xrg As Range
'Update by Extendoffice 2020/7/17
Private Sub Worksheet_Change(ByVal Target As Range)

Dim MailAddress As String
Dim MailAddress_CC As String

On Error Resume Next

If Intersect(Range("E2:ZZ1000"), Target) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If IsNumeric(Target.Value) Then

If Target.Value >= 3 Then

MailAddress = Range("S" & Target.Row).Value
MailAddress_CC = Range("T" & Target.Row).Value

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC)

' Send the email
emailItem.Send


End If

End If

End Sub
Sub Mail_small_Text_Outlook(MailAddress As String, MailAddress_CC As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim cell As Range
Dim strbody As String

Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

On Error Resume Next
With xOutMail
.To = MailAddress
.Cc = MailAddress_CC
.BCC = ""
.subject = "Driver Errors Investigation"
.htmlBody = "Hi," & vbNewLine & vbNewLine & "You have driver error investigations to complete on" & Cells(cell.Row, "A").Value & ""
.Display
End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[mattmcclements]

@mattmcclements
I think we have a classic case here of poster knowing exactly what they have and want but maybe not conveying same to a helper who makes assumptions and gets them a bit wrong!
From seeing your latest post, I think I am a tad wiser.
Can you please confirm or otherwise inform on the following points?

'Lates' data extract above typical of imported data set but there are additional columns?
You wish code to scan this data-set and send email where offences are >= 3 ?
Will you scan any data-set more than once? As in, do we need to indicate when an email has been sent for a given row, so that it is disregarded for subsequent scans?
If so, which blank column do you have available to take a 'Y' to indicate email sent?

I had assumed that 'you have an investigation to complete on "x"' in your initial post meant that x was a date!! Hence my code trying to create a date for 'ByDate' variable.
I now believe it to be an employee name.
So, in your code :
ByDate. = Name from Column A Shall we call that EmpName
ByDate1 = Date from Column C Shall we call that OnDate
ByDate2 = Minutes late from Column V Shall we call that MinLate
ByDate3 = Number of Offences from Column K Shall we call that NumOffs

eMail address for recipient to be found in Column S of row ?
email address for CC to be found in Column T of row?

I'm really sorry I'm still trying to grasp VBA, there's a lot to learn!
I'm not quite sure what that first question means but there are 3 columns of imported data which are all used to trigger other VLookups etc.
Yes I am trying to scan all offences >=3 and then send the email.
It will be scanned more than once and column "W" is now going to indicate if an email has already been sent.
Apologies yes I realised when I re-read my question how easily it could be taken and just ran with the code I had rather than change it. The code has now been changed to match:

ByDate = (Cells(Target.Row, "A"))

OnDate = (Cells(Target.Row, "C"))

MinLate = (Cells(Target.Row, "V"))

NumOffs = (Cells(Target.Row, "K"))

The email address recipient is column M and works of a VLOOKUP of the shift so depending on the employee, the email address will be different.
The email address for CC is found in column N and works in the same manner as the email address.

Again thank you so much, I really want to master Excel so that I can eventually help people who are like I am now.

 

[Snakehips]

No problem. Unless I have been careless, this is hopefully pretty much it.
Note that the first variable now reads ByEmp !!!!!!!!
Note that at the bottom. I have your email call and send disabled by commenting out.
I have a debug line enabled instead for feedback as I cannot test the mailing.
Remove / add leading apostrophes when you need. Remove the debug all together if you want.

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByEmp As String
Dim OnDate As String
Dim MinLate As String
Dim NumOffs As String
Dim Lr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Determine last row based on data in A
Lr = .Range("A" & .Rows.Count).End(xlUp).Row
'Loop through column K
    For Each cell In .Range("K2:K" & Lr)
     
    
        'test for offences >=3 AND 'email sent' not marked as 'Y' in column W  (Offset 12 from K)
            If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 12) = "Y" Then
             'update r as row of interest
                r = cell.Row
                
                'Establish parameters for email sub
                ' email addresses
                MailAddress = .Range("M" & r).Value
                MailAddress_CC = .Range("N" & r).Value
                'Employee
                ByEmp = .Range("A" & r).Value
                'Date
                OnDate = CDate(.Range("C" & r))
                'Minutes late
                MinLate = .Range("V" & r)
                'Number of offences
                NumOffs = .Range("K" & r)
                
                'call  email sub and pass variables
               ''''' Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByEmp, OnDate, Minlate, NumOffs)
                
                ' Send the email
                '''''emailItem.Send
                
                'Mark email for row as sent, with 'Y'  in column W
                .Range("W" & r) = "Y"
                
               '****************
               '******* test print in the Immediate pane if mail call and send statements above are commented out while testing
               ' remove leading apostrophes to enble commented out statements
              Debug.Print "Hi, you have a lates investigation to complete on " & ByEmp & " who was late on " & OnDate & " by " & MinLate & " minutes, this is their " & NumOffs & " offence."
               '********
               
                Else
                'Otherwise ignore this row and continue loop
            End If
    
    Next

End With
End Sub

Hope that helps.
+ 1 for Learning by doing. Good luck! Enjoy.

 

[mattmcclements]

No problem. Unless I have been careless, this is hopefully pretty much it.
Note that the first variable now reads ByEmp !!!!!!!!
Note that at the bottom. I have your email call and send disabled by commenting out.
I have a debug line enabled instead for feedback as I cannot test the mailing.
Remove / add leading apostrophes when you need. Remove the debug all together if you want.

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByEmp As String
Dim OnDate As String
Dim MinLate As String
Dim NumOffs As String
Dim Lr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Determine last row based on data in A
Lr = .Range("A" & .Rows.Count).End(xlUp).Row
'Loop through column K
    For Each cell In .Range("K2:K" & Lr)
    
   
        'test for offences >=3 AND 'email sent' not marked as 'Y' in column W  (Offset 12 from K)
            If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 12) = "Y" Then
             'update r as row of interest
                r = cell.Row
               
                'Establish parameters for email sub
                ' email addresses
                MailAddress = .Range("M" & r).Value
                MailAddress_CC = .Range("N" & r).Value
                'Employee
                ByEmp = .Range("A" & r).Value
                'Date
                OnDate = CDate(.Range("C" & r))
                'Minutes late
                MinLate = .Range("V" & r)
                'Number of offences
                NumOffs = .Range("K" & r)
               
                'call  email sub and pass variables
               ''''' Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByEmp, OnDate, Minlate, NumOffs)
               
                ' Send the email
                '''''emailItem.Send
               
                'Mark email for row as sent, with 'Y'  in column W
                .Range("W" & r) = "Y"
               
               '****************
               '******* test print in the Immediate pane if mail call and send statements above are commented out while testing
               ' remove leading apostrophes to enble commented out statements
              Debug.Print "Hi, you have a lates investigation to complete on " & ByEmp & " who was late on " & OnDate & " by " & MinLate & " minutes, this is their " & NumOffs & " offence."
               '********
              
                Else
                'Otherwise ignore this row and continue loop
            End If
   
    Next

End With
End Sub

Hope that helps.
+ 1 for Learning by doing. Good luck! Enjoy.

That's worked perfectly thank you so much! Just another quick question, how easy would it be to change the trigger (K=>3) from a numerical value to the text e.g. "Not absent"?

 

[mattmcclements]

That's worked perfectly thank you so much! Just another quick question, how easy would it be to change the trigger (K=>3) from a numerical value to the text e.g. "Not absent"?

Apologies, everything is working apart from when scanned again, it is still returning instances which have already been scanned and confirmed. This is probably an error on my behalf but this is the code I have.

Thank you again

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByEmp As String
Dim OnDate As String
Dim MinLate As String
Dim NumOffs As String
Dim Lr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Determine last row based on data in A
Lr = .Range("A" & .Rows.Count).End(xlUp).Row
'Loop through column K
For Each cell In .Range("K2:K" & Lr)


'test for offences >=3 AND 'email sent' not marked as 'Y' in column W (Offset 12 from K)
If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 12) = "Y" Then
'update r as row of interest
r = cell.Row

'Establish parameters for email sub
' email addresses
MailAddress = .Range("M" & r).Value
MailAddress_CC = .Range("N" & r).Value
'Employee
ByEmp = .Range("A" & r).Value
'Date
OnDate = CDate(.Range("C" & r))
'Minutes late
MinLate = .Range("V" & r)
'Number of offences
NumOffs = .Range("K" & r)

'call email sub and pass variables
Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByEmp, OnDate, MinLate, NumOffs)

'Send the email
emailItem.Send

'Mark email for row as sent, with 'Y' in column W
.Range("W" & r) = "Yes"

End If

Next

End With
End Sub

 

[mattmcclements]

Apologies, everything is working apart from when scanned again, it is still returning instances which have already been scanned and confirmed. This is probably an error on my behalf but this is the code I have.

Thank you again

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByEmp As String
Dim OnDate As String
Dim MinLate As String
Dim NumOffs As String
Dim Lr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Determine last row based on data in A
Lr = .Range("A" & .Rows.Count).End(xlUp).Row
'Loop through column K
For Each cell In .Range("K2:K" & Lr)


'test for offences >=3 AND 'email sent' not marked as 'Y' in column W (Offset 12 from K)
If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 12) = "Y" Then
'update r as row of interest
r = cell.Row

'Establish parameters for email sub
' email addresses
MailAddress = .Range("M" & r).Value
MailAddress_CC = .Range("N" & r).Value
'Employee
ByEmp = .Range("A" & r).Value
'Date
OnDate = CDate(.Range("C" & r))
'Minutes late
MinLate = .Range("V" & r)
'Number of offences
NumOffs = .Range("K" & r)

'call email sub and pass variables
Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByEmp, OnDate, MinLate, NumOffs)

'Send the email
emailItem.Send

'Mark email for row as sent, with 'Y' in column W
.Range("W" & r) = "Yes"

End If

Next

End With
End Sub

Please ignore this apologies I have figured it out

 

[Snakehips]

Please ignore this apologies I have figured it out

Thank goodness for that!! 'Y' >'Yes' !!
Do you still want to change the trigger to "Not Absent" ?
If so what column would that be in?

 

[mattmcclements]

Thank goodness for that!! 'Y' >'Yes' !!
Do you still want to change the trigger to "Not Absent" ?
If so what column would that be in?

No sorry it's working perfectly now, thank you so much for your continued help, I promise I'll pay it forward one day!