Sending emails with attachments

[valquiriaa]

Hi all, I'm having an issue with the below code.

I want to send 2 files on the same email, which is fine. But I dont have 2 files for all suppliers, so what do I need to include on the code to keep sending the email even if there is only one file?

Any help will be much appreciated :)


Sub CreateEmail()
Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Dim path As String
path = "W:\BUYERS\Xmas files"
Dim RList As Range
Set RList = Range("A2", Range("a2").End(xlDown))
Dim R As Range
For Each R In RList
Set EItem = EApp.CreateItem(0)
With EItem
.To = R.Offset(0, 2)
.Subject = "Peak Planning - Xmas 2022 - Information and Data - " & R.Offset(0, 1)
.Attachments.Add (path & R.Offset(0, 3))
.Attachments.Add (path & R.Offset(0, 4))
.Body = "Dear Supplier, " & vbNewLine & vbNewLine _
& "Please find attached spreadsheets to help with stock planning for the coming peak season." & vbNewLine _
& " - High Volume Build (DC10 Only) " & vbNewLine _
& " - C line & Lids off Build Data " & vbNewLine _
& vbNewLine & vbNewLine & "Many thanks" & vbNewLine & vbNewLine & _
"Kind regards,"
.Send
End With
Next R
End Sub

[arnelgp]

Sub CreateEmail()
Dim EApp As Object
Dim sAttach As String
Set EApp = CreateObject("Outlook.Application")


Dim EItem As Object




Dim path As String
path = "W:\BUYERS\Xmas files\"


Dim RList As Range
Set RList = Range("A2", Range("a2").End(xlDown))


Dim R As Range


For Each R In RList


'Set EItem = EApp.CreateItem(0)

With EApp.CreateItem(0)
.To = R.offset(0, 2)
.Subject = "Peak Planning - Xmas 2022 - Information and Data - " & R.offset(0, 1)
sAttach = R.offset(0, 3) & ""
'add attachment if there is an attachment and it it does exists
If Len(sAttach) And Len(Dir$(path & sAttach)) <> 0 Then
.Attachments.Add (path & sAttach)
End If
sAttach = R.offset(0, 4) & ""
'add attachment if there is an attachment and it it does exists
If Len(sAttach) And Len(Dir$(path & sAttach)) <> 0 Then
.Attachments.Add (path & sAttach)
End If
.Body = "Dear Supplier, " & vbNewLine & vbNewLine _
& "Please find attached spreadsheets to help with stock planning for the coming peak season." & vbNewLine _
& " - High Volume Build (DC10 Only) " & vbNewLine _
& " - C line & Lids off Build Data " & vbNewLine _
& vbNewLine & vbNewLine & "Many thanks" & vbNewLine & vbNewLine & _
"Kind regards,"
.Send


End With


Next R



End Sub

[valquiriaa]

Thanks heaps!! Really appreciated :)

[snb]

See: https://www.snb-vba.eu/VBA_Outlook_external_en.html#L_3.1.4