Filtered Results to email

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
413
I came across this VBA post that allows me to create an email and BCC a list of emails, however I want to gather the visible filtered list, and not everything that is within the range.

I'm unsure where SpecialCells(xlCellTypeVisible) would apply. Any assistance would be appreciated:

Code:
Sub CreateEmail()


Dim oMSOutlook As Object
Dim oEmail As Object
Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)


With oEmail


 For i = 2 To Application.WorksheetFunction.CountA(Columns(4)) '2 Skips header, Column 4 applies to Column D
 .Recipients.Add(Cells(i, 4).Value).Type (3)   ' Type 1 (To), Type 2 (CC), Type 3 (BCC) 


 Next i


.Display 'Displays the email before it gets sent
End With


Set oMSOutlook = Nothing
Set oEmail = Nothing


End Sub
:eek:
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,481
Try with this variant:
Code:
For I = 2 To .....
    If Rows(I).Hidden = False Then
        .Recipients.Add(Cells(I, 4).Value).Type (3)
    End If
Next I
Bye
 

Forum statistics

Threads
1,078,462
Messages
5,340,454
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top