Send Email from Excel Button with the Body containing data from the last cell in a range that has data in it

[erinbrooks921]

Hi - I have been searching threads for the answer but can't find exactly what I am trying to do.

My spreadsheet has 4 columns - I want to create a button to send an email that contains the value of the last cell in column D that has data in it.

Example spreadsheet:
Cash Report Date Agency Code 3 Digit Code DC#
5/11/2015 29179 001 29179001
5/12/2015 29179 002 29179002
5/13/2015 29179 003 29179003

I want the email to say "Today's DC# is 29179003".

Can anyone help me with the code? I'm having trouble looking up the value in the last cell with data. This is my code so far:


Private Sub CommandButton1_Click()
On Error GoTo ErrHandler

' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)


Dim strBody As String

'This is the part that doesn't work
Dim WorkRange As Range
Dim rngInput As Range
Dim i As Integer, CellCount As Integer
Dim LastinColumn As String
Application.Volatile

rngInput = "D:D"
WorkRange = rngInput.Columns(1).EntireColumn
WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LastinColumn = WorkRange(i).Value
Debug.Print LastinColumn
'Exit Function
End If
Next i

strBody = "I have completed the Cash Report close. The DC# is: " & LastinColumn



With objEmail
.To = "jbarnes@staffordcountyva.gov; bjanis@staffordcountyva.gov"
.CC = "kbailey@staffordcountyva.gov; kcox@staffordcountyva.gov"
.Subject = "Cash Report Close"
.Body = strBody
.Display ' DISPLAY MESSAGE.
End With

' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing

ErrHandler:
Debug.Print Err And Err.Description


End Sub


Any help would be appreciated.

 

[BarryL]

try

Sub mailmacro()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim lr As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error GoTo endit
    With OutMail
        .to = "jbarnes@staffordcountyva.gov; bjanis@staffordcountyva.gov" 'email address
        .CC = "kbailey@staffordcountyva.gov; kcox@staffordcountyva.gov"
        .BCC = ""
        .Subject = "Cash Report Close"
        .Body = "Todays DC# is" & Range("D" & lr).Value
      
        .display   'or use .send
    End With
  
 
    Set OutMail = Nothing
    Set OutApp = Nothing
    
endit:
End Sub

 

[BarryL]

also id be very wary of putting ppls email addresses on the web. maybe edit your post so they are xxxxx'd out