Results 1 to 3 of 3

Thread: invaled or unqualified reference error

  1. #1

    invaled or unqualified reference error

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    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
    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
    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 = ""
                .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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    Where do you get the error, which line of code?

  3. #3
    strMessage = strMessage & .Cells(iLoop, "C").Text & " " &  .Cells(iLoop, "D").Text & " " & .Cells(iLoop, "E").Text & " "  & .Cells(iLoop, "V").Text & " " & "Today" & vbCrLf
    in .Cells (iloop, "C")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts