Mail merge - bespoke attachments?

[mrhall]

Our exam officer as a folder with 150 pdfs in - these need individually emailing.

 

Anyone found a way to create a mail merge that could attach a different pdf to each email

(and yes, we can easily create a list of names - email address - attachment file name)

 

I can find things we could pay for - but hoping someone has already solved this...)

 

Thanks

 

Peter

[mavhc]

https://www.process.st/how-to/use-power-automate-to-send-emails-from-excel/

[bobsmith]

My Macro to do this is here, basically a mailmerge - have their email address and the file name as columns:

 

Sub Send_email_fromexcel()

Dim edress As String

Dim subj As String

Dim message As String

Dim filename As String

Dim outlookapp As Object

Dim outlookmailitem As Objec

tDim myAttachments As Objec

tDim path As String

Dim lastrow As Integer

Dim attachment As String

Dim x As Integer

x = 2

Do While Sheet1.Cells(x, 1) <> ""

Set outlookapp = CreateObject("Outlook.Application")

Set outlookmailitem = outlookapp.createitem(0)

Set myAttachments = outlookmailitem.Attachments

path = "C:\your file path here"

edress = Sheet1.Cells(x, 1)

 

subj = Sheet1.Cells(x, 2)

filename = Sheet1.Cells(x, 3)

attachment = path + filename

outlookmailitem.To = edress

outlookmailitem.cc = ""

outlookmailitem.bcc = ""

outlookmailitem.Subject = subj

outlookmailitem.body = "Please see your Resit Grades attached" & vbCrLf & "Kind Regards" & vbCrLf & "xxx School"

myAttachments.Add (attachment)

outlookmailitem.display

outlookmailitem.send

 

lastrow = lastrow + 1

edress = ""

x = x + 1

Loop

Set outlookapp = Nothing

Set outlookmailitem = Nothing

End Sub

Edited February 29, 2024 by bobsmith