Copy from Excel to Outlook - white cells behind topleft corner of image

Question

I am copying excel content from Excel to the Outlook Email Body, and unlike many other users my problem is not that cell formatting is messed up, but rather that when copying any of the images from excel, they are pasted into outlook with a white cell behind the image's top left corner.

Attached a sample image with how it looks in Excel and then in Outlook.

I have spent hours trying to solve this including:

• change the format of the image (PNG/JPG/BMP)
• play around with resolution, image compression, move-and-size-with-cells option, etc.
• tried to manually copy the range into the outlook email body VS copying it via a macro which converts the worksheet-range into a html string and generates the email directly from excel

The last option, where I used VBA to generate the email from excel directly, exhibits the exact some problem as a manual copy & paste from excel. The problem only occurs around the area of images and the cells that are touched by the topleft corner of the image, no matter if those cells are regular cells or merged.

This leads me to believe that this is either an issue with my images/object (I replaced all images with shapes and had the same issue, or its bug in MS Office in when it comes to handling the copy & paste process of content with objects.

Would love to hear some opinions of you guys, I am at the end of my wit.

Thank you!

Also, here the function I use for copying the range to outlook.

Excel VBA

Option Explicit

Private Function RngToEmail(rng As Range, eTo As String, eSubject As String)
    Dim wbThis As Workbook, wbNew As Workbook
    Dim tempFileName As String, imgName As String, newPath As String

    '~~> Do not change "Myimg". This will be used to
    '~~> identify the images
    Dim imgPrefix As String: imgPrefix = "Myimg"

    '~~> This is the temp html file name.
    '~~> Do not change this as when you publish the
    '~~> html file, it will create a folder Temp_files
    '~~> to store the images
    Dim tmpFile As String: tmpFile = "Temp.Htm"

    Set wbThis = Workbooks(rng.Parent.Parent.Name)
    Set wbNew = Workbooks.Add

    '~~> Copy the relevant range to new workbook
    rng.Copy wbNew.Worksheets("Sheet1").Range("A:A")

    newPath = wbThis.Path & "\"
    tempFileName = newPath & tmpFile

    '~~> Publish the image
    With wbNew.PublishObjects.Add(xlSourceRange, _
        tempFileName, "Sheet1", rng.Address, xlHtmlStatic, _
        imgPrefix, "")
        .Publish (True)
        .AutoRepublish = True
    End With

    '~~> Close the new file without saving
    wbNew.Close (False)

    '~~> Read the html file in a string in one go
    Dim MyData As String, strData() As String
    Dim i As Long
    Open tempFileName For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    '~~> Loop through the file
    For i = LBound(strData) To UBound(strData)
        '~~> Here we will first get the image names
        If InStr(1, strData(i), "Myimg_", vbTextCompare) And InStr(1, strData(i), ".Png", vbTextCompare) Then
            '~~> Insert actual path to the images
            strData(i) = Replace(strData(i), "Temp_files/", newPath & "Temp_files\")
        End If
    Next i

    '~~> Rejoin to get the new html string
    MyData = Join(strData, vbCrLf)

    '~~> Create the Email
    Dim OutApp As Object, OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .to = eTo
        .Subject = eSubject

        '~~> Set the body
        .HTMLBody = MyData

        '~~> Show the email. Change it to `.Send` to send it
        .Display
    End With

    '~~> Delete the temp file name
    Kill tempFileName
End Function

Sub Sample()
    RngToEmail ThisWorkbook.Sheets("FINAL").Range("A:F"), "[e-mail]", "Some Subject"
End Sub

Answer 1

Do you insert picture to this cell? When testing on my Outlook 2016 version 1812, I also find the issue. The cell behind the picture would appear blank or wired when pasting to email body. I haven’t found related official articles and I’m not familiar with VBA code.

However, we can choose to paste as a picture in Paste Options to avoid this.