I have an application that runs automatically and it drops a synopsis of the sheet into an email. When I manually paste it with keeping source formatting or as picture everything is ok. But I have tried it 2 different ways with VBA and have issues with both. I don't really care which one I go with, I just want it issue free. Thank you for any help
The current way, Pastes it as a picture, but the picture is fuzzy.
The previous way, but the table changes with the size of the email window, causing the text to wrap, just look terrible overall.
The current way, Pastes it as a picture, but the picture is fuzzy.
Code:
Sub SendEmail()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Dim EmailList As String
Dim OutApp As Object
Dim OutMail As Object
'Set range for the body of email
Set rng = Nothing
On Error Resume Next
Set rng = Sheets("Positions").Range("A9:AF47").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
'Set up range of distribution list
For Each cell In ThisWorkbook.Sheets("Lookups").Range("L7:L15")
If cell.Value Like "?*@?*.?*" Then
EmailList = EmailList & cell.Value & ";"
End If
Next cell
If Len(EmailList) > 0 Then EmailList = Left(EmailList, Len(EmailList) - 1)
'Turns off screen Updating
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Saves workbook to send correct workbook in email
ThisWorkbook.Save
'Creates Email Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Send Email
On Error Resume Next
With OutMail
.To = EmailList
.Subject = Month(Date) & "/" & Day(Date) & " - " & "East Basis Positions"
'.TMLBody = RangetoHTML(rng)
'New section
.TMLBody = "<span LANG=EN>" _
& "<p class=style2>span LANG=EN>font FACE=Calibri SIZE=3>" _
& "Attached is the Daily NE Basis Positions and PNL," _
& "<BR"
'first we create the image as a JPG file
Call CreateJPG("Positions", "A9:AF47", "DashboardFile")
'we attached the embedded image with a Position at 0 (makes the attachment hidden)
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue, 0
'Then we add an html <img src=''> link to this image
'Note than you can customize width and height - not mandatory
.TMLBody = .TMLBody & "<br<BDaily Report:</B<br" _
& "<img src='cid:DashboardFile.jpg'" & "width='1961' height='650'<br" _
& "<brThank You,<brJ</font</span"
'End of Section
.Attachments.Add Workbooks((Format(Date, "yyyymmdd") & " - EastBasisPositions.xlsx")).FullName
.Display
End With
Workbooks((Format(Date, "yyyymmdd") & " - EastBasisPositions.xlsx")).Close
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Windows("BasisPositionsEast.xlsm").Activate
Sheets("Positions").Select
Sheets("Positions").Range("M4").UnMerge
Sheets("Positions").Range("M4").ClearContents
Sheets("Positions").Range("M4").Value = "=now()"
Sheets("Positions").Range("M4").Select
Selection.Copy
With Selection
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
Application.CutCopyMode = False
Sheets("Positions").Range("M4:O4").Merge
Sheets("Positions").Range("O6").Select
End Sub
Sub CreateJPG(namesheet As String, nameRange As String, nameFile As String)
ThisWorkbook.Activate
Worksheets(namesheet).Activate
Set Plage = ThisWorkbook.Worksheets(namesheet).Range(nameRange).SpecialCells(xlCellTypeVisible)
Plage.CopyPicture
With ThisWorkbook.Worksheets(namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
.Activate
.Chart.Paste
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
Worksheets(namesheet).ChartObjects(Worksheets(namesheet).ChartObjects.Count).Delete
Set Plage = Nothing
End SubThe previous way, but the table changes with the size of the email window, causing the text to wrap, just look terrible overall.
Code:
Sub SendEmail()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Dim EmailList As String
Dim OutApp As Object
Dim OutMail As Object
'Set range for the body of email
Set rng = Nothing
On Error Resume Next
Set rng = Sheets("Positions").Range("A9:AF47").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
'Set up range of distribution list
For Each cell In ThisWorkbook.Sheets("Lookups").Range("L7:L15")
If cell.Value Like "?*@?*.?*" Then
EmailList = EmailList & cell.Value & ";"
End If
Next cell
If Len(EmailList) > 0 Then EmailList = Left(EmailList, Len(EmailList) - 1)
'Turns off screen Updating
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Saves workbook to send correct workbook in email
ThisWorkbook.Save
'Creates Email Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Send Email
On Error Resume Next
With OutMail
.To = EmailList
.Subject = Month(Date) & "/" & Day(Date) & " - " & "East Basis Positions"
.HTMLBody = RangetoHTML(rng)
.Attachments.Add Workbooks((Format(Date, "yyyymmdd") & " - EastBasisPositions.xlsx")).FullName
.Display
End With
Workbooks((Format(Date, "yyyymmdd") & " - EastBasisPositions.xlsx")).Close
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Windows("BasisPositionsEast.xlsm").Activate
Sheets("Positions").Select
Sheets("Positions").Range("M4").UnMerge
Sheets("Positions").Range("M4").ClearContents
Sheets("Positions").Range("M4").Value = "=now()"
Sheets("Positions").Range("M4").Select
Selection.Copy
With Selection
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
Application.CutCopyMode = False
Sheets("Positions").Range("M4:O4").Merge
Sheets("Positions").Range("O6").Select
End Sub
Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")
'Close TempWB
TempWB.Close savechanges:=False
'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
Last edited:
