Press button, send email

I need code that essentially copies a cell range (A1:I20) and pastes it as body of an email, as if I had copied and hit control v in the body myself.
I've seen numerous links but I can't seem to make it work.
Also, the title will always be "Attendance" and a date referencing from a cell on a different tab (Attendance 2/3/18, for example)
The button is on a different tab ("Forms") than the target cells. The target cells to copy will be on a hidden tab ("Percap"). Date is on "Cover"

https://www.rondebruin.nl/win/s1/outlook/mail.htm

I've tried that before. It tells me selection is not a range or the sheet is protected. I set the range to A1:I20 and the sheet is not protected. Here is my code:

Sub Mail_Selection_Range_Outlook_Body()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    Set rng = Sheets("Face and Tattoo").Range("A1:I20").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

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

    On Error Resume Next
    With OutMail
        .To = "piercashroom@ldry.com"
        .CC = ""
        .BCC = ""
        .Subject = "Tattoo/Facepaint" And ThisWorkbook.Sheets("PerCap").Range("I4").Value
        .HTMLBody = RangetoHTML(rng)
        .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


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

.

Option Explicit

Sub Mail_Selection_Range_Outlook_Body()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object

    Set rng = Nothing
    On Error Resume Next
    Set rng = Sheets("Face and Tattoo").Range("A1:I20").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected" & _
               vbNewLine & "please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

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

    On Error Resume Next
    With OutMail
        .To = "my@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "Tattoo/Facepaint " & Sheets("PerCap").Range("I4").Value
        .HTMLBody = RangetoHTML(rng)
        .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


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

It still doesn't work, I'm afraid. It thinks the Rng=Nothing still. I can't tell you why, the form is not protected. Your spreadsheet worked fine, so it's not Outlook. What would cause it to think the range is nothing?

Something else is going on with your computer or Excel ... the example project functions as intended here.

The macro requires that Outlook is installed on your computer ... and ... you will need to establish a default email account within Outlook.

The sample works on my computer as well. Outlook has a default email set and opens up a new message the way it should after clicking the example button. Here's the file, it's been greatly scaled down. The code in question is in module 66. Also, any tips for condensing those modules? Each one is just a button that prints a range. It takes forever to save.

After downloading your file ... I was able to view just a few of the Modules, including #66. After that, each time I attempted to open any module (even the ones that had opened previously) I would receive an error message indicating ".DLL not available".

Do you have any reference in your coding that is calling a .DLL ?

Of the modules I was able to view, it appears each one is printing out a different range of data from the sheets. If they all print out a hard copy (paper) version of the indicated data, why not place all of the print commands into a single
module and call that single module with one Command Button ? Again, I wasn't able to see all of the modules so my assumptions about the purpose of the modules may be inaccurate.

Sorry I can't be of more assistance due to the .DLL error message.

I will try opening it on a different computer tomorrow, and see if there's anything I can find. As far as I'm aware, there is no.dll file being referenced. I didn't make the original spreadsheet, I'm just updating it with numerous improvements to save time closing our cash room. Each of those buttons print out different forms. It really is an impressive spreadsheet. I will report back after I try and open it tomorrow.