this code send a mail when a cell in column V take value or if the value changes.the mail contains the values in columns C,D,E,V,and the word today,but i get invaled or unqualified reference error.
what is wrong in the code?
what is wrong in the code?
Code:
Option Explicit
Private Sub Worksheet_Calculate()
Dim arrNew_V As Variant
Dim iLoop As Integer
Dim strMessage As String
arrNew_V = Application.Transpose(rColV)
'check current value of Column J with previous value of Column J
If (arrNew_V(iLoop) <> arrColV(iLoop)) And _
(Trim(arrNew_V(iLoop)) <> "") Then 'if the values are different, AND the new value is not blank
strMessage = strMessage & .Cells(iLoop, "C").Text & " " & .Cells(iLoop, "D").Text & " " & .Cells(iLoop, "E").Text & " " & .Cells(iLoop, "V").Text & " " & "Today" & vbCrLf
End If
Next iLoop
End With
'store new values for comparing the next time.
arrColV = arrNew_V
'send one message
If Trim(strMessage) <> "" Then
Send_Email (strMessage2)
End If
End Sub
Code:
Private Sub Workbook_Open()
'set up locations for comparing values... they MUST start on Row1, even if your data is starting elsewhere
Set rColV = Worksheets("Test").Range("V1:V100")
'store the data from each column in an Array for comparing later.
arrColV = Application.Transpose(rColV)
End Sub
Code:
Option Explicit
Public arrColV As Variant
Public rColV As Range
Sub Send_Email(strBody As String)
Dim OutApp As Object
Dim OutMail As Object
With ActiveSheet
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "alert@outlook.com"
.Subject = "Update " & Format(Now(), "dd/mmm/hh hh:mm")
.Body = strBody
.display 'or use .send
End With
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub