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

Victor96

New Member
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Rajneesh Rawat

New Member
Joined
Mar 31, 2017
Messages
35
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

VBA Code:
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

New Member
Joined
Dec 4, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top