Copy table to email

[agibsonsw]

Hello. Outlook and Excel 2007.

I'm automating Outlook from Excel to send an HTML email. How can I copy an Excel range into the body of the email?
Can I Display the email and somehow paste the clipboard content into the body of the email?
I believe it might be possible to re-construct the Excel data as HTML but this seems like a huge task.
Is there a third way?
Thanks for any advice. Andy

(A smaller question: Is it possible to view the source of an HTML email? I'm constructing an email and hoped
that if I right-clicked I would have a 'View Source' option.)

[VegasNath]

... I'm automating Outlook from Excel to send an HTML email. How can I copy an Excel range into the body of the email?
Can I Display the email and somehow paste the clipboard content into the body of the email?
I believe it might be possible to re-construct the Excel data as HTML but this seems like a huge task.

In my limited experience of automatically generating outlook emails from excel, I have found that it is better to recreate the data in a HTML table within the outlook body from the excel data. I have never found a tidy way of using paste, the column widths and row heights end up in a mess.

[agibsonsw]

Thank you for your response.
I DO believe you! But would like to try pasting it before creating a (slightly) complicated procedure to convert
a range into TD and TR HTML tags within the body.
When I Display the email is it possible to use SendKeys somehow to navigate to the body and paste?
Andy.

[agibsonsw]

I created a function to convert a range to HTML and thought I'd show it in case anyone is interested.

Code: Select all

Function BuildTbl(rngT As Range) As String
    Dim rng As Range
    Dim strTbl As String
    
    strTbl = "<table><tr>"
    For Each rng In rngT
        strTbl = strTbl & "<td>" & rng.Value & "</td>"
        If rng.Column = 4 Then
            strTbl = strTbl & "</tr><tr>"
        End If
    Next rng
    strTbl = Left(strTbl, Len(strTbl) - 4) 'remove the last <tr>
    strTbl = strTbl & "</table>"
    BuildTbl = strTbl
End Function

'Tools/ References Microsoft Outlook 12.0 Object Library
Sub SendOLookEMail()
    Dim ol As Outlook.Application
    Dim mi As Outlook.MailItem
    Dim strBody As String
    
    Set ol = New Outlook.Application
    Set mi = ol.CreateItem(olMailItem)
    With mi
        .BodyFormat = olFormatHTML
        .Display
        .To = "bob.bones@somewhere.com"
        .Subject = "My great table"
        .HTMLBody = "<p>Here is the data you need:</p><p />"
        .HTMLBody = .HTMLBody & BuildTbl(Range("A1:D4"))
        'Stop - to pause execution
        .Send
    End With
    Set mi = Nothing
    Set ol = Nothing
End Sub

It would be fairly straightforward to amend the function to specify widths, align numbers to the right, add a pound
sign to a column. (The function will need to be modified to deal with different numbers of columns.)

Andy.

[agibsonsw]

The following version works regardless of the number of columns in the table range:

Code: Select all

The following version will work for any number of columns in the table range:

Function BuildTbl(rngT As Range) As String
    Dim rng As Range
    Dim strTbl As String
    Dim intLastCol
    
    intLastCol = rngT.Columns.Count + rngT.Column - 1
    strTbl = "<table><tr>"
    For Each rng In rngT
        strTbl = strTbl & "<td>" & rng.Value & "</td>"
        If rng.Column = intLastColThen
            strTbl = strTbl & "</tr><tr>"
        End If
    Next rng
    strTbl = Left(strTbl, Len(strTbl) - 4) 'remove the last <tr>
    strTbl = strTbl & "</table>"
    BuildTbl = strTbl
End Function

'Tools/ References Microsoft Outlook 12.0 Object Library
Sub SendOLookEMail()
    Dim ol As Outlook.Application
    Dim mi As Outlook.MailItem
    Dim strBody As String
    
    Set ol = New Outlook.Application
    Set mi = ol.CreateItem(olMailItem)
    With mi
        .BodyFormat = olFormatHTML
        .Display
        .To = "bob.bones@somewhere.com"
        .Subject = "My great table"
        .HTMLBody = "<p>Here is the data you need:</p><p />"
        .HTMLBody = .HTMLBody & BuildTbl(Range("c1").CurrentRegion)
        Stop    '- to pause execution
        .Send
    End With
    Set mi = Nothing
    Set ol = Nothing
End Sub

[VegasNath]

Thanks Andy, that is very useful for me. Shame this does not retain the formatting, maybe a-n-other lounger may chime in with further options.

[agibsonsw]

Hello.
It depends on the formatting you want to keep. If, for example a cell is bold, this could be replicated with:

If rng.Font.Bold = True Then
strTbl = strTbl & "<b>" & rng.Value & "</b>"
Else
strTbl = strTbl & rng.Value
End If

Andy.