VBA Optimization

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have the following VBA script that takes a message and places a copy in a
local folder and one in shared folder. It works fine, but it is slow. Is
there something I can do to make it work faster?

I am not really a programmer and pasted this together with help from you all
previously.

Sub W7X80201()
Dim obj As Object
Dim i As Long
Dim Sel As Selection

Dim objFolder As Outlook.MAPIFolder
Dim objFolder1 As Outlook.MAPIFolder
Dim objInbox As Outlook.MAPIFolder
Dim objInbox1 As Outlook.MAPIFolder
Dim objNS As Outlook.NameSpace
Dim objNS1 As Outlook.NameSpace

Set objNS = Application.GetNamespace("MAPI")
Set objNS1 = Application.GetNamespace("MAPI")

'Assume these are mail folders
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
Set objFolder = objInbox.Folders("Projects").Folders("City of
Scottsdale").Folders("W7X80201 - Airport - Seal Coat")
Set objInbox1 = objNS1.GetDefaultFolder(olPublicFoldersAllPublicFolders)
Set objFolder1 =
objNS1.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Phoenix").Folders("Jobs").Folders("NAI").Folders("W7X80201 - Airport - Seal Coat")

Set Sel = Application.ActiveExplorer.Selection
For i = Sel.Count To 1 Step -1
Set obj = Sel(i)
Select Case True
Case (TypeOf obj Is Outlook.MailItem), (TypeOf obj Is
Outlook.ReportItem)
Set objCopy = obj.Copy
obj.Move objFolder
objCopy.Move objFolder1
End Select
Next
End Sub
 
How many items do you count in the Selection collection?

--
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, 25 Nov 2008 11:56:04 -0800 schrieb Paul:
I have the following VBA script that takes a message and places a copy in a
local folder and one in shared folder. It works fine, but it is slow. Is
there something I can do to make it work faster?

I am not really a programmer and pasted this together with help from you all
previously.

Sub W7X80201()
Dim obj As Object
Dim i As Long
Dim Sel As Selection

Dim objFolder As Outlook.MAPIFolder
Dim objFolder1 As Outlook.MAPIFolder
Dim objInbox As Outlook.MAPIFolder
Dim objInbox1 As Outlook.MAPIFolder
Dim objNS As Outlook.NameSpace
Dim objNS1 As Outlook.NameSpace

Set objNS = Application.GetNamespace("MAPI")
Set objNS1 = Application.GetNamespace("MAPI")

'Assume these are mail folders
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
Set objFolder = objInbox.Folders("Projects").Folders("City of
Scottsdale").Folders("W7X80201 - Airport - Seal Coat")
Set objInbox1 = objNS1.GetDefaultFolder(olPublicFoldersAllPublicFolders)
Set objFolder1 =
Click to expand...
objNS1.GetDefaultFolder(olPublicFoldersAllPublicFolders).Folders("Phoenix").Folders("Jobs").Folders("NAI").Folders("W7X80201
- Airport - Seal Coat")
 
It could be anywhere from 1 to 100. Usually it is 1-10. IF this is the
most efficient way of doing this is there a way to add a counter so that
would show "Message X of Y complete?

Like I said, I know just enough to be dangerous!!

Thanks for the help
 
I think, Copy and Move is what consumes so much time, and I don't think you
could accelerate that code. If you add a counter, that would make it even
slowlier. However, you could do that by adding a UserForm to the project,
put a label on it, and update it in every loop. You must display the form
with Show 0, for that it displays non-modal, and call DoEvents after every
update.

--
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, 5 Dec 2008 08:04:00 -0800 schrieb Paul:
 
Back
Top