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:
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,
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,