VBA Loop to attach files-CodePudding

I'm having a little trouble with the code below. It should do the following.

open the file selector
I would pick or more files
this files would be sent by e-mail, to an address specified in my spreadsheet

I was able to pick one file and send the e-mail with it attached, but when I select two or more the code stops in the following part:

For fichier1 = LBound(fichier1) To UBound(fichier1)

MonMessage.Attachments.Add fichier1

Next fichier1

Note: I'm not using the reference of the Outlook object, because if I do another macro stops working.

Thanks in advance for any help or input

Full code bellow


Dim Fichier As Variant
Dim A       As Integer
Dim MaMessagerie As Object
Dim MonMessage As Object
Set MaMessagerie = CreateObject("Outlook.application")
Set MonMessage = MaMessagerie.CreateItem(0)


fichier1 = Application.GetOpenFilename("File to send (*.XLS*), *.XLS*", _
               Title:="Pick at least One file", _
               MultiSelect:=True)

If Not IsArray(fichier1) Then
    If fichier1 = "" Or fichier1 = False Then
        MsgBox "No file selected!", vbExclamation, "Atention"
        Application.ScreenUpdating = True
        Exit Sub
    End If
End If


MonMessage.To = "[email protected]"
MonMessage.CC = ""

'loop to Attach 1 or more files
For fichier1 = LBound(fichier1) To UBound(fichier1)

MonMessage.Attachments.Add fichier1

Next fichier1

MonMessage.Subject = "Subject"


MonMessage.Body = "test"
MonMessage.Display
'MonMessage.Send

Set MaMessagerie = Nothing

'MsgBox "Email sent"


End Sub```

CodePudding user response：

From the Microsoft Documentation on For...Next Statements The proper syntax is:

For counter = start To end

Where the counter is a valid numeric variable.

A basic example would be :

For i = 1 to 5

In your code, you have the counter as an object, which is why it causes an error. If you want to iterate using an object you can try a For Each...Next Statement.

The syntax is:

For Each element In group

A basic example would be

Dim oItem as Variant
For Each oItem in oCollection

or one of my favorite uses:

Dim Cell as Range
For Each Cell in Range("A1:A4").Cells

In your code, you need to change the element so that it is a seperate variable because you have the same object as the element and the group.

I would suggest something like:

Dim oFile as Variant
For Each oFile in fichier1

MonMessage.Attachments.Add oFile

Next oFile

CodePudding user response：

You never declare fichier1 in the code.

In the following it is declared and a new variable has been added, idx, that can be used when looping through the array of selected files.

Option Explicit

Sub SendMultiAttachmebts()
Dim Fichier1 As Variant
Dim MaMessagerie As Object
Dim MonMessage As Object
Dim idx As Long

    Set MaMessagerie = CreateObject("Outlook.application")
    Set MonMessage = MaMessagerie.CreateItem(0)

    Fichier1 = Application.GetOpenFilename("File to send (*.XLS*), *.XLS*", _
                                           Title:="Pick at least One file", _
                                           MultiSelect:=True)

    If Not IsArray(Fichier1) Then
        If Fichier1 = "" Or Fichier1 = False Then
            MsgBox "No file selected!", vbExclamation, "Atention"
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End If

    MonMessage.To = "[email protected]"
    MonMessage.CC = ""

    'loop to Attach 1 or more files
    For idx = LBound(Fichier1) To UBound(Fichier1)

        MonMessage.Attachments.Add Fichier1(idx)

    Next idx

    MonMessage.Subject = "Subject"
    MonMessage.Body = "test"
    MonMessage.Display
    'MonMessage.Send

    Set MaMessagerie = Nothing

    'MsgBox "Email sent"

End Sub