Expand VBA Permanent Delete Code

[RayB]

Hello,

I am using Outlook 2007 and really understand Excel VB, however, my Outlook VB capability is as a novice.

I have a VBA macro that moves email messages to a folder called rb Auto Deletes positioned as shown below:

After hours of searching and trying different approaches I have written this code which will permanently delete messages without going through the Outbox:

Sub AutoPermDelete()

' Usage: program mimics the entry of Shift + Delete to permanently delete an email
' How to use: select the mail item, execute this macro

Dim myOlApp As Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim Sel As Selection

Set myOlApp = Outlook.Application
Set myNameSpace = myOlApp.GetNamespace("MAPI")

Set Sel = Application.ActiveExplorer.Selection

SendKeys ("+{DELETE}")

SendKeys ("{ENTER}")

End Sub

The code works perfectly but I have to select each email. So what I would like to have help with expanding the code to do the following:

1. 'Look' at the rb Auto Deletes folder.
2. Determine which messages are older than 1 calendar year from the current date and permanently delete them using my code.

I am totally lost as to how to make this happen and all my attempts have not worked so I am asking for help.

Merry Christmas and Thanks in Advance for your help!

 

[oliv-]

Hi,
try this

Function Nettoyage_Inbox()
'---------------------------------------------------------------------------------------
' Procedure : Nettoyage_Inbox
' Author    : OCTU
' Date      : 11/01/2015
' Purpose   : permanently delete emails
'---------------------------------------------------------------------------------------
'
    Dim Dossier As Folder
    Dim NS As NameSpace
    Set NS = application.session
    Set Dossier = NS.GetDefaultFolder(olFolderInbox).Parent.Folders("rb Auto deletes")
dim myItems 
 madate = DateAdd("yyyy", -1, Date)
    Set myItems = Dossier.items.Restrict("[LastModificationTime] < '" & madate & "'")
    While myItems.count > 0
       myItems.Remove 1
        'Dossier.Items(1).Delete 'garde historique Exchange
    Wend

End Function

 

[Lewis-H]

To delete a row using VBA, use a statement with the following structure:

1	Worksheets.Rows(Row#).Delete

Worksheets.Rows(Row#).Delete

1. Item: Worksheets.
   • VBA Construct: Workbook.Worksheets property.
     
     
   • Description: Returns a Worksheet object representing the worksheet you work with.
2. Item: Rows(Row#).
   • VBA Construct: Worksheet.Rows property.
     
     
   • Description: Returns a Range object representing row number Row# of the worksheet returned by item #1 above.
     
     If you explicitly declare a variable to represent Row#, use the Long data type.
3. Item: Delete.
   • VBA Construct: Range.Delete method.
     
     
   • Description: Deletes the Range object returned by item #2 above.

Macro Example
The following macro deletes row 6 of the worksheet named “Delete row”.
view source

print?

1	Sub deleteRow()

2	Worksheets("Delete row").Rows(6).Delete

3

4

End Sub

Hope this is helpful!

Regards,
Lewis

 

[oliv-]

Lewis, it's not EXCEL but OUTLOOK

 

[RayB]

Hi,
try this

Function Nettoyage_Inbox()
'---------------------------------------------------------------------------------------
' Procedure : Nettoyage_Inbox
' Author    : OCTU
' Date      : 11/01/2015
' Purpose   : permanently delete emails
'---------------------------------------------------------------------------------------
'
    Dim Dossier As Folder
    Dim NS As NameSpace
    Set NS = application.session
    Set Dossier = NS.GetDefaultFolder(olFolderInbox).Parent.Folders("rb Auto deletes")
dim myItems
madate = DateAdd("yyyy", -1, Date)
    Set myItems = Dossier.items.Restrict("[LastModificationTime] < '" & madate & "'")
    While myItems.count > 0
       myItems.Remove 1
        'Dossier.Items(1).Delete 'garde historique Exchange
    Wend

End Function

Thank you so much it works perfectly!

 

[RayB]

One other question is do you know a way to add this function to run from a button on the a toolbar? From what I have read, the classical way to add a button to control a macro will not work because Functions do not show up in the Macro list.

Again, Merry Christmas and Thanks in Advance for your help!

 

[Diane Poremsky]

It should work if you change it to a sub or use a simple macro to call it:

sub Deletemail()
Nettoyage_Inbox
end sub

I have a macro here that empties all mail in folder (it may not work in 2007 though)

Empty Multiple Deleted Items Folders using a Macro

The scenario: You have several email accounts, each with their own data file and you want to empty the deleted items in every data file with one click. The solution: a macro.

www.slipstick.com

This shows how to work with selected items:

Working with All Items in a Folder or Selected Items

These samples contains the basic code for working with all items in a selected folder or selected items in a folder.

www.slipstick.com