Hello, I have a macro that I want to send 3 attachments to each person in the "RecipientList" however two of the attachments will go to everyone and one is a personalised attachment. It currently works but sends them as 3 separate emails and would like them to go once with the 3 different attachments. I am not sure how to group the attachments to the corresponding recipient email to make this work.
Any help would be appreciated.
Any help would be appreciated.
VBA Code:
Sub SendSummary()
Application.ScreenUpdating = False
Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")
Dim EItem As Object
Set RecipientList = Worksheets("RecipientList")
Sheets("RecipientList").Activate
Dim path As String
path = "D:\09\EOM\2022-2023\01\Templates\Summaries\" 'put your path here
Dim RList As Range
Set RList = RecipientList.Range("A2", Range("A2").End(xlDown))
Dim R As Range
For Each R In RList
Set EItem = EApp.CreateItem(0)
With EItem
.SentOnBehalfOfName = "Test@gmail.com"
.to = R.Offset(0, 1)
.Subject = R.Offset(0, 3) & " Summary"
.Attachments.Add (path & R.Offset(0, 2))
.Body = "Hello " & R & vbNewLine & vbNewLine _
& "Please see attached " & R.Offset(0, 3) & " Receipts." _
& vbNewLine & vbNewLine & "Thank you" & vbNewLine & vbNewLine & _
"Finance"
.Save
End With
Next R
Set EApp = Nothing
Set EItem = Nothing
Sheets("Dashboard").Activate
Application.ScreenUpdating = True
MsgBox "Done, emails saved in drafts"
End Sub