Add Reference on load

[greg0303]

I have a Word template that has a marco which send an email out, however the sub uses the Outlook.Application object which requires the MS Outlook 9.0 Object Libary reference added. I don't want to have to add this to each user that might use the template, can you not somehow add this referance when the document loads with vb?

 

[firefytr]

You could try..

Sub AddOLRef()
    Application.VBE.ActiveVBProject.References.AddFromGuid _
        "{00062FFF-0000-0000-C000-000000000046}", 0, 0
End Sub

The GUID (Globally Unique IDentifier) should be good for all versions of Outlook object library.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[CautionMP]

greg0303,
You could also change your code to use generic object containers for all your Outlook objects, this way you don't have to add a reference.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[KenPuls]

Hi Greg,

CMP is referring to Late Binding to connect/create an Outlook instance. I have an example on a few other apps here:

http://www.excelguru.ca/node/10

And here:

http://www.excelguru.ca/node/11

It could very easily be modified to work with Outlook.

Ken Puls, CMA

http://www.excelguru.ca

 

[firefytr]

Depending on version fluctuation and how much you really utilize your Office suite, late binding might be better. I think it really depends on the usage of the user(s) and their circumstances. So each situation should be evaluated on an individual basis.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenPuls]

Actually, not to overly plug my site here, but you may want to look at the following article. (I probably should have mentioned it alone, actually)

http://www.excelguru.ca/node/44

The article actually does use late binding to connect to Outlook, so you can see how the whole email is constructed. It also deals with avoiding the dreaded Outlook security prompt, by using a little freeware app called "ClickYes". If you use Outlook 2000 or above, chances are you'll run into this.

Hopefully it helps.


Ken Puls, CMA

http://www.excelguru.ca

 

[firefytr]

Two things:

1) Has anybody heard about Outlook 2007 having security dampeners for this issue?

2) Ken, you're site and it's articles are *always* worth plugging!!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[KenPuls]

LOL! Thanks Zack.

I haven't tried the Outlook 2007 thing personally. Since it won't co-exist with 2003, I didn't install it. There was not way I was going to overwrite my full version with a time sensitive trial of 2007.

At some point, I'm going to set up a virtual PC and will give it a go there. Just haven't had time to date. If anyone else has done the test, though, I'd love to know.

Ken Puls, CMA

http://www.excelguru.ca

 

[greg0303]

I just use the sendkey command to send an Alt+S to click the send button. The email will quickly pop up then disapper. Seems to work for me so far.

With objMail
.To = "me@mydomain.com"
.Subject = "Test Email"
.body = "Test Email"
.Display
End With
Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "%S

 

[firefytr]

Hi there. I would really recommend using SendKeys. There are alternatives, such as Ken points out with ClickYes. There is also CDO and Outlook Redemption. And I always recommend OutlookSpy to anyone who uses VBA with Outlook.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

I meant ...

I would really recommend NOT using SendKeys.

Sorry 'bout that...

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[greg0303]

Why would you not recommend using the Sendkeys?

 

[longhair]

sendkeys can be buggy - if your object does not have focus then you are sending your keys else where.
you can also use blat to send the mail.
regards,
longhair

 

[firefytr]

I would not recommend using SendKeys because I don't like it. I don't like it because it's prone to errors, very finicky, hard to maintain and even harder to troubleshoot. Ever try stepping through your code with a SendKeys call in it? PITA.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[CautionMP]

greg0303,
Ditto for me and [tt]SendKeys()[/tt], it's like using a bandaid when stiches are needed.

A thought on CDO, Outlook Redemption, OutlookSpy, and ClickYes. No disrespect to firefytr and KenPuls, these are great tools, but the fact that you seem to building an application for other users they may present problems with loading, installing, and registering other software. I'm asking because in my last corporate job the company had managed desktops and getting approval to install software was, in the words of firefytr, a major PITA (I probably could have been fired for installing the MSDN library on my desktop).

Now here's the thought:[ul]
[li]One of the MS workarounds is to create a User Defined Function (UDF) in Outlook to send the message, then call that UDF from another application (Word in this example).[/li]
[li]We can write the code to create a new Module and UDF in Outlook, from Word or any other Office application.[/li]
[li]Then call the new UDF in the same session?[/li][/ul]

greg0303, firefytr or KenPuls any thoughts?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[KenPuls]

Hey guys,

I'll third the anti-sendkeys arguments. Get a user that clicks somewhere else during your routine because it's taking too long and you're fried.

CMP,

It's an interesting thought on the UDF. I wonder if it would work. You're suggesting using code to write code from externally, correct? Never tested it myself, but may be worth a go. Seems strange that it would be that easy to just get around the security though.

In regards to the rest, I totally understand your concern. One thing to note, though... CDO is not a download and install thing. It's native on the system. The thing is, you have to be able to leverage an SMTP mail server to use it. I've never been able to pull that off if it needs authentication.



Ken Puls, CMA

http://www.excelguru.ca

 

[longhair]

all,
one reason why i advocate blat is the ease of its use. it does not need to be installed, although you can. the code to use it is just a simple shell call.
regards,
longhair

 

[greg0303]

For this application I think I'll stick with Sendkeys just cause its a pretty basic use. The word file is a request form for authorization to access a public folder. The user inputs the folder they want to get into and some other info and then hit a submit button. When they click that it creates the email and sends it off, which works. If for some reason the sendkey misses alt+s cause the user is changing focus on some window or timing is off then its not really a big deal cause an alt+s isn't going to do much damage on another app and if they user sees the email not send they will just hit the send button anyways but manually. I didn't want to have to deploy software on all the users computers just this.

Thanks for all your help!

 

[firefytr]

CMP, I think that would be very interesting. I primarily only access Outlooks OM via outside applications for automation purposes, so I'd need to do some research to grab the current mail item, or last created mail item. but that sounds very intriguing and quite a workaround. It would be very nice to have, and I'd probably use it, as it would take out the need to a third party program.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci