Email and Range of cells including a picture

JustineM

New Member
Joined
Jun 23, 2017
Messages
8
Hi All,

I'm using the below code to send a range of cells using a command button click, which works as expected, but misses out a picture that I would also like to send:
Code:
 Sub SendSelectedCells_inOutlookEmail()
    Dim objSelection As Excel.Range
    Dim objTempWorkbook As Excel.Workbook
    Dim objTempWorksheet As Excel.Worksheet
    Dim strTempHTMLFile As String
    Dim objTempHTMLFile As Object
    Dim objFileSystem As Object
    Dim objTextStream As Object
    Dim objOutlookApp As Outlook.Application
    Dim objNewEmail As Outlook.MailItem
 
    'Copy the selection
    Set objSelection = Range("A1:F15")
     Range("A1:F15").Select
     
    Selection.Copy
 
    'Paste the copied selected ranges into a temp worksheet
    Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
    Set objTempWorksheet = objTempWorkbook.Sheets(1)
 
    'Keep the values, column widths and formats in pasting
    With objTempWorksheet.Cells(1)
         .PasteSpecial xlPasteValues
         .PasteSpecial xlPasteColumnWidths
         .PasteSpecial xlPasteFormats
    End With
 
    'Save the temp worksheet as a HTML file
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm"
    Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address)
    objTempHTMLFile.Publish (True)
 
    'Create a new email
    Set objOutlookApp = CreateObject("Outlook.Application")
    Set objNewEmail = objOutlookApp.CreateItem(olMailItem)
 
    'Read the HTML file data and insert into the email body
    Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
    objNewEmail.HTMLBody = objTextStream.ReadAll
    objNewEmail.Display
    'You can specify the new email recipients, subjects here using the following lines:
    objNewEmail.To = ""
    objNewEmail.Subject = "Notification of Audit"
    'objNewEmail.Send --> directly send out this email
    
      
    objTextStream.Close
    objTempWorkbook.Close (False)
    objFileSystem.DeleteFile (strTempHTMLFile)
End Sub

Do I need to reference the picture in the code, or is there a way I can embed the picture in excel so it is included in the cell range. I've tried embedding the picture from a file image and cropped it to size, but it still doesn't pick up the picture,

Thanks
Jm,
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,223,628
Messages
6,173,429
Members
452,514
Latest member
cjkelly15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top