I'm looking to insert multiple itemized objects in excel via a VBA

Question

Is there a way you can set this code to have the embedded image go into a separate cell instead of on top of each other?

For example, i run this macro to insert 3 PDFs. I want these 3 PDFs to go in cells A2, A3, A4.

The reason I am doing this is because I am going to use this spreadsheet in a mail merge to send the individual PDFs to different email addresses.

Sub AddOlEObject()

Dim mainWorkBook As Workbook

Set mainWorkBook = ActiveWorkbook

Folderpath = "C:\Users\jyousef\Documents\SL\October15"

Set fso = CreateObject("Scripting.FileSystemObject")

NoOfFiles = fso.GetFolder(Folderpath).Files.Count

Set listfiles = fso.GetFolder(Folderpath).Files

For Each fls In listfiles

    Counter = Counter + 1

    Range("A" & Counter).Value = fls.Name

    strCompFilePath = Folderpath & "\" & Trim(fls.Name)

    If strCompFilePath <> "" Then

        ActiveSheet.OLEObjects.Add(Filename:=strCompFilePath, Link:=False, DisplayAsIcon:=True, IconIndex:=1, IconLabel:=strCompFilePath).Select

            'Sheets("Object").Activate

        'Sheets("Object").Range("B" & ((Counter - 1) * 3) + 1).Select

    End If

Next

mainWorkBook.Save

End Sub

Answer 1

Objects are not actually placed in cells so I'm not sure how that will work with a mail merge but to place objects at different point just select the different cells (objects are placed at the active cell if you don't specify top and left).

Add something like

Range("A" & Counter).select

after

Range("A" & Counter).Value = fls.Name