CODEWITHSUNDEEP
vbaexcelemailoutlook
Tom
Tom

Reputation: 65

Put the last row range text in Outlook body

I would like to send an email from my Excel spreadsheet with a message in the body containing the last row range from column A to column G.

I tried to add my last row range to the following code.

Sub Mail_LastRowRange_Outlook()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Thanks for your help" & '**I Would like to insert_
      my last row range from column A to G in here**

    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Looking for a solution"
        .Body = strbody
        .Attachments.Add ("C:\test.txt")
        .Send   
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Upvotes: 2

Views: 1546

Answers (3)

MacroMarc
MacroMarc

Reputation: 3324

Sub test1()
  Dim lastRow As Long
  'get last row from column A of worksheet with 'codename' Sheet1
  lastRow = getLastRow(Sheet1, 1)

  Dim r As Range
  Set r = Sheet1.Range("A" & lastRow & ":G" & lastRow)

  Dim str As String
  'Join the cell texts with a space - lazy coding...
  str = Join(Application.Transpose(Application.Transpose(r.Value2)), " ")

  MsgBox str
End Sub


Public Function getLastRow(ws As Worksheet, Optional col As Long) As Long
  Dim arr As Variant

  If col > 0 Then
        arr = Intersect(ws.UsedRange, ws.Columns(col)).Value2
  Else
        arr = ws.UsedRange.Value2
  End If

  Dim i As Long, j As Long
  For i = UBound(arr) To 1 Step -1
        For j = UBound(arr, 2) To 1 Step -1
              If Len(arr(i, j)) > 0 Then
                    getLastRow = i + ws.UsedRange.Row - 1
                    Exit Function
              End If
        Next j
  Next i
End Function

The function above is THE most robust function to get the last row of actual data value in a worksheet/column. Everything else is vulnerable including Range.Find("*", , , , , xlPrevious) which is vulnerable to activeCell in filtered ListObject

The function below is susceptible to a couple things like filtered rows, last row having data, etc etc.

Public Function getLastRow2(ws As Worksheet, col As Long) As Long
    getLastRow2 = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Upvotes: 1

user2529170
user2529170

Reputation: 71

This should work, as long as there are no blank cells in column A from cell A1 through the last row with data in column A. Replace the line that you have "strbody = ...." with all of this.

Cells(1,1).Select
Selection.End(xlDown).Select

Dim s As String
s = ActiveCell.Value     'A
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'B
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'C
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'D
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'E
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'F
ActiveCell.Offset(0, 1).Select
s = s & ActiveCell.Value 'G

strbody = "Thanks for your help" & s

Upvotes: 0

user8355222
user8355222

Reputation: 51

determine the specific row, and create a range object and iterate over it, e.g.:

    Dim rng As range
    Dim item As range
    Dim row As Long

    Set rng = range(Cells(row, 1), Cells(row, 7))

    For Each item In rng
        strBody = strBody & " " & item
    Next item

Upvotes: 0

Related Questions