CODEWITHSUNDEEP
excelvbaoutlook
Shira L.
Shira L.

Reputation: 81

Add Text & Excel table as picture to email

How do I add both text & Excel table (as a picture) to Outlook mail?

I don't want to use the RangetoHTML1 function since it doesn't save the conditional formatting.

In the code below, the table is copied as picture to the mail. How do I add the text?

Sub SendCA_list()
    Dim oApp As Object, oMail As Object, rng As Range, p As Picture
    Dim strBody As String, wordDoc As Object

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(olMailItem)
    Set rng = Range("Table4[[#All],[Department]:[Status]]")
    rng.Copy  'copy required table
    Set p = ActiveSheet.Pictures.Paste 'paste and cut the table to make picture
    p.Cut

    With oMail
        .Subject = "Request for CAs - ISO Audit"
        strBody = "<BODY style='font-size:12pt;font-family:HP Simplified'>" & _
            "Hi,<br><br>Please see attached report and the open " & _
            "AIs (itable below).<br><br>Best Regards,<br>Shira" & "</BODY>"
        .HTMLBody = strBody
        Set wordDoc = oMail.GetInspector.WordEditor
        wordDoc.Range.Paste
        .Display
    End With
End Sub

Upvotes: 1

Views: 1889

Answers (2)

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

Ok, this is another way to approach the problem which exports the image and then attaches in with the HTML referring to the attached image. 

Sub SendCA_list()

    Dim oApp As Object
    Set oApp = CreateObject("Outlook.Application")

    Dim oMail As Object
    Set oMail = oApp.CreateItem(0)

    Dim rng As Range
    Set rng = Sheet1.Range("Table4[[#All],[Department]:[Status]]")
    rng.CopyPicture xlPrinter

    Dim chartObj As ChartObject
    Set chartObj = Sheet1.ChartObjects.Add(0, 0, rng.Width, rng.Height)

    chartObj.Select
    chartObj.Chart.Paste
    chartObj.Chart.Export ThisWorkbook.Path & "\table.png", "png"
    chartObj.Delete

    Dim strbody As String
    strbody = "<BODY style='font-size:12pt;font-family:HP Simplified'>" & "Hi,<br><br>Please see attached the ISO Internal Audit Report and the open AIs (in the table below)."
    strbody = strbody & "<br><img src='cid:table.png'/>"
    strbody = strbody & "<br><br>Best Regards,<br>Shira<br><br></BODY>"

    With oMail

        .Subject = "Request for CAs - ISO Audit"
        .Attachments.Add ThisWorkbook.Path & "\table.png", 1, 0
        .htmlbody = strbody
        .Display
    End With
End Sub

Upvotes: 1

Diederik Sieburgh
Diederik Sieburgh

Reputation: 111

What is currently happening in your code is the Paste command of the picture is deleting the HTML already in body of the email. If you paste first and then amend the current HTMLbody (the image) to your String HTML you will get the desired results. Note: you may need to play around with this further to meet your exact needs of formatting the email.

Edit: I have included the full Sub now below. I have also added .display before appending your BODY string to the pasted image. Between testing and posting I had removed the .display but later realized this is needed to reference the .htmlbody value.

Sub SendCA_list()

    Dim oApp As Object
    Set oApp = CreateObject("Outlook.Application")
    Dim oMail As Object

    Set oMail = oApp.CreateItem(0)

    Dim rng As Range
    Set rng = Range("Table4[[#All],[Department]:[Status]]")
    rng.Copy  'copy required table

    Dim p As Picture
    Set p = ActiveSheet.Pictures.Paste 'paste and cut the table to make it picture
    p.Cut

    Dim strbody As String

    With oMail

       .Subject = "Request for CAs - ISO Audit"
        strbody = "<BODY style='font-size:12pt;font-family:HP Simplified'>" & "Hi,<br><br>Please see attached the ISO Internal Audit Report and the open AIs (in the table below).<br><br>Best Regards,<br>Shira<br><br></BODY>"

        Dim wordDoc As Object
        Set wordDoc = oMail.GetInspector.WordEditor

        wordDoc.Range.Paste
        .Display
        .htmlbody = strbody & .htmlbody

    End With
End Sub

Upvotes: 0

Related Questions