VBA to send a selection on a sheet in the body of email

[JonSalmon87]

Hi, Im trying to make an email vba with an activeX button to send a table of information.

I have attached a workbook with the example of what im trying to to for reference.

I am trying to send an email which will in the body of the email do the following:

show cell M17
then 1 line down show range m13:n14
then 2 lines down show the range H4:k33.

As you can see with the coding so far, i am typing in cell by cell, but there must be a more efficient way of coding this?



Any help would be greatly appreciated, thank you in advance!

Kind Regards

Jon







My code so far looks like this:

Private Sub CommandButton1_Click()
Dim mainWB As Workbook
Dim SendID
Dim CCID
Dim Subject
Dim Body

Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set mainWB = ActiveWorkbook

SendID = mainWB.Sheets("TRADE").Range("N6").Value
CCID = mainWB.Sheets("TRADE").Range("N7").Value
Subject = "ORDER CONFIRM" & mainWB.Sheets("TRADE").Range("N11").Value
Body = mainWB.Sheets("TRADE").Range("M17").Value & vbNewLine & vbNewLine & mainWB.Sheets("TRADE").Range("H4").Value & mainWB.Sheets("TRADE").Range("I4").Value & mainWB.Sheets("TRADE").Range("J4").Value & mainWB.Sheets("TRADE").Range("K4").Value


With olMail
    .To = SendID
    If CCID <> "" Then
      .CC = CCID
    End If
    .Subject = Subject
    .Body = Body
    .Send

End With
MsgBox ("trade confirm has been sent to " & SendID)
End Sub

[davesexcel]

There is no attachment.
What do you mean by..."As you can see with the coding so far, i am typing in cell by cell, but there must be a more efficient way of coding this?"