Command Button - Send as Body not attachment

[zugfrig]

HI,

I came across this code to add a command button which i can press to send my work via email. It works perfectly for the way it was designed, but that is to attach the sheet as an attachment.

Given it's only a few cells of data, I'm wanting to include it in the body of the email, rather than as an attachment.


this is the code (i tried removing the .attachments.add... line but that just sends nothing)


Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
ActiveWorkbook.Save
xMailBody = "Body content" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "Ash.D@freight.com"
.CC = ""
.BCC = ""
.Subject = "Shipment update for " & [B2]
.Body = xMailBody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Display
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[Anthony47]

Remove the ".Attachments.Add" line and add instructions to insert into the "Body" of the mail the necessari information. For example:

xMailBody = "Body content" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
'
'Add more text from the workbook:      (next lines have been added)
xMailBody = xMailBody & vbCrLf & "This is the shipment date: " & Format(Sheets("Sheet1").Range("A2").Value, "dd-mmm-yyyy")
xMailBody = xMailBody & vbCrLf & "This is AWB number: " & Sheets("Sheet1").Range("B2").Value
xMailBody = xMailBody & vbCrLf & "This is the shipment cost: US$ " & Format(Sheets("Sheet1").Range("C2").Value, "0.00")

On Error Resume Next

Make sure to convert to text the info that you wish to insert (or dates will appears as serial numbers, or values could be displayed with 17 decimals, and other silly situations)