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!
Bookmarks