Add tables of excel to mail body using macros

A great question!

To add tables from Excel to the body of an email using VBA macros, you can follow these steps:

Step 1: Create a new Excel macro

1. Open your Excel file and go to the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.

2. In the Visual Basic Editor, click Insert > Module to insert a new module.

3. Paste the following code into the module:

   Sub SendEmailWithTable()
    Dim olApp As Object
    Dim olMail As Object
    Dim table As Range
    Dim tableHTML As String
   
    ' Set up the email application
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' 0 = email
   
    ' Set the email properties
    With olMail
       .To = "[email protected]"
       .Subject = "Test Email with Table"
       .Body = ""
    End With
   
    ' Select the table in Excel
    Set table = Range("A1:E5") ' adjust the range to your table
   
    ' Convert the table to HTML
    tableHTML = "<table>"
    For Each cell In table
        tableHTML = tableHTML & "<tr>"
        For Each column In table.Columns
            tableHTML = tableHTML & "<td>" & cell.Offset(0, column.Column - 1).Value & "</td>"
        Next column
        tableHTML = tableHTML & "</tr>"
    Next cell
    tableHTML = tableHTML & "</table>"
   
    ' Add the HTML table to the email body
    olMail.Body = olMail.Body & tableHTML
   
    ' Send the email
    olMail.Send
   End Sub

   Step 2: Run the macro

4. Save the module by clicking File > Save (or press Ctrl + S).

5. Go back to your Excel file and click Developer > Macros in the ribbon.

6. Select the SendEmailWithTable macro and click Run.

Step 3: Configure the email settings

1. In the SendEmailWithTable macro, update the To property with the recipient's email address.
2. Update the Subject property with the desired subject line.
3. Adjust the Range object to select the table you want to send.

How it works

The macro uses the Outlook Object Library to create a new email object and set its properties. It then selects the table in Excel using the Range object and converts it to HTML using a loop. The HTML table is then added to the email body using the Body property. Finally, the email is sent using the Send method.

Note: Make sure you have Outlook installed and configured on your system for this macro to work. Also, be aware that this macro sends an email using the default Outlook profile, so make sure you have the correct profile set up.