How to save Excel sheet as HTML but keeping correct image source to web (not local)-CodePudding

I have several images in a single sheet workbook. These images are loaded programmatically using their web URL.

I am trying to generate an email with the HTML body as the sheet. However, when I am saving the sheet as HTML it seems to convert the image source from web to local.

I have tried 2 approaches (both found on web), namely:

.PublishObjects.Add method

With wbTemp.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=htmlFilePath, _
     Sheet:=wbTemp.Sheets(1).Name, _
     Source:=wbTemp.Sheets(1).UsedRange.Address, _
     HtmlType:=xlHtmlStatic)
     .Publish (True)
End With

Saving down the temp workbook as HTML

wbTemp.SaveAs Filename:=htmlFilePath, FileFormat:=xlHtml

In both instances, if I open the HTML file in my brower, it loads perfectly. However, when the HTML gets loaded to the email body (.HTMLBody) I get different results. In 1, the HTML loads, however, I get broken images with error: The picture can't be displayed when I display the email. In method 2 I get the error: This page uses frames, but your browser doesn't support them. when displaying it.

Questions:
A. Is there a way to export/save down a sheet as HTML keeping the file web source?
B. If not A, is there a way to alter the HTML generated by either 1 or 2 to set the source to the correct web URL? (there are approx 7 images).

I am using Office 365.

UPDATE 1

If I manually open the HTML file generated in my browser, and then copy and paste that into an Outlook email body all loads fine..

UPDATE 2

If I attach the HTML file generated from 1, to a new email As Text, it loads fine as well. But somehow, when programmatically loading there is corruption occurring.

CodePudding user response：

All, you will NOT believe (maybe you will given it's Excel/VBA) the solution I found for this.

It seems like if I add the attachment to the email AFTER setting the HTML body, it seems to corrupt it. I.e.

With emailItem
    .To = configArr(y, EMAILDL_COL)
    .ReplyRecipientNames = "[email protected]"
    .Subject = configArr(y, EMAILSUBJ_COL)
    .htmlBody = htmlEmailBody
    .Attachments.Add wbToMail.FullName
    .Display
End With

However, if I add the attachment BEFORE adding the HTML body, it works! Genius!

With emailItem
    .To = configArr(y, EMAILDL_COL)
    .ReplyRecipientNames = "[email protected]"
    .Subject = configArr(y, EMAILSUBJ_COL)
    .Attachments.Add wbToMail.FullName
    .htmlBody = htmlEmailBody
    .Display
End With

This is also ONLY after using method 1 above (method 2 still does not work).