VBA to send email consisting of all visible cells (& not hidden ones)

[Victor96]

Dear Forum,

I have this code in Excel VBA:

Sub Outlook_Mail_Every_Worksheet_Body()

Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet

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


Set OutApp = CreateObject("Outlook.Application")

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("I1").Value Like "?*@?*.?*" And ws.Range("I3").Value <> 0 Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("I1").Value
.CC = ws.Range("I2").Value
.Subject = ws.Range("A1").Value
.HTMLBody = RangetoHTML(ws.UsedRange)
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next ws

Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


I am trying to incorporate the following code into it: " Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible) " in order to hide a row for the recipients. It will not work however - I would highly appreciate any help on this matter!

Thank you,
Victor

 

[Rajneesh Rawat]

Dear Forum,

I have this code in Excel VBA:

Sub Outlook_Mail_Every_Worksheet_Body()

Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet

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


Set OutApp = CreateObject("Outlook.Application")

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("I1").Value Like "?*@?*.?*" And ws.Range("I3").Value <> 0 Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("I1").Value
.CC = ws.Range("I2").Value
.Subject = ws.Range("A1").Value
.HTMLBody = RangetoHTML(ws.UsedRange)
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next ws

Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


I am trying to incorporate the following code into it: " Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible) " in order to hide a row for the recipients. It will not work however - I would highly appreciate any help on this matter!

Thank you,
Victor

Hi,

If you are trying to publish a table in a mail body, try below code to avoid hidden items

Sub PublishTable_Mail()

'Copy range of interest
Dim r As Range
'Set r = Range("A16:E27")
Set r = Range("A16").CurrentRegion

r.Copy

'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)
outMail.HTMLBody = "<P STYLE='font-family:Calibri;font-size:14.0;'><B><U>On Account Aging -</U></B></P>" 'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'To paste as picture
 wordDoc.Range.PasteAndFormat wdChartPicture

'To paste as a table
'wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
End Sub

 

[Victor96]

Hi Rajneesh,

Thank you for your help and the code. However, I am using the code for multiple sheets with tables that vary in sizes, hence, I need the marked area to be dynamic and include the entire sheet/pivot apart from hidden cells/collumns.

I hope it makes sense otherwise please let me know. Thank you in advance!