VBA last row value

thardin17

New Member
Joined
Mar 10, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
How do I return the values in the last row of data and add those values to the body of an email? I'm a beginner. This is what I have come up with so far. Thanks for the help

Sub Email_From_Excel_Basic()

Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)

emailItem.to = "Email@company.com"

emailItem.Subject = "New Customer Sales Log Entry"

'emailItem.Body = Range.End(x1Up).Value

'emailItem.Attachments.Add ActiveWorkbook.FullName

emailItem.Send
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Can you give a sample dataset? The value of a single cell, or the entire row? Including headers or not?
 
Upvote 0
To take the value from column A:

VBA Code:
Sub Email_From_Excel_Basic()

  Dim emailApplication As Object
  Dim emailItem As Object
  
  Set emailApplication = CreateObject("Outlook.Application")
  Set emailItem = emailApplication.CreateItem(0)
  
  emailItem.to = "Email@company.com"
  
  emailItem.Subject = "New Customer Sales Log Entry"
  
  emailItem.Body = Range("A" & Rows.Count).End(xlUp).Value
  
  'emailItem.Attachments.Add ActiveWorkbook.FullName
  
  'emailItem.Display
  emailItem.Send

End Sub

Do you need more values from the last row with data? Which columns?

Range.End(x1Up).Value
Note: You put x1up (number one) and it must be the letter l: xlUp
 
Upvote 0
To take the value from column A:

VBA Code:
Sub Email_From_Excel_Basic()

  Dim emailApplication As Object
  Dim emailItem As Object
 
  Set emailApplication = CreateObject("Outlook.Application")
  Set emailItem = emailApplication.CreateItem(0)
 
  emailItem.to = "Email@company.com"
 
  emailItem.Subject = "New Customer Sales Log Entry"
 
  emailItem.Body = Range("A" & Rows.Count).End(xlUp).Value
 
  'emailItem.Attachments.Add ActiveWorkbook.FullName
 
  'emailItem.Display
  emailItem.Send

End Sub

Do you need more values from the last row with data? Which columns?


Note: You put x1up (number one) and it must be the letter l: xlUp
Thank you
 
Upvote 0
I would like to return all the data in that row for all columns that have data in them.
Assuming you have data in column A.
Try this:

VBA Code:
Sub Email_From_Excel_Basic()
  Dim emailApplication As Object
  Dim emailItem As Object
  Dim j As Long, lr As Long
  Dim sBody As String
  
  Set emailApplication = CreateObject("Outlook.Application")
  Set emailItem = emailApplication.CreateItem(0)
  
  emailItem.To = "Email@company.com"
  
  emailItem.Subject = "New Customer Sales Log Entry"
  lr = Range("A" & Rows.Count).End(xlUp).Row
  For j = 1 To Cells(lr, Columns.Count).End(1).Column
    sBody = sBody & " " & Cells(lr, j).Value
  Next
  emailItem.Body = sBody
  
  'emailItem.Attachments.Add ActiveWorkbook.FullName
  
  'emailItem.Display
  emailItem.Send

End Sub

🫡
 
Upvote 0
Solution
Assuming you have data in column A.
Try this:

VBA Code:
Sub Email_From_Excel_Basic()
  Dim emailApplication As Object
  Dim emailItem As Object
  Dim j As Long, lr As Long
  Dim sBody As String
 
  Set emailApplication = CreateObject("Outlook.Application")
  Set emailItem = emailApplication.CreateItem(0)
 
  emailItem.To = "Email@company.com"
 
  emailItem.Subject = "New Customer Sales Log Entry"
  lr = Range("A" & Rows.Count).End(xlUp).Row
  For j = 1 To Cells(lr, Columns.Count).End(1).Column
    sBody = sBody & " " & Cells(lr, j).Value
  Next
  emailItem.Body = sBody
 
  'emailItem.Attachments.Add ActiveWorkbook.FullName
 
  'emailItem.Display
  emailItem.Send

End Sub

🫡
Thank you! This worked.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top