We help IT Professionals succeed at work.

vb code to copy and paste a table from excel to outlook


I am writing a vb program to send email using outlook. For the outlook contents,
I want to copy a range of cells (or a table) from excel and paste it in outlook using vb code and  send email. How can I do this? I need the vb code to paste the copied table from excel in to outlook without losing the format in excel.
Please help.

Watch Question

Implementation Consultant
Hi prashant06,

I think you have 2 options to archive that:
1. Attach Excel file
2. Use below code to create HTML body with table
Sub SendWorkbooks()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim lr As Long, col As Integer
    Dim i As Long, j As Integer
    Dim sBody As String
    lr = Range("A" & Rows.Count).End(xlUp).Row
    'Change col to reflect number of columns in your table
    col = 5
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = "maksym.szotek@newlook.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Report for date " & Format(Date, "mm/dd/yyyy")
        sBody = "<HTML><BODY><TABLE border=1>"
        For i = 1 To lr
            sBody = sBody & "<tr>"
            For j = 1 To col
                sBody = sBody & "<td>" & Cells(i, j).Value & "</td>"
            Next j
            sBody = sBody & "</tr>"
        Next i
        sBody = sBody & "</TABLE></BODY></HTML>"
        .HTMLBody = sBody

    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Open in new window



Thanks a lot.. It works now.