PDA

View Full Version : Cycling through filters



patricevease
01-22-2016, 03:02 AM
Hi all.

I am struggling to create a VBA that will cycle through a filter. The idea is that it will cycle through a table filter until it has individually selected each filter. (That is the part I haven't done).

I already have a VBA code that will export the current excel worksheet as a PDF to a file location and send it off using a specified cell value. (Code Posted Below) --> the overall goal is for the VBA to cycle through each filter and export the spreadsheet as a PDF so I can send it off, however I cannot seem to solve the cycling issue. Ideally it will be a single code that will automatically run through each filter.

Any help is greatly appreciated.



Sub sendReminderMail()
Dim Name As String
Name = Cells(1, 1)
ChDir "C:\Users\x\Documents\x"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, OpenAfterPublish:=False




Dim Maildb As Object
Dim MailDoc As Object
Dim Body As Object
Dim Session As Object
'Start a session to notes
Set Session = CreateObject("Lotus.NotesSession")
'This line prompts for password of current ID noted in Notes.INI
Call Session.Initialize
'or use below to supply password of the current ID
'Call Session.Initialize("<password>")
'Open the mail database in notes
'Set Maildb = Session.GETDATABASE("", "c:\notes\data\mail\mymail.nsf")
Set Maildb = Session.GETDATABASE("", "C:\Users\Public\Lotus\Notes\Data\mail\x.nsf")
If Not Maildb.IsOpen = True Then
Call Maildb.Open
End If


'Create the mail document
Set MailDoc = Maildb.CREATEDOCUMENT
Call MailDoc.REPLACEITEMVALUE("Form", "Memo")
'Set the recipient
Call MailDoc.REPLACEITEMVALUE("SendTo", Name)
'Set subject
Call MailDoc.REPLACEITEMVALUE("Subject", "KPI Report Test")
'Create and set the Body content
Set Body = MailDoc.CREATERICHTEXTITEM("Body")
Call Body.APPENDTEXT("Test")
'Example to create an attachment (optional)
Call Body.ADDNEWLINE(2)
Call Body.EMBEDOBJECT(1454, "", "C:\Users\x\Documents\x\" & Name & ".pdf")
'Example to save the message (optional)
MailDoc.SAVEMESSAGEONSEND = True
'Example to set high priority (optional)
'MailDoc.DeliveryPriority = "H"
'Example to set return receipt (optional)
'MailDoc.DeliveryReport = "B"
'Send the document
'Gets the mail to appear in the Sent items folder
Call MailDoc.REPLACEITEMVALUE("PostedDate", Now())
Call MailDoc.SEND(False)
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set Body = Nothing
Set Session = Nothing


End Sub


Thanks you in advance.

SamT
01-22-2016, 04:26 PM
What is a "Table Filter" and what is the code you are using to try to cycle thru it?


Set w = Worksheets("Crew")
With w.AutoFilter
For Each Fltr In .Filters
'
Next Fltr
End With