Emailing a named range via Outlook

[Mr_Doug]

Hi All,

I have a minor (hopefully) issue when trying to email a named range using the code below. The code was borrowed from another post and adapted slightly.

I'm getting a "Compile error: Type mismatch" when trying to amend the sender (I have more than one mailbox on the pc) using;

objNewEmail.Sender = "<email address here>"

Thanks in advance.

Doug

Private Sub cbSend_Click()
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 named range
Range("EmailContent").Select
Set objSelection = Selection
Selection.Copy

' Paste the copied selected ranges into a temp worksheet
Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
Set objTempWorksheet = objTempWorkbook.Sheets(1)

' Maintain the formatting
With objTempWorksheet.Cells(5, 1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
End With

With objTempWorksheet.Cells(1, 1)
.Value = "Dear " & tbRecipientName.Value & ","
End With

With objTempWorksheet.Cells(3, 1)
.Value = "Please find your quotation below"
End With

' Save the temporary 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)

' Open outlook and create a new email message
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.To = tbEmailAddress.Value 'passed in from a textbox
objNewEmail.Subject = "Your subject message here: " & tbQuoteREf.Value
' objNewEmail.Sender = "me@you.com"

objNewEmail.Display

' Housekeeping - Remove the htm file and close the temporary sheet
objTextStream.Close
objTempWorkbook.Close (False)
objFileSystem.DeleteFile (strTempHTMLFile)
objNewEmail.HTMLBody = "<table align=""left"">" & objTextStream.ReadAll & "</table>" & "<p align=""left"">" & objNewEmail.HTMLBody & "</p>"
Set EmailApplication = Nothing
Set EmailItem = Nothing

Unload ufEmailDetails
Range("A1").Select
Range("EmailContent").ClearOutline

End Sub