I don't visit this forum very often. Normally, I integrate Excel with Outlook and Word. Your code could be involved so a quick response can't be expected.
For something like that, rather than htmlBody, I would use Outlook's WordEditor method with Word's Paste feature. I don't have time to fully flesh out a specific example. See the end for a WordEditor code example. It would be easier to help if you had attached a simple example XLSM file with the objects.
Normally, we like to see code pasted between Code tags. Click the # icon in the reply toolbar to insert the tags. e.g.
Private Sub Application_Reminder(ByVal Item As Object)
Dim objPeriodicalMail As MailItem
If Item.Class = olTask Then
If InStr(LCase(Item.Subject), "send an email periodically") Then
Set objPeriodicalMail = Outlook.Application.CreateItem(olMailItem)
With objPeriodicalMail
.Subject = "Bla Bla"
.To = "mail@mail.com"
.CC = " mail@mail.com "
.HTMLBody = "<HTML><BODY>" _
& "Bla Bla" & "<br>" & "<br>" _
& "Bla Bla" & "<br>" & "<br>" _
& "Bla Bla</HTML></BODY>"
.Attachments.Add ("c:\folder\file.xlsm")
.Importance = olImportanceHigh
.ReadReceiptRequested = True
.Send
End With
End If
End If
End Sub
Pasting can be a bit tricky depending on the objects that are copied and pasted to WordEditor. Here is an Excel VBA example that shows the concept:
'https://www.mrexcel.com/forum/excel-questions/1029977-how-can-i-attach-object-file-attached-worksheet-email.htmlSub Main()
Dim S$, T$, sig$, ole$
'Tools > References > Microsoft Outlook xx.0 Object Library > OK
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
'Tools > References > Microsoft Word xx.0 Object Library > OK
Dim Word As Document, wr As Word.Range, rTo As Recipient, wos As Word.Selection
'INPUTS to change if needed...........................................................
S = "Hello World Example" 'Subject
T = "ken@gmail.com" 'To
ole = "oleFile1" 'OLE object's name.
sig = ThisWorkbook.Path & "\sig.rtf" 'contents to copy for signature.
'End INPUTS...........................................................................
'Get Outlook application
Set olApp = New Outlook.Application
'Make email, send/display.
Set olMail = olApp.CreateItem(olMailItem)
With olMail 'olApp.CreateItem(olMailItem)
.Subject = S
.Importance = olImportanceNormal
'Set the recipient(s) for To field and resolve.
Set rTo = .Recipients.Add(T)
rTo.Resolve
rTo.Type = olTo 'olTo, olcc, olbcc
If rTo.Resolved = False Then
Debug.Print T & "email address: Resolved=False"
'.To = T 'Using rTo above for .To instead.
GoTo TheEnd
End If
'Setup WordEditor parts:
.GetInspector.Display
Set Word = .GetInspector.WordEditor
Set wr = Word.Range
wr.Font.Name = "Arial"
wr.Font.Size = 18
'Body, introductory text:
'wr.typetext "..."
wr.Text = "Dear VBA Enthusiast, " & vbCrLf & vbCrLf & _
"I hope that you find this example of copied Excel Range " _
& "and embedded OLEObject using WordEditor in Outlook " _
& "useful." & String(2, vbCrLf)
'Body, range A1, copy/paste:
Set wos = Word.Windows(1).Selection
Sheet1.Range("A1").CopyPicture xlScreen, xlPicture
wr.Collapse Direction:=wdCollapseEnd
'Word.Range(End:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
wr.Paste
wr.Collapse Direction:=wdCollapseEnd
Word.Range.InsertAfter String(2, vbCrLf)
'Word.Selection.EndKey Unit:=wdStory
'Selection.InsertBreak Type:=wdPageBreak
'word.Selection.Paste
'Body, copy/paste OLEObject
Word.Range.InsertAfter " " & String(2, vbCrLf)
wr.Collapse Direction:=wdCollapseEnd
'Word.Range(Start:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
Sheet1.OLEObjects(ole).Copy
olMail.GetInspector.Display
Sheet1.[A1].Select
.GetInspector.Display
wr.PasteSpecial Placement:=wdInLine, DataType:=wdPasteOLEObject
'Body, copy/paste contents of sig.rtf, signature...
'Adjust String()'s vbCrLf number to be below the oldObject due to its size.
wr.InsertParagraphAfter
wr.InsertBreak Type:=wdSectionBreakNextPage
wr.InsertAfter String(22, vbCrLf)
wr.Collapse Direction:=wdCollapseEnd
'https://msdn.microsoft.com/en-us/vba/word-vba/articles/selection-insertfile-method-word
wr.InsertFile sig, , , False, False
'Word.Range(Start:=Word.Content.End - 2).PasteAndFormat wdPasteDefault
'GetObject(sig).Range.Copy
'wr.PasteSpecial Placement:=wdInLine, DataType:=wdPasteRTF
'wr.Paste
'.Attachments.Add e 'e is the full path to a file.
.Display
'.Send
End With
TheEnd:
Set olMail = Nothing
Set olApp = Nothing
End Sub