Hi,

I'm trying to create a macro in excel that will send an email via Outlook with 2 attachments. 1 of the attachments will be unique to the individual and I want to be able to pick up the file name by referencing the file name column in excel e.g. .Attachments.Add ("D:\File\Document2_FullName.pdf") - Record 1 is John Smith so the file link will be "D:\File\Document2_JohnSmith.pdf". A loop will also be required so that it can move to the next record.

This is what I have so far -

Sub SendEmail(email_address As String, subject_line As String, mail_body As String)

Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

    With olMail
        .To = email_address
        .CC = ""
        .BCC = ""
        .Subject = subject_line
        .BodyFormat = olFormatHTML
        .HTMLBody = mail_body
        .Attachments.Add ("D:\File\Document1.pdf")
        .Attachments.Add ("D:\File\Document2_FullName.pdf")
        .Display    'or use .Display

End With
End Sub

Sub SendOfferEmail()

row_number = 1

Do
DoEvents
    row_number = row_number + 1

    Call SendEmail(Sheet1.Range("E" & row_number), "Subject Line", "Mail Body")
        
Loop Until row_number = 3

End Sub
Any assistance will be greatly appreciated.

Thanks!