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]

.

 

[Snakehips]

@mattmcclements
Untested code but try with an additional parameter for the date within the email sub. As generated via the change event sub.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate 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

'String of Date in A
ByDate = CDate(Cells(Target.Row, 1))    '<<<<<<<<<<

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate)   '<<<<<<

' Send the email
emailItem.Send


End If

End If

End Sub

Sub Mail_small_Text_Outlook(MailAddress As String, MailAddress_CC As String, ByDate As String)  '<<< Inc date parameter
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" & ByDate & ""  '<<<<<<<<<<
.Display
End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[mattmcclements]

@mattmcclements
Untested code but try with an additional parameter for the date within the email sub. As generated via the change event sub.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate 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

'String of Date in A
ByDate = CDate(Cells(Target.Row, 1))    '<<<<<<<<<<

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate)   '<<<<<<

' Send the email
emailItem.Send


End If

End If

End Sub

Sub Mail_small_Text_Outlook(MailAddress As String, MailAddress_CC As String, ByDate As String)  '<<< Inc date parameter
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" & ByDate & ""  '<<<<<<<<<<
.Display
End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

That's worked perfectly thank you so much! Another quick question the trigger "target.value>=3 is works off imported information so the code will only trigger the email when the number is manual inputted, is there any way you can code it so when the information is imported the email will still generate?

 

[Snakehips]

Glad it helped.
Difficult for me to comment re the importation.
Maybe post import. you have a macro that scans the data for instances of ??>=3 and fires off emails accordingly?

 

[mattmcclements]

Glad it helped.
Difficult for me to comment re the importation.
Maybe post import. you have a macro that scans the data for instances of ??>=3 and fires off emails accordingly?

Thank you so much!

 

[mattmcclements]

Glad it helped.
Difficult for me to comment re the importation.
Maybe post import. you have a macro that scans the data for instances of ??>=3 and fires off emails accordingly?

Hi, as you can see from the attachment when the offence number column is >=3 it will flag the email but all the information regarding employee name, clock in time and date is all imported into a master sheet. If the offence number is manually inputted it will work but unfortunately it won't work if it's imported in.

Thank you again

Lates Tracker.xlsm
	A	B	C	D	E	F		H	I	J	K
1	Employee	Personnel Number	Date	Clock in	Start time	Minutes late		Shift	Responsible	Offence No.	Action
2	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
3	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
4	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
5	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
6	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
7	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
8	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
9	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
10	#N/A				#N/A	#N/A		#N/A	#N/A	0	#N/A
Lates

Cell Formulas
Range	Formula
E2:E10	E2	=VLOOKUP(B2,Master!$C$2:$I$1014,7,FALSE)
F2:F10	F2	=IF(D2>E2,D2-E2,"NOT LATE")
H2:H10	H2	=INDEX(Master!$D$2:$D$1338,MATCH(B2,Master!$C$2:C1013,0))
I2:I10	I2	=IF(H2=$N$2,"Joe Westwell",IF(H2=$N$3,"Aaron Johnson",IF(H2=$N$5,"Gareth Roberts",IF(H2=$N$6,"Steven Jones",IF(H2=$N$4,"Lee Massey",IF(H2=$N$7,"Malcolm Wright",IF(H2=$N$9,"Craig Jones",IF(H2=$N$8,"Lukasz Pawlik",IF(H2=$N$10,"Mike Moffatt",IF(H2=$N$11,"MANAGER",IF(H2=$N$12,"Gary Lee",IF(H2=$N$13,"Gary Lee",IF(H2=$N$14,"Mark Wright")))))))))))))
J2:J10	J2	=COUNTIFS($B$2:B2,B2,$G$2:G2,"LATE")
K2:K10	K2	=IF(G2=$R$1,"NOT LATE",IF(J2>=3,"Investigate",IF(J2=2,"1 Away",IF(J2=1,"First one"))))
A2:A10	A2	=INDEX(Master!$F$2:$F$1338,MATCH(B2,Master!$C$2:C1013,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F3394	Cell Value	between 0.0631944444444444 and 0.999988425925926	text	NO
F2:F3394	Cell Value	<=0.0625	text	NO
F2:F3394	Cell Value	contains "NOT LATE"	text	NO
J2:J3394	Cell Value	=0	text	NO
K2:K3394	Cell Value	contains "NOT LATE"	text	NO
J2:J3394	Cell Value	contains "NOT LATE"	text	NO
I2:I3394	Cell Value	contains "MANAGER"	text	NO
H2:H3394	Cell Value	contains "NA"	text	NO
K2:K3394	Expression	=K2="First one"	text	NO
J2:J3394	Cell Value	=1	text	NO
K2:K3394	Expression	=K2="1 Away"	text	NO
J2:J3394	Cell Value	=2	text	NO
K2:K3394	Expression	=K2="Investigate"	text	NO
C2:C1130,A2:A3394,J2:J3394	Cell Value	between 3 and 1000	text	NO

 

[Snakehips]

When I said '...post, import...' I was meaning post as in 'after' your import

Anyway, give or take the odd mistake / assumption in interpreting your data then maybe along these lines?
Again, code not tested.

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
DimLr As Long
Dim r As Long
On Error Resume Next


With Sheets("Report")

'Get last row

Lr = .Range("A" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range("J2:J" & Lr)
   
        If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 2) = "Y" Then '<<< Assumes 'Email Sent'  Confirmation Y' ' in column L <<< ???(Offset 2)
            r = cell.Row
            MailAddress = .Range("S" & r).Value
            MailAddress_CC = .Range("T" & r).Value
           
            'String of Date in C  '??
            ByDate = CDate(.Range("C" & r))
           
            Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate)   '<<<<<<
           
            ' Send the email
            emailItem.Send
       
         
        'Mark email as Sent in column L   <<<< ?????
        .Range("L" & r) = "Y"
   
   
        Else
        End If
    Next

End With

End Sub

 

[mattmcclements]

When I said '...post, import...' I was meaning post as in 'after' your import

Anyway, give or take the odd mistake / assumption in interpreting your data then maybe along these lines?
Again, code not tested.

Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
DimLr As Long
Dim r As Long
On Error Resume Next


With Sheets("Report")

'Get last row

Lr = .Range("A" & .Rows.Count).End(xlUp).Row
    For Each cell In .Range("J2:J" & Lr)
  
        If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 2) = "Y" Then '<<< Assumes 'Email Sent'  Confirmation Y' ' in column L <<< ???(Offset 2)
            r = cell.Row
            MailAddress = .Range("S" & r).Value
            MailAddress_CC = .Range("T" & r).Value
          
            'String of Date in C  '??
            ByDate = CDate(.Range("C" & r))
          
            Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate)   '<<<<<<
          
            ' Send the email
            emailItem.Send
      
        
        'Mark email as Sent in column L   <<<< ?????
        .Range("L" & r) = "Y"
  
  
        Else
        End If
    Next

End With

End Sub

Sorry for such a delayed response to this but it doesn't seem to be working. I've attached an example of what it should look like. The personnel ID, Date and clocked in all the imported data. This is the current code I have.

You've been such a great help so far, Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
Dim ByDate1 As String
Dim ByDate2 As String
Dim ByDate3 As String
On Error Resume Next

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

If IsNumeric(Target.Value) Then

If Target.Value >= 3 Then

MailAddress = Range("M" & Target.Row).Value
MailAddress_CC = Range("N" & Target.Row).Value

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

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

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

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

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate, ByDate1, ByDate2, ByDate3)

' Send the email
emailItem.Send


End If

End If

End Sub
Sub Mail_small_Text_Outlook(MailAddress As String, MailAddress_CC As String, ByDate As String, ByDate1 As String, ByDate2 As String, ByDate3 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 = "Lates Investigation"
.htmlBody = "Hi," & vbNewLine & vbNewLine & "you have a lates investigation to complete on " & ByDate & " who was late on " & ByDate1 & " by " & ByDate2 & " minutes, this is their " & ByDate3 & " offence."
.Display
End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
DimLr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Get last row

Lr = .Range("A" & .Rows.Count).End(xlUp).Row
For Each cell In .Range("J2:J" & Lr)

If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 2) = "Y" Then '<<< Assumes 'Email Sent' Confirmation Y' ' in column L <<< ???(Offset 2)
r = cell.Row
MailAddress = .Range("M" & r).Value
MailAddress_CC = .Range("" & r).Value

'String of Date in C '??
ByDate = CDate(.Range("C" & r))

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate) '<<<<<<

' Send the email
emailItem.Send


'Mark email as Sent in column L <<<< ?????
.Range("L" & r) = "Y"


Else
End If

Lates Tracker.xlsm
	A	B	C	D		F	G		I	J	K	L
1	Employee	Personnel Number	Date	Clock in		Start time	Minutes late		Shift	Responsible	Offence No.	Action
2	Person A	1	01/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
3	Person B	2	13/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
4	Person C	3	08/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
5	Person D	4	05/04/2022	6:02:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
6	Person E	5	01/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
7	Person F	6	15/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
8	Person G	7	08/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
9	Person H	8	09/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
10	Person I	9	14/04/2022	6:04:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
11	Person A	1	03/04/2022	9:59:00 AM		07:00:00	02:59:00		MANAGER	MANAGER	0	NOT LATE
12	Person B	2	01/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
13	Person C	3	08/04/2022	6:01:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
14	Person D	4	10/04/2022	9:59:00 AM		07:00:00	02:59:00		MANAGER	MANAGER	0	NOT LATE
15	Person E	5	11/04/2022	10:01:00 AM		07:00:00	03:01:00		MANAGER	MANAGER	0	NOT LATE
16	Person F	6	03/04/2022	9:57:00 AM		07:00:00	02:57:00		MANAGER	MANAGER	0	NOT LATE
17	Person G	7	04/04/2022	10:05:00 AM		07:00:00	03:05:00		MANAGER	MANAGER	0	NOT LATE
18	Person H	8	17/04/2022	9:58:00 AM		07:00:00	02:58:00		MANAGER	MANAGER	0	NOT LATE
19	Person I	9	03/04/2022	9:59:00 AM		07:00:00	02:59:00		MANAGER	MANAGER	0	NOT LATE
20	Person A	1	17/04/2022	9:58:00 AM		07:00:00	02:58:00		MANAGER	MANAGER	0	NOT LATE
21	Person B	2	10/04/2022	9:58:00 AM		07:00:00	02:58:00		MANAGER	MANAGER	0	NOT LATE
22	Person C	3	04/04/2022	8:10:00 AM		07:00:00	01:10:00		MANAGER	MANAGER	0	NOT LATE
23	Person D	4	10/04/2022	9:58:00 AM		07:00:00	02:58:00		MANAGER	MANAGER	0	NOT LATE
24	Person E	5	17/04/2022	9:55:00 AM		07:00:00	02:55:00		MANAGER	MANAGER	0	NOT LATE
25	Person F	6	03/04/2022	9:57:00 AM		07:00:00	02:57:00		MANAGER	MANAGER	0	NOT LATE
26	Person G	7	01/04/2022	6:15:00 AM		07:00:00	NOT LATE		MANAGER	MANAGER	0	NOT LATE
27	Person H	8	03/04/2022	9:59:00 AM		07:00:00	02:59:00		MANAGER	MANAGER	0	NOT LATE
28	Person I	9	10/04/2022	9:58:00 AM		07:00:00	02:58:00		MANAGER	MANAGER	0	NOT LATE
Lates

Cell Formulas
Range	Formula
F2:F28	F2	=IF(Lates!I2=Master!$D$2,VLOOKUP(Lates!E2,Master!$K$2:$L$8,2,FALSE),VLOOKUP(B2,Master!$C$2:$I$1008,7,FALSE))
G2:G28	G2	=IF(D2>F2,D2-F2,"NOT LATE")
I2:I28	I2	=INDEX(Master!$D$2:$D$1332,MATCH(B2,Master!$C$2:C1007,0))
J2:J28	J2	=IF(I2=$O$2,"Joe Westwell",IF(I2=$O$3,"Aaron Johnson",IF(I2=$O$5,"Gareth Roberts",IF(I2=$O$6,"Steven Jones",IF(I2=$O$4,"Lee Massey",IF(I2=$O$7,"Malcolm Wright",IF(I2=$O$9,"Craig Jones",IF(I2=$O$8,"Lukasz Pawlik",IF(I2=$O$10,"Mike Moffatt",IF(I2=$O$11,"MANAGER",IF(I2=$O$12,"Gary Lee",IF(I2=$O$13,"Gary Lee",IF(I2=$O$14,"Mark Wright")))))))))))))
K2:K28	K2	=COUNTIFS($B$2:B2,B2,$H$2:H2,"LATE")
L2:L28	L2	=IF(H2=$S$1,"NOT LATE",IF(K2>=3,"Investigate",IF(K2=2,"1 Away",IF(K2=1,"First one"))))
A2:A28	A2	=IFERROR(INDEX(Master!$F$2:$F$1332,MATCH(B2,Master!$C$2:C1007,0)),"NOT IN MASTER SHEET")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:F	Cell Value	contains "OVERTIME"	text	NO
G2:G3394	Cell Value	contains "NOT LATE"	text	NO
A:A	Cell Value	contains "NOT IN MASTER SHEET"	text	NO
3395:1048576,Q15:XFD18,1:1,O3:XFD14,O15:O18,O20:XFD3394,C2:XFD2,A80:N3394,P19:XFD19,H3:H3394,K3:K3394,A2:A79,C3:N79	Cell Value	contains "LEAVER"	text	NO
G2:G3394	Cell Value	>=0.000694444	text	NO
K2:K3394	Cell Value	=0	text	NO
L2:L3394	Cell Value	contains "NOT LATE"	text	NO
K2:K3394	Cell Value	contains "NOT LATE"	text	NO
J2:J3394	Cell Value	contains "MANAGER"	text	NO
I2:I3394	Cell Value	contains "NA"	text	NO
L2:L3394	Expression	=L2="First one"	text	NO
K2:K3394	Cell Value	=1	text	NO
L2:L3394	Expression	=L2="1 Away"	text	NO
K2:K3394	Cell Value	=2	text	NO
L2:L3394	Expression	=L2="Investigate"	text	NO
A2:A3394,E2:E3394,C2:C1130,K2:K3394	Cell Value	between 3 and 1000	text	NO

 

[Snakehips]

@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?