first start of OL2007 macro VERY slow - how can I fix this?

[Dan]

hello,

I've written a small macro that starts up an addin (DesktopSMS) on the main
window toolbar from a contact form. I had to do it that way because the
addin does not provide programmatic access... :-(

anyway..
I found that the first time I start the macro after starting outlook takes
ages (3-5 minutes) and hangs OL2007 during this waiting time. however,
subsequent starts afterwards are very fast.

(I also noticed that starting the macro editior with Alt-F11 alse takes a
lot of time, though I do not know if that is related. though, after starting
the macro editor at least once, also the macro I mentioned above starts fast
the first time)

anyone can tell me how to speed up the first execution of that macro?
frankly, I am not sure that the problem is with the script.. could it be
that I have a corrupt VBAProject.otm?




the code is very simple. I'm calling DSMS_form() only from a contact form.
It grabs the mobile number from the contact, puts it on the clipboard
(because I did not find another way to send it to the DesktopSMS form
afterwards), then pushes the button DesktopSMS on the main window. I am
using another software to paste the clipboard back to the DesktopSMS form.
I know it would be more elegant to do everything in VBA, but I was simply
not finding a way to access the DesktopSMS addin directly..


Function GetCurrentItem() As Object
Dim objApp As Application

Set objApp = CreateObject("Outlook.Application")

On Error Resume Next
Select Case TypeName(objApp.ActiveWindow)
Case "Explorer"
Set GetCurrentItem = objApp.ActiveExplorer.Selection.item(1)
Case "Inspector"
Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
End Select

Set objApp = Nothing
End Function

Function DSMS_phone() 'get the phone number if this is a contact
Dim objItem As Object

Set objItem = GetCurrentItem()

If objItem.Class = olContact Then
With objItem
DSMS_phone = .MobileTelephoneNumber
' DSMS_email = .Email1Address 'not needed, just for fun
End With
End If

Set objItem = Nothing
End Function

Sub DSMS_button()
Dim explorer As explorer
Dim toolbars As CommandBars
Dim DesktopSMS As CommandBar
Dim SMSBtn As CommandBarButton

Set explorer = Outlook.ActiveExplorer
Set toolbars = explorer.CommandBars
Set DesktopSMS = toolbars.item("Desktop SMS")
Set SMSBtn = DesktopSMS.Controls.item("New S&MS")

SMSBtn.Execute

Set explorer = Nothing
Set toolbars = Nothing
Set DesktopSMS = Nothing
Set SMSBtn = Nothing
End Sub

Sub DSMS_form()
Call ClipBoard_SetData(DSMS_phone)
DSMS_button
End Sub


for the clipboard handling, I am using this code below I found somewhere on
the web.
I did first use the example here
http://word.mvps.org/faqs/macrosvba/ManipulateClipboard.htm
but it did not work reliably with the ClipMagic tool I am using. the version
below has not shown any problems so far.


Option Explicit

Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
Dim hGlobalMemory As Long, lpGlobalMemory As Long
Dim hClipMemory As Long, X As Long

' Allocate movable global memory.
'-------------------------------------------
hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

' Lock the block to get a far pointer
' to this memory.
lpGlobalMemory = GlobalLock(hGlobalMemory)

' Copy the string to this global memory.
lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

' Unlock the memory.
If GlobalUnlock(hGlobalMemory) <> 0 Then
MsgBox "Could not unlock memory location. Copy aborted."
GoTo ExitHere
End If

' Open the Clipboard to copy data to.
If OpenClipboard(0&) = 0 Then
MsgBox "Could not open the Clipboard. Copy aborted."
Exit Function
End If

' Clear the Clipboard.
X = EmptyClipboard()

' Copy the data to the Clipboard.
hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

ExitHere:

If CloseClipboard() = 0 Then
MsgBox "Could not close Clipboard."
End If

End Function

 

[Sue Mosher [MVP-Outlook]]

Try replacing this statement:

Set objApp = CreateObject("Outlook.Application")

with

Set objApp = Application

so that you use the Outlook.Application object that is already available to VBA. You might also try scattering some Debug.Print statements to try to determine which statement(s) contribute most to the delay.

 

[Ken Slovak - [MVP - Outlook]]

3 - 5 minutes sounds really excessive. Is that really how long it takes?

How long does it take to start up Outlook with and without that specific
addin? Is that addin managed code? If it is a first start should take
longer, but not 3 to 5 minutes longer, maybe a minute. That's because
managed code must first start up the Common Language Runtime (CLR) if it's
the first managed code to run, plus the code has to be JIT (just in time)
compiled into the cache the first time it's run.

 

[Dan]

hello ken,

it is not outlook that takes so long to start.
it is just the macro if I start it the first time.

I stopped time. it is more on the 90 seconds side. subsequent starts are
under a fraction of a second. I made a very simple test by putting the code
below as a macro command (button) onto the contact form ribbon and onto the
standard toolbar on the main OL2007 window.

Sub MsgBoxTest()
MsgBox "HI"
End Sub

then I restarted outlook.
the first time I pressed the macro button it takes around 90 second until
the "HI" message box comes up. afterwards it was way under a second.

so it is more on a generic side, my problem. not so much related to the code
I wrote... I guess?





I am not sure about the add-in type. but it's a customized version of the
redoxygen office sms addin (see
http://redoxygen.com/micro_sites/office_sms/, or from my provider
http://www.swisscom-mobile.ch/scm/gek_desktop_sms-en.aspx?c.scn=desktopsms)

Dan

 

[Dan]

thanks sue,

I changed the line. there was no change in startup :-(

dan

Try replacing this statement:

Set objApp = CreateObject("Outlook.Application")

with

Set objApp = Application

so that you use the Outlook.Application object that is already available to
VBA. You might also try scattering some Debug.Print statements to try to
determine which statement(s) contribute most to the delay.

 

[Ken Slovak - [MVP - Outlook]]

Interesting.

Is it shorter if you use Alt+F11 to open the VBA project, then close it,
before you run the macro for the first time?

I'm wondering if the delay is in initializing the VBA project.

 

[Dan]

Interesting.

Is it shorter if you use Alt+F11 to open the VBA project, then close it,
before you run the macro for the first time?

I'm wondering if the delay is in initializing the VBA project.

indeed, ken.

if I open the VBA editor with Alt+F11 the first time after starting up
outlook, it takes about 60 seconds until the editor comes up.
after closing the editor again, starting my macros is much much faster.. 1-2
seconds until the form comes up.. for the MsgBox test it is immediately
comming up.

Alt+F11 afterwards is immediate as well.

so, what happens when the project is initializing? I have the project signed
(there is a binary from microsoft that allows me to setup a signature for
local VBA scripts.. I've done that).

anyway, I did some testing. starting with a clean empty VbaProject.OTM and
adding modules step by step, testing Alt-F11 startup time.guess what.. it
starts to slow down as soon as I digitally sign the project. even only the
empty project signed takes a considerable time to start.
I re-created the digital signature with selfcert.exe from the office12
folder. even with the new digital signature, the startup of the editor is
around 60 seconds...

what else can I do?

dan

 

[Dan]

I was able to speed up the first start of my VBA macros by changing the
digital signature.

instead of using the digital signature created by SelfCert.exe (just for
code signing), I am now using my company provided signature (I had to add
Code Signing to it's purposes).

the main difference between the two certificates is that the selfcert
created one is not trusted. it can be added to the Trusted Root
Certification Authorities Store as indicated in the description of it if
opened in IE. signing VbaProject.OTM would work, but as soon as I exit
outlook, it would discard that certificate..

dan

 

[Ken Slovak - [MVP - Outlook]]

I'm not positive, but my guess is that the extra time with the selfcert
certificate is an attempt at validation from a certificate root authority.

I'd be curious if you add the selfcert certificate to your trusted
publishers list if that helps at all.

 

[Dan]

I'm not positive, but my guess is that the extra time with the selfcert
certificate is an attempt at validation from a certificate root authority.

I'd be curious if you add the selfcert certificate to your trusted
publishers list if that helps at all.

hi ken,
that's what I tried. I believe I mentioned it below as well..
I exported the selfcert certificate, deleted it and then importet it into
the Trusted Root Certification Authorities Store. that actually works (I was
a little surprised on that). I could even select it in the VBA editor as
digital signature. then I exit the VBA editor, still no complaint from
Outlook.

But as soon as I exit Outlook, it just tells me that there was a problem and
that it removed the digital signature.. I don't recal the exact pop up
message, though.

dan

 

[Ken Slovak - [MVP - Outlook]]

What I was thinking of wasn't registering it using IE, but after creating
the selfcert and setting the VBA project to use that cert to see if it shows
up in the trusted publishers list under Trust Center.

To tell the truth I usually set my macro security to prompt rather than
using a certificate. In my experience there are problems when you sign the
VBA project, like if you sign it and then change it and re-sign you get tons
of prompts to save. It was really bad on Outlook 2003 and earlier, so I got
out of the habit of signing the code.

 

[Sue Mosher [MVP-Outlook]]

Ditto. I like getting the prompt at startup. It lets me know for sure that VBA did indeed kick in.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54

What I was thinking of wasn't registering it using IE, but after creating
the selfcert and setting the VBA project to use that cert to see if it shows
up in the trusted publishers list under Trust Center.

To tell the truth I usually set my macro security to prompt rather than
using a certificate. In my experience there are problems when you sign the
VBA project, like if you sign it and then change it and re-sign you get tons
of prompts to save. It was really bad on Outlook 2003 and earlier, so I got
out of the habit of signing the code.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007
Reminder Manager, Extended Reminders, Attachment Options
http://www.slovaktech.com/products.htm

hi ken,
that's what I tried. I believe I mentioned it below as well..
I exported the selfcert certificate, deleted it and then importet it into
the Trusted Root Certification Authorities Store. that actually works (I
was a little surprised on that). I could even select it in the VBA editor
as digital signature. then I exit the VBA editor, still no complaint from
Outlook.

But as soon as I exit Outlook, it just tells me that there was a problem
and that it removed the digital signature.. I don't recal the exact pop up
message, though.

dan