[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I have an outlook macro the keeps giving me an ARRAY OUT OF BOUNDS error on Set myMailItem = mySubFolder.Items(i).  Please review my code and make suggestions where you see fit.

Posted on 2005-04-19
3
Medium Priority
?
178 Views
Last Modified: 2010-04-08
Public Const DerivPath = "\\Houdata01\Investments\TRADING\EXCEL\DERIV\PositionDistribution.xls"
Public Const CIPath = "\\Houdata01\Investments\Canada\CIportfolio.xls"
Public Const Receipients = "Daily Options" '"erik.benke@aiminvestments.com; robert.botard@aiminvestments.com; jeffrey.galloway@aiminvestments.com; roger.mortimer@aiminvestments.com; glen.hilton@aiminvestments.com; dean.pancoast@aiminvestments.com"

Public Sub Main()
   
    If MsgBox("Update Call Overwrite Model?", vbOKCancel, Update) = vbOK Then
        EmailHandler
    Else
        GoTo EndOfMain
    End If
   
EndOfMain:

End Sub
Private Sub EmailHandler()

Dim myNameSpace As NameSpace
Dim myFolder As MAPIFolder
Dim mySubFolder As MAPIFolder
Dim myMailItem As MailItem
Dim myItems As Items
Dim myAttactment As Attachment
Dim myDate As Date
Dim myString As String
Dim i As Integer

Const myPath As String = "\\Houdata01\Investments\Derivatives\Archive\"
Const myFile As String = "Call_Overwrite_Model_JPM.xls"


    Set myNameSpace = Application.GetNamespace("MAPI")
   
    i = 1
    Set myFolder = myNameSpace.Folders(1)
    Do While myFolder.Name <> "Mailbox - #HOU-Derivatives"
        Set myFolder = myNameSpace.Folders(i)
        i = i + 1
    Loop
   
    i = 1
    Set mySubFolder = myFolder.Folders(1)
    Do While mySubFolder.Name <> "Call Overwrite Archive"
        Set mySubFolder = myFolder.Folders(i)
        i = i + 1
    Loop
   
    i = 1
    Do While myDate <> Date
        Set myMailItem = mySubFolder.Items(i)
        myDate = myMailItem.ReceivedTime
        myDate = VBA.FormatDateTime(myDate, vbShortDate)
        i = i + 1
    Loop
   
    Set myAttachment = myMailItem.Attachments(1)
    myAttachment.SaveAsFile myPath & myFile
    myAttachment.SaveAsFile myPath & "Call_Overwrite_Model_" & VBA.Month(myDate) & "_" & VBA.Day(myDate) & "_" & VBA.Year(myDate) & ".xls"
       
End Sub

Private Sub AutoEmailer()

Dim myNameSpace As NameSpace
Dim myFolder As MAPIFolder
Dim mySubFolder As MAPIFolder
Dim myMailItem As MailItem
Dim myAttactment As Attachment
Dim i As Integer

Set myNameSpace = Application.GetNamespace("MAPI")
   
    'Set folder to "Mailbox - #HOU-Derivatives"
    i = 1
    Set myFolder = myNameSpace.Folders(1)
    Do While myFolder.Name <> "Mailbox - #HOU-Derivatives"
        Set myFolder = myNameSpace.Folders(i)
        i = i + 1
    Loop
   
    'Set sub folder to "Inbox"
    i = 1
    Set mySubFolder = myFolder.Folders(1)
    Do While mySubFolder.Name <> "Inbox"
        Set mySubFolder = myFolder.Folders(i)
        i = i + 1
    Loop
   
Set myMailItem = CreateItem(olMailItem)
With myMailItem
    .To = Receipients
    .Subject = "*** Daily Option Positions ***"
    .Body = ""
    .SentOnBehalfOfName = "#HOU-Derivatives"
End With

Set myAttachment = myMailItem.Attachments
Set myAttachment = myAttachment.Add(DerivPath)

myMailItem.Send

End Sub

Private Sub SFAutoEmailer()

Dim myNameSpace As NameSpace
Dim myFolder As MAPIFolder
Dim mySubFolder As MAPIFolder
Dim myMailItem As MailItem
Dim myAttactment As Attachment
Dim i As Integer

Set myNameSpace = Application.GetNamespace("MAPI")
   
    'Set folder to "Mailbox - #HOU-Derivatives"
    i = 1
    Set myFolder = myNameSpace.Folders(1)
    Do While myFolder.Name <> "Mailbox - Benke, Erik G"
        Set myFolder = myNameSpace.Folders(i)
        i = i + 1
    Loop
   
    'Set sub folder to "Inbox"
    i = 1
    Set mySubFolder = myFolder.Folders(1)
    Do While mySubFolder.Name <> "Inbox"
        Set mySubFolder = myFolder.Folders(i)
        i = i + 1
    Loop
   
Set myMailItem = CreateItem(olMailItem)
With myMailItem
    .To = "Roger.Mortimer@aiminvestments.com; Glen.Hilton@aiminvestments.com; Erik.Benke@aiminvestments.com"
    .Subject = "*** Canada Income Positions ***"
    .Body = ""
    .SentOnBehalfOfName = "#HOU-Derivatives"
End With

Set myAttachment = myMailItem.Attachments
Set myAttachment = myAttachment.Add(CIPath)

myMailItem.Send

End Sub


0
Comment
Question by:heinzcr
1 Comment
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 1000 total points
ID: 13815996
There doesn't seem to be anything to stop it. You aren't checking the value of the variable (i) that you are using for the indexing.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question