Hello,

I am trying to generate email to send to 1 adress, with a variable quantity of attachments, depending on list of files, including their root:


The range in which the list is situated is AE2 till AE35, but quantity of files is variable.
file name in cell AE2: S:\Groups\VPK Display Verkoop\02 Aanvraag Modellen\SLAP_VD189999_01_WARNER B_testproof\05 Drukproeven\103935_softproof.pdf
filename in cell AE3: S:\Groups\VPK Display Verkoop\02 Aanvraag Modellen\SLAP_VD189999_01_WARNER B_testproof\05 Drukproeven\103935_softproof_ZM.pdf

and so on...

Some kind of loop till empty cell in that range is needed, I think...

Here is the code:

(for some reason I never seem to remember how to put < at the front and > at the end.. sorry for this...
Sub VBACodeToSendEmail()


'Declare Variables
Dim oApp As Object
Dim oMail As Object
Dim n As Integer

'Disable screen updating
Application.ScreenUpdating = False

'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)

With oMail
.to = ThisWorkbook.Sheets("Productieaanvraag").Range("M12")
.Cc = ThisWorkbook.Sheets("Productieaanvraag").Range("V9")
.Subject = ThisWorkbook.Sheets("Productieaanvraag").Range("V11")
For n = 2 To Count
FileName = Range("AE2:AE35").Value
.Attachments.Add ThisWorkbook.Sheets("Productieaanvraag").Range("AE" & n)
Next n

'Set email body including your signature
'Make use of "& vbNewLine &" code to add new line in your email body
.Body = ThisWorkbook.Sheets("Productieaanvraag").Range("X8")
'Assign attachment file path
'Below code will be used to show created email. To send email replace Display with Send
.Display
End With

Set oMail = Nothing
Set oApp = Nothing



'Enable screen updating
Application.ScreenUpdating = True

End Sub

Thanks for your kind assistance!!

Michel