Send Email Data with Hidden rows without Values

[chaboyski]

Hi Team,

i want to copy the cells C22:J60, but, cells C40:J60 should be hidden if there is no data then send email.

i dont need it to be hidden on the file, i just wanted it to be hidden on the email.

How would i be able to include it on the code below?

 Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim strbody2 As String
    Dim objWord
    Dim objDoc
    Dim objSelection
    Dim objShapes
    Dim i As Integer
    Dim strValue As String
    Dim ws As Worksheet
   
    Today = Format(Now(), "mm.dd.yyyy")
   
    'Attach Range to report
    Set rng = Nothing
    On Error Resume Next
        ' Only send the visible cells in the selection.
    Set rng = Sheets("Standard Outage Template").Range("C22:J60").SpecialCells(xlCellTypeVisible)
        ' You can also use a range with the following statement.
        ' Set rng = Selection.SpecialCells(xlCellTypeHidden)
    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)

    'This is the email body

    On Error Resume Next
   
    With OutMail
        .Display
        .SentOnBehalfOfName = Sheets("Recipients").Range("C1")
        .To = Sheets("Recipients").Range("r2")
        .CC = Sheets("Recipients").Range("C3")
        .BCC = Sheets("Recipients").Range("C6")
        .Subject = Sheets("Recipients").Range("C10")
        .HTMLBody = strbody & RangetoHTML(rng) & strbody2 & .HTMLBody
        .Display ' display before sending or use .Send
    End With
   
    On Error GoTo 0
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = False
    End With

    Set OutApp = Nothing
    Set OutMail = Nothing

'This code will hide the Formula sheet
 For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "Formula") > 0 Then
            ws.Visible = xlSheetHidden
        End If
    Next ws

End Sub

 

[davesexcel]

"Sheets("Standard Outage Template").Range("C22:J60")."

Make the range dynamic by finding the last row, then

Sheets("Standard Outage Template").Range("C22:J" & LastRow).

 

[chaboyski]

thank you @davesexcel