CODEWITHSUNDEEP
vbaemailoutlook
bigbryan
bigbryan

Reputation: 431

Outlook VBA: How to send emails from Drafts folder?

I have a hundred mails in my drafts folder in Outlook. And I want to send them all.

I have a code here below that sends the mails from drafts folder except for one mail(which is the last mail). The last mail that the program reads gets an error that says:

"Run-time error '440': Array index out of bounds."

What do you think guys? Thanks a lot.

For i = 1 To myFolder.Items.Count

    myFolder.Items(i).Send

Next

Upvotes: 0

Views: 5600

Answers (3)

Peter
Peter

Reputation: 6035

As each draft is sent, there is one less email in the folder. The next email moves up to be the first in the collection. So as you loop, you can just keep sending the message folder.Items(1).

I have setup this code for some users at work (they have multiple mailboxes linked to their accounts): 

    Sub SendAllYourMailboxDrafts()
        SendAllDrafts "your-mailbox-name"
    End Sub

    Sub SendAllDrafts(mailbox As String)

        Dim folder As MAPIFolder
        Dim msg As Outlook.MailItem
        Dim count As Integer

        Set folder = Outlook.GetNamespace("MAPI").Folders(mailbox)
        Set folder = folder.Folders("Drafts")

        If MsgBox("Are you sure you want to send the " & folder.Items.count & " items in your " & mailbox & " Drafts folder?", vbQuestion + vbYesNo) <> vbYes Then Exit Sub

        count = 0
        Do While folder.Items.count > 0
            Set msg = folder.Items(1)
            msg.Send
            count = count + 1
        Loop

        MsgBox count & " message(s) sent", vbInformation + vbOKOnly
    End Sub

Upvotes: 0

SkyMaster
SkyMaster

Reputation: 1323

The index for the Items collection starts at 1, and the items in the Items collection object are not guaranteed to be in any particular order.
https://msdn.microsoft.com/en-us/library/office/ff863652.aspx

EDIT: See also compact the Outlook data file: https://support.office.com/en-nz/article/Reduce-the-size-of-Outlook-Data-Files-pst-and-ost-e4c6a4f1-d39c-47dc-a4fa-abe96dc8c7ef

Upvotes: 1

DanL
DanL

Reputation: 999

You will be running into problems if you modify the folder within a loop with an incrementing counter.

A possible solution would be to loop the collection backwards, i.e.:

For i = myFolder.Items.Count To 1 Step -1
    myFolder.Items(i).Send
Next

And as SkyMaster mentioned, the array is 1-indexed.

Upvotes: 1

Related Questions