Send email through VBA.

[PCTech]

I have a setup that works if a user has outlook open one their computer. It will copy a range from Sheet2 and put it in the body of the email.
I want to change my macro to use this method Use CDO to send email from VBScript or VBA through Office 365
That works great until I change .HTMLBody = "Test Message" to .HTMLBody = strHtml & RangetoHTML(rng)
I get Compile Error: ByRef argument type mismatch and (rng) is highlighted.
Can someone tell me what I'm doing wrong or what I need to change?
Thanks in advance.

Here is the reference range.

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

 

[Dan_W]

In whichever routine you are calling this function from (the one containing .HTMLBody = strHtml & RangetoHTML(rng)), you need to first dimension the RNG variable:

Dim rng As Range

Try that and please let me know if it works.

 

[PCTech]

Thank you, Thank you, Thank you, That work great.

 

[Dan_W]

You're very welcome.