Use the value of a specific cell in the last row

[BadgerMushroomSnake]

Hi everyone,

I have a pretty basic spreadsheet with 8 columns.
Data get added everyday (10 rows per day average)

I've done my research on internet but it seems to be rather difficult to find the info I would need. And I am pretty much a newbie.

I am trying to create a Macro linked to a button that will :

- Send an email to 5 persons that will always be the same (I think I can modify a code I have for that)
- The subject of the email should be "Cancellation - Cell Value of last row, column A - Cell Value of last row, column B"
- The body should be "Please cancel booking - Cell value of last row, column A"


Basically I need to refer to a specific cell in the last row with data.
I can only guess that I need an attribute to define the last row and then something like

Dim Lastrow As Long

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

oMail.Subject = "Cancellation - " & Cell.Value(Lastrow, 1) & " - " & Cell.Value (Lastrow, 2)

I guess I am all wrong, could you help me or redirect me to an article/video/... ?

Thank you for your help!

 

[Leith Ross]

Hello Bader,

The code you posted is correct for finding the last cell in column "A" with a value in it. What issues are you having with the code?

 

[BadgerMushroomSnake]

Hi Leith,

Thanks for the quick reply.

Really? Is that part of code correct? Because what's in the email subject, I totally made up.
What will be then the code to find the last cell in column B?

Thanks

 

[BadgerMushroomSnake]

Would it be something like that:

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row ---> Find the last non blank cell in column A
Lastrowb = Cells(Rows.Count, 2).End(xlUp).Row --> Find the last non blank cell in column B

????

And then for the subject " oMail.subject = "Cancellation - " & Lastrow & " - " & Lastrowb

???

 

[Leith Ross]

Hello Badger,

If both column "A" and column "B" always have values and the same number of rows then you can use this code...

Dim Lastrow As Long


    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row


    oMail.Subject = "Cancellation - " & Cells(Lastrow, 1).Value & " - " & Cells(Lastrow, 2).value

If column "A" and "column"B" have different number of rows then use...

    LastrowA = Cells(Rows.Count, 1).End(xlUp).Row ---> Find the last non blank cell in column A
    LastrowB = Cells(Rows.Count, 2).End(xlUp).Row --> Find the last non blank cell in column B

 

[BadgerMushroomSnake]

Hi Leith,

Thanks for the explanation. It'as been really helpful.

Now I have another issue: When I run the code below, when I refer to a cell of the last row, it gives me the value that cell had the first time I ran it, but it's changed now and that value is not even in the spreadsheet anymore.

The value "Cells(Lastrow, 1).Value" given (in my case "Ref88888") is not even in the file anymore. It was the value of the cell of the last row column A the first time I ran the macro.

Maybe the macro has to be reset every time I run it by some way... Could you help again?

Sub SendLastRow2()


Dim Mail_Object, Mail_Single As Variant


Dim Lastrow As Long


Lastrow = Cells(Rows.Count, 1).End(xlUp).Row


Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single


.Subject = "THIS IS A TEST cancellation - " & Cells(Lastrow, 1).Value & " / " & Cells(Lastrow, 2).Value
.To = "me@me.com"
.CC = ""
.BCC = ""
.Body = "test"


.Display


End With


End Sub

 

[BadgerMushroomSnake]

Alright, ignore my last comment
I've been a bit stupid and forgot some data in the file
Everything works perfectly
Thanks a lot

To share the code :

Sub SendLastRow()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim Lastrow As Long




    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)




    On Error Resume Next
    With OutMail
        .To = "me@me.com"
        .CC = ""
        .BCC = ""
        .Subject = "THIS IS A TEST cancellation - " & Cells(Lastrow, 1).Value & " / " & Cells(Lastrow, 2).Value
        .Body = "Hi," & vbNewLine & vbNewLine & _
              "Please cancel the booking" & vbNewLine & vbNewLine & _
              "Booking: " & Cells(Lastrow, 1).Value & vbNewLine & _
              "xxx booking: " & Cells(Lastrow, 2).Value & vbNewLine & vbNewLine & _
              "Comment: " & Cells(Lastrow, 5).Value & vbNewLine & vbNewLine & _
              "Thanks,"
        .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub