PDA

View Full Version : Using VBA to paste an Excel sheet as an image and keep conditional formatting



myles8908
12-10-2014, 08:39 AM
So I have an excel sheet at work that I send out every 3 hours for downtime reports on machines. I am attaching an image to the email and that shows the conditional formatting of the table just fine, but if I paste the image into the body of the email it takes away the conditional formatting. (All using VBA of course)

Any solutions to why this happens or what could be done to prevent the conditional formatting from being taken away?

Thanks

Blade Hunter
12-10-2014, 07:34 PM
How are you copying it? Are you using this?



Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

myles8908
12-11-2014, 05:16 AM
Sub EmailRangeInHTML(ByVal Recipient As String, ByVal Subject As String, ByRef Range_To_Send As Range) Dim Bytedata() As Byte
Dim HTMLcode As String
Dim HTMLfile As Object
Dim olApp As Object
Dim TempFile As String
Dim Wks As Worksheet

' Copy the worksheet to create a new workbook
Set Wks = Range_To_Send.Parent

' The new workbook will be saved to the user's Temp directoy
TempFile = Environ("Temp") & "\Temp Email.htm"

' Start Outlook
Set olApp = CreateObject("Outlook.Application")

' Convert the Message worksheet into an HTML file.
With Wks.Parent.PublishObjects
.Add(SourceType:=xlSourceRange, _
Filename:=TempFile, Sheet:=Wks.Name, _
Source:=Range_To_Send.Address, HtmlType:=xlHtmlStatic) _
.Publish Create:=True
End With

' Read the HTML file back as a string.
Open TempFile For Binary Access Read As #1
ReDim Bytedata(LOF(1))
Get #1, , Bytedata
Close #1

HTMLcode = StrConv(Bytedata, vbUnicode)

' Re-align the HTML code to the left side of the web page.
HTMLcode = VBA.Replace(HTMLcode, "align=center x:publishsource=", "align=left x:publishsource=")

' Activate the mail inspector. This must be done in Outlook 2010 and later to use Send.
olApp.Session.getdefaultFolder 6

' Compose and send the email.
With olApp.CreateItem(olMailItem)
.To = Recipient
.Subject = Subject
.BodyFormat = 2 ' HTML
.HTMLBody = HTMLcode
.Send
End With

Kill TempFile
Wks.Parent.PublishObjects.Delete

End Sub



Sub Email()
EmailRangeInHTML "myles at email. com", "HTML Range Test", Range("A1:AG64")
End Sub


This is what I have and it works! I just want it to send from donotreply at email.com . Any suggestions??