How do I eliminate the "Enable Macro" selection?

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I know this question hass been asked before but I was unable to make it work.
In order to make up for Outlook's inadequate handling of IMAP, I put
together a simple macro that would move messages to a folder "Trash" when
invoked. (The delete key deletes emails forever - thank you Microsoft!)

All I want to do is to enable the macro without having to manually enable it
each time I start outlook and without setting my security settings to enable
all macros.

I would appreciate any help.

Here is the code:

Sub MoveToTrash()
On Error Resume Next
Dim objFolder As MAPIFolder
Dim objNS As NameSpace, objItem As MailItem

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.Folders("KJB").Folders("Trash")

If objFolder Is Nothing Then
MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation,
"INVALID FOLDER"
End If

If Application.ActiveExplorer.Selection.Count = 0 Then
Exit Sub
End If

For Each objItem In Application.ActiveExplorer.Selection
If objFolder.DefaultItemType = olMailItem Then
If objItem.Class = olMail Then
objItem.UnRead = False
objItem.Move objFolder
End If
End If
Next

Set objItem = Nothing
Set objFolder = Nothing
Set objNS = Nothing
End Sub
 
Either use the settings and enable macros, or sign the VBA project; for that
search for 'SelfCert.exe'.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 15 Sep 2009 23:15:01 -0700 schrieb Keith:
 
Thanks a lot. I didnt find it by that name. Its under MS office and tools
as "Digital Certificate for VBA Projects.lnk". Then i did a Google search
and found this great writeup on how to do it, though its pretty
self-explanatory: http://www.howto-outlook.com/howto/selfcert.htm

Quick follow-up. Is there a way to make the macro run when I hit the delete
key? Right now I have an icon for it on my toolbar but sometimes I hit
delete and then a message is gone forever.

Also, I lifted the code and modified it from a macro someone else wrote so
its probably lousy code on my part. Its quite slow - any ideas on how to
make it faster? I am not a VBA expert - as you can no doubt tell!

Thanks again Michael.
 
Outlook can't tell you what has been pressed on the keyboard. For that you'd
have to use a lot of Win32 API code. You might rather want to stick with the
toolbar button.

But as far as I know the DEL key doesn't delete messages, it just marks them
as deleted. Just click Edit/Recover (or similar) , and the messages are
back.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Wed, 16 Sep 2009 14:50:02 -0700 schrieb Keith:
 
Thanks Michael,

No, the emails are gone. On other platforms like my iPhone then you can
tell it to move deleted items to a selected folder like "Trash" but Outllook
just throws them in the bit-bucket. Its really poor design but I think
microsoft really only care about exchenge support and they do the bare
minimum to support POP and IMAP. There are other posts about how to write
vba to do this.

Incidentally, can you see if its possible to make this code run faster?
don't know enoutgh about VBA to speed it up myself.

Keith
 
#1: I'd say it's no IMAP account then; or you have a filter in place that
hides those messages that are flagged as deleted. A lot of people here ask
for how to delete messages from an IMAP account immediately just by hitting
the DEL key, but Outlook doesn't support that.

#2: no

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Thu, 17 Sep 2009 19:54:01 -0700 schrieb Keith:
 
You've declared objItem as a MailItem type, but then you check if the
class is olMail, isn't that redundant? Also, regardless of whether the
Trash folder holds non-MailItems, you can skip the DefaultItemType
check. If it does matter, you're already limiting the For loop to mail
items anyway, and if it doesn't matter, the check is pointless.

You might also consider using the Restrict Method on the Items
Collection and returning a filtered subset of items. It can make your
code faster if the folder contains a large number of non-MailItems.

http://msdn.microsoft.com/en-us/library/aa210275(office.11).aspx

--JP
 
Skipping the DefaultItemType is a good catch; the check is useless not
because of the declaration of the object variable but because it will return
the same value in any loop.

The Restrict method doesn't help as he loops through a collection of
selected items.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Fri, 18 Sep 2009 07:14:22 -0700 (PDT) schrieb JP:
 
Yep it’s definitely IMAP and the messages are definitely deleted from the
server. The account shows as "IMAP/SMTP" in the account settings. Deleted
emails from Outlook disappear from my view on the iPhone too. If I delete
them from Outlook they are gone forever. If I delete them from my iPhone
they go to Trash. I understand though that the function of putting deleted
items in a designated folder such as Trash is not part of the IMAP spec but
that most IMAP platforms (except Outlook) support it. Outlook does however
let me designate a sent items folder.

I used to use POP but when I got my iPhone, I switched to IMAP to access my
emails because I wanted to synchronize emails between the two platforms.

My email account is with GoDaddy so I use: IMAP.secureserver.net and
SMTPOUT.secureserver.net. Godaddy uses "Trash" for deleted items.

Ive asked about IMAP support in Outlook General section and the result seems
to be to write a VBA macro because this delete -move to trash function is not
supported.

Thanks so much for your help.

Keith
 
Thanks you so much Michael and JP

I made the suggested change (At least I think I did – it still works anyway!)

Your input lead me to ask some questions which I have added as comments. I
really really appreciate your help.

So here is the new code:

Sub MoveToTrash()
On Error Resume Next
Dim objFolder As MAPIFolder
Dim objNS As NameSpace
Dim objItem As MailItem

Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.Folders("KJB").Folders("Trash")

' is this redundant since the folder name is hard coded and doesnt change??
If objFolder Is Nothing Then
MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation,
"INVALID FOLDER"
End If

' Is this test needed since it appears the FOR loop below takes care of
this test
If Application.ActiveExplorer.Selection.Count = 0 Then
Exit Sub
End If

For Each objItem In Application.ActiveExplorer.Selection
objItem.UnRead = False
objItem.Move objFolder
Next

' Do these items need to be set to nothing. Wont they be removed from the
heap or stack when the sub() ends?
Set objItem = Nothing
Set objFolder = Nothing
Set objNS = Nothing
End Sub
 
#1: I'd keep that in place. In a few months you don't remember all the
details and might decide to rename the folder. Then the prompt will remind
you to update the code. Skipping that one wouldn't save you more than a
millisecond, if any.

#2: It's not necessary in your code, but again the saved time is not worth
worrying about.

#3: VB(A) automatically sets the variables to Nothing as soon as you leave
the function.

So, actually you can delete all the mentioned lines of code. But time could
be saved only within the loop. And there's nothing you can do except maybe
to use a variable set to the Selection object:

Dim Sel as Outlook.Selection
Set Sel=Application.ActiveExplorer.Selection
For Each objItem in Sel
....
Next

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Fri, 18 Sep 2009 19:19:01 -0700 schrieb Keith:
 
I made all the changes Michel, thanks a lot.

Actually I think the slow speed is in part because of the way I invoke the
macro.

I select on item then invoke the macro by pressing my macro button, then I
repeat for the next item, etc.

A better method would be to select multiple items first and then invoke the
macro once to move the multiple selected items at once.

For completeness, here is my final code:

Sub MoveToTrash()
On Error Resume Next

Dim objNS As NameSpace
Dim objFolder As MAPIFolder
Dim objItem As MailItem
Dim Sel As Outlook.Selection

' "Trash" folder in user namespace
Set objNS = Application.GetNamespace("MAPI")
Set objFolder = objNS.Folders("KJB").Folders("Trash")

' Warn if "Trash" folder doesnt exist
If objFolder Is Nothing Then
MsgBox "This folder doesn't exist!", vbOKOnly + vbExclamation,
"INVALID FOLDER"
End If

' User selected emails to move to trash
Set Sel = Application.ActiveExplorer.Selection

' For each mail item in user selection, move it to "Trash" folder
For Each objItem In Sel
objItem.UnRead = False
objItem.Move objFolder
Next
End Sub
 
Can this code be compiled to make it run faster or is the VBA code interpreted?

I guess the speed things is just my observation. If I hit the delete key,
its very fast, if I hit my macro button it might take a second to run and
complete the move so it seems slow.
 
I think moving an IMAP message is what takes most of the time. There's
nothing you can do to speed it up.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Sun, 20 Sep 2009 11:10:01 -0700 schrieb Keith:
 
Don't worry, it happens to all of us :) The code I've written to move
or delete multiple emails always runs slower than the Del key.

--JP
 
VBA code will always be slower than Extended MAPI code of course.




Don't worry, it happens to all of us :) The code I've written to move
or delete multiple emails always runs slower than the Del key.

--JP
 
Well that's reassuring JP - I was about to talk to my therapist about why
Outlook is making me feel a victim. Guess I can cancel that appointment now
:)
 
Back
Top