VBA Multiple attachments to email with 1 personalised

Orfevre

New Member
Joined
Jul 11, 2022
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top