Command Button - Send as Body not attachment

zugfrig

Board Regular
Joined
Jun 3, 2009
Messages
64
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Remove the ".Attachments.Add" line and add instructions to insert into the "Body" of the mail the necessari information. For example:
VBA Code:
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)
 
Upvote 0

Forum statistics

Threads
1,224,813
Messages
6,181,106
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top