Array of outlook items....HELP!!

[WhytheQ]

I'm using the following code, from Excel, with no reference to Outlook:


Dim objOutlook As Object
Dim objMailItem As Object

Dim myCreatedEmails() As Object

Sub CreateAndDisplayEmails()

Set objOutlook = CreateObject("Outlook.Application")

Erase myCreatedEmails

For i = 1 To 3

Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
.To = "Tester" & i
.Subject = "Tester" & i
End With

ReDim myCreatedEmails(k)
'##### falls down on next line #####
myCreatedEmails(k) = objMailItem
k = k + 1

Next i

For k = 1 To UBound(myCreatedEmails)
myCreatedEmails(k).Display
Next k

End Sub


....I've marked where it already falls down and I assume even if this
line is fixed then it'll fall down later on on the line
"myCreatedEmails(k).Display"

The above must be pretty bl##dy close!...can anyone help?

Help appreciated
Jason

 

[Michael Bauer]

Am 23 Jun 2006 09:26:43 -0700 schrieb WhytheQ:

What value is k the first time? And what type is myCreatedEmails?

k must be 0 the first time. If myCreatedEmails is an object type then you
must use the Set statement.

 

[WhytheQ]

thanks for the pointers Michael.
i'm now on a machine without outlook. Do you think the following will
work:

option base 1

Dim objOutlook As Object
Dim objMailItem As Object


Dim myCreatedEmails() As Outlook.MailItem'Object


Sub CreateAndDisplayEmails()


Set objOutlook = CreateObject("Outlook.Application")


Erase myCreatedEmails


For i = 1 To 3


Set objMailItem = objOutlook.CreateItem(0)


With objMailItem
.To = "Tester" & i
.Subject = "Tester" & i
End With

ReDim preserve myCreatedEmails(i)
myCreatedEmails(i) = objMailItem

Next i


For k = 1 To UBound(myCreatedEmails)
myCreatedEmails(k).Display
Next k


End Sub

I've changed the type from object to outlook.mailitem(not too sure if
this will work from Excel without a reference to Outllook model?)
I've added the Preserve word to stop the array being overwritten.
Also added option base 1 as I like to start at 1.

will this work now?

help appreciated.

Jason.

 

[Michael Bauer]

Am 24 Jun 2006 01:40:28 -0700 schrieb WhytheQ:

Without a reference onto the Outlook library you can´t declare a variable as
any Outlook object. And without having Outlook installed you can´t create an
instance of it (e.g. with CreateObject)

Beside that, if the array is declared as any object you need to use the Set
statement:

Set myCreateEmails(i)=...

No error, but if you do know how many objects you want to create then I´d
ReDim the array only once (before starting the loop). That´s a lot faster.

 

[WhytheQ]

nice one Michael. With a reference to Outlook I have the following;

Option Explicit

Option Base 1

Dim objOutlook As Object
Dim myCreatedEmails() As Outlook.MailItem 'Object

Dim i As Integer
Dim k As Integer

Sub CreateAndDisplayEmails()

Set objOutlook = CreateObject("Outlook.Application")

Erase myCreatedEmails

For i = 1 To 3

ReDim Preserve myCreatedEmails(i)
Set myCreatedEmails(i) = objOutlook.CreateItem(0)

With myCreatedEmails(i)
.To = "Tester" & i
.Subject = "Tester" & i
End With

Next i

For k = 1 To UBound(myCreatedEmails)
myCreatedEmails(k).Display
Next k

End Sub


seems to work fine.
gonna try to do it without a reference now.
also gonna try to do it as a collection.

thanks again.
Jason

 

[WhytheQ]

and using a collection and reference I've got:

Option Explicit

Dim objOutlook As Object
Dim objMailItem As Object
Dim myCreatedEmails As Collection

Dim i As Integer

Sub CreateAndDisplayCollectionEmails()

Set objOutlook = CreateObject("Outlook.Application")

Set myCreatedEmails = New Collection

For i = 1 To 3

Set objMailItem = objOutlook.CreateItem(0)

With objMailItem
.To = "Tester" & i
.Subject = "Tester" & i
End With

myCreatedEmails.Add Item:=objMailItem, key:=CStr(i)

Next i

For i = 1 To myCreatedEmails.Count
myCreatedEmails(i).Display
Next i

End Sub

Both seem to work ok. Thanks for the help
Jason