Adding Excel Range To Email Body

**DMT Dave** · 05-18-2022, 12:50 AM

Hi Guys, I have found 2 functions to try and output a specific Excel Sheet to Email Body, watched the video of it and followed apart from renaming my Dims, I am coming up with Sub Or Function not defined ? (RangeToHTML) not defined

I try and use the run option in Function in EmailRange

One final question, when you kind people out there can confirm where i am wrong, I am guessing if I want to call this from a command button, i would need to open the Excel File not visible then run the EmailRange function ?

Code:

Function RangetoHTML(rng As Range)' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    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

Code:

Sub EmailRange()

Dim OutApp As Object, OutMail As Object
Dim iRowCount As Integer, iColCount As Integer
Dim rngSheet As Range
Dim strBody As String, strGreet As String, strExitLine As String, strUser As String, strMsg As String


strGreet = "Hello <br><br>"
strMsg = "DMT Holidays " & Format(Now(), "yyyy") & " has been updated, here is an updated version <br><br>"
strExitLine = "Kind Regards <br><br>"
strUser = Forms!frmMainMenu!txtLogin




Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)


iRowCount = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
iColCount = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))


Set rngSheet = Sheets("PRINT UPDATE").Range(Cells(1, 1), Cells(iRowCount, iColCount))


strBody = "<BODY Style = font-Size:11pt;font-family:Times New Roman>" & _
strGreet & strMsg & "<br>" & strExitLine & "<br>" & strUser


On Error Resume Next
    With OutMail
        .To = "myemailaddress"
        .subject = "Holiday Calendar Update"
        .Display
        .HTMLBody = strBody & RangetoHTML(rngSheet) & strGreet & .HTMLBody
    End With
    
    On Error GoTo 0
    
Set OutMail = Nothing
Set OutApp = Nothing




End Sub

**Welshgasman** · 05-18-2022, 01:19 AM

Depends where you put the function?
If in a code module then you would need to declare it as Public.

No idea about your other code, as you do not have an Excel object?, so all the Excel statements are not going to work?

And yes, hard to work on any file (other than a copy, rename or delete), unless it is open

**DMT Dave** · 05-18-2022, 01:42 AM

Hi WGM, Function RangeToHTML is in a module I have called PublicFunctions

The EmaiLRange is in a module called PublicSubs

If i am reading your input correctly, try changing:
Function RangeToHTML (rng as Range) to:

Public Function RangeToHTML (rng as Range) ?

Same applies from Sub To Public Sub ?

**DMT Dave** · 05-18-2022, 01:46 AM

As far as I am understanding with the copied code from R D Bruin site, doesn't the excel file only a temp file until range is copied and sent to mail body ?

Also do i need to find if it is the open (active worksheet) that is being transferred to mail body ?

please forgive me if I have misunderstood !

**Welshgasman** · 05-18-2022, 01:51 AM

Originally Posted by DMT Dave

Hi WGM, Function RangeToHTML is in a module I have called PublicFunctions

The EmaiLRange is in a module called PublicSubs

If i am reading your input correctly, try changing:
Function RangeToHTML (rng as Range) to:

Public Function RangeToHTML (rng as Range) ?

Same applies from Sub To Public Sub ?

No, for some reason Subs do not need it?, at least I have not used it.

**Welshgasman** · 05-18-2022, 01:55 AM

Originally Posted by DMT Dave

As far as I am understanding with the copied code from R D Bruin site, doesn't the excel file only a temp file until range is copied and sent to mail body ?

Also do i need to find if it is the open (active worksheet) that is being transferred to mail body ?

please forgive me if I have misunderstood !

Yes, but you do not have an Excel object or file open so as to be able to locate a range to pass to that function? You cannot use functions of an object unless that object is defined and set/open.

I will go to my grave saying this

'Walk though your code !!!
That will show errors immediately, and you can react to them, or post back here with the error message and the line it occurs on.

**DMT Dave** · 05-18-2022, 04:06 AM

When i walk through the code this is the result

Click image for larger version.

Name: Capture.JPG
Views: 13
Size: 111.8 KB
ID: 47822

**DMT Dave** · 05-18-2022, 04:15 AM

Also have file open on the correct sheet to answer the the object being open, then once I am able to transfer by this method, i will change the file to open on the correct sheet name and visible false just to tidy the procedure

**Welshgasman** · 05-18-2022, 05:25 AM

So have you made the function Public?
Unless you have global excel objects, I would have thought you would need to open the workbook in that sub, or pass in the objects, none of which you appear to be doing.
You also do not have Option Explcit, what is rngSheet going to hold?

**Micron** · 05-18-2022, 06:59 AM

Standard module variables are public by default: https://docs.microsoft.com/en-us/off...and-visibility

All procedures are public by default, except for event procedures. When Visual Basic creates an event procedure, the Private keyword is automatically inserted before the procedure declaration. For all other procedures, you must explicitly declare the procedure with the Private keyword if you don't want it to be public.

Try removing the ( ) around the rng parameter. All that code is in Excel, yes? If so, I think you should have posted it elsewhere to avoid confusing anyone as to the subject application.
EDIT - You have compiled the code recently?

**Welshgasman** · 05-18-2022, 09:28 AM

I took it to be in Access, due to the subforum it is posted in?

**Welshgasman** · 05-18-2022, 09:43 AM

Works for me in Excel?
Did not make the function Public in Excel either. Sorry about that.

Code:

Sub Test_RangeToHTML()
Dim rng As Range
Set rng = ActiveSheet.Range("A1:C8")
Debug.Print "Test for Range" & vbCrLf & RangeToHTML(rng) & "this is the end"
Set rng = Nothing


End Sub

**DMT Dave** · 05-18-2022, 12:57 PM

Ahh thank you will adapt your suggestions

Adding Excel Range To Email Body

Thread Tools

Adding Excel Range To Email Body

Similar Threads

Adding Excel Cells To Email html body

Adding Image To Email Body

Adding Items To HTML Email Body

Adding a linked reply to email body

Adding query results to email body

Posting Permissions