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

user_9870211 0
Viewed 47

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:

  1. .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
  1. 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.

vba excel html
1 Answers

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 = "abc@xyz.com"
    .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 = "abc@xyz.com"
    .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).

user_98702110
Related