Reference Outlook in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Noemi - 22 Nov 2007 04:30 GMT

Hi

I have an excel spreadsheet which is used on different versions ie 97 & 2000.

I need to be able to email the spreadsheet using a macro however at the
moment I get a compile error.

We are unable to set the library reference on the 97 version because to save
the form we must run it through 2000 first.

Any idea's what I need to do to set the correct reference using code.

Thanks

Reply to this Message

Bob Phillips - 22 Nov 2007 09:54 GMT

Take a look here http://www.xldynamic.com/source/xld.EarlyLate.html

Signature

---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi
>
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Noemi - 22 Nov 2007 23:06 GMT

Hi Bob
I am trying to use the sample code however the following line stays red and
I dont know why

Set objRecipient.Add("John Wilson")

any idea's as I cannot test it until this is not red anymore.

thanks

> Take a look here http://www.xldynamic.com/source/xld.EarlyLate.html
>
[quoted text clipped - 13 lines]
> >
> > Thanks

Reply to this Message

Bob Phillips - 23 Nov 2007 23:25 GMT

Can we see the rest of the code?

Signature

> Hi Bob
> I am trying to use the sample code however the following line stays red
[quoted text clipped - 24 lines]
>> >
>> > Thanks

Reply to this Message

Noemi - 25 Nov 2007 21:43 GMT

Hi Bob

Below is the code I am using, I have marked the line which stays red and I
dont know why.

Dim objOutlook As Object
Dim objNameSpace As Object
Dim objMailItem As Object
Dim objRecipient As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")

Set objMailItem = objOutlook.CreateItem(0)

Set objRecipient.Add("John Wilson") **********Stays Red
objRecipient.Type = 1

objMailItem.Subject = "Testing Only"
objMailItem.Body = "Testing only"
objMailItem.Attachments.Add ActiveWorkbook.FullName

objNameSpace.Logon , , True
objMailItem.Send

Set objRecipient = Nothing
Set objMailItem = Nothing
Set objNameSpace = Nothing
Set objOutlook = Nothing

Thanks
Noemi

> Can we see the rest of the code?
>
[quoted text clipped - 26 lines]
> >> >
> >> > Thanks

Reply to this Message