Excel vba email to show cell data that is in last non-blank row.

[jeffdolton]

Hi, I'm looking for a macro please. I've searched everywhere for this, so your help would be really appreciated!

I wish to generate an email from a worksheet with data appearing in the body text that comes from specific cells in the last blank row.

For instance, from the last row below, I would like the second, fifth and 6th cells to appear in the body text. It's important that the data is taken from the last row.

26/12/2020

2020EN006

Closed

Engines

Safety 3

Propeller damage

25/12/2020

Jeff Dolton

Many thanks for your help.

 

[ranman256]

Sub SendLastRec()
Dim vCode, vFld2, vFld5, vFld6
Dim vTo, vSubj, vBody

  'goto last row
Range("A1").Select
Selection.End(xlDown).Select

'build email
vFld2 = ActiveCell.Offset(0, 1).Value 'col 2
vFld5 = ActiveCell.Offset(0, 4).Value 'col 5
vFld6 = ActiveCell.Offset(0, 5).Value    'col 6
vBody = "fld2=" & vFld2 & vbCrLf
vBody = vBody & "fld5=" & vFld5 & vbCrLf
vBody = vBody & "fld6=" & vFld6 & vbCrLf

vTo = "bob@acme.com"   'or range("A7").value
vSubj = "your data"

'send email
Send1Email vTo, vSubj, vBody
End Sub


'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!! checkmark OUTLOOK OBJECTS in the vbE menu, Tools, References
'-------
private Function Send1Email(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
With oMail
.To = pvTo
.Subject = pvSubj
.HTMLBody = pvBody
'.Body = pvBody

    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1

.Display True
'or
'.Send
End With
Send1Email = True
Set oMail = Nothing
Set oApp = Nothing
Exit Function
ErrMail:
MsgBox Err.Description, vbCritical, Err
'Resume Next
End Function