We help IT Professionals succeed at work.

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

Hi,

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.
Thanks,
Prashant.


Comment
Watch Question

Implementation Consultant
Commented:
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")
    OutApp.Session.Logon
    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
        
        .Send

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

End Sub

Open in new window

Author

Commented:
Hi..

Thanks a lot.. It works now.

Rgds,
Prashant.