VBA last row value

[thardin17]

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

 

[MountainFog]

Can you give a sample dataset? The value of a single cell, or the entire row? Including headers or not?

 

[DanteAmor]

To take the value from column A:

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

 

[thardin17]

Can you give a sample dataset? The value of a single cell, or the entire row? Including headers or not?

I would like to return all the data in that row for all columns that have data in them.

 

[thardin17]

To take the value from column A:

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

 

[DanteAmor]

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:

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

 

[thardin17]

Assuming you have data in column A.
Try this:

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.