I've the following problem and thought there should be a way to automate it to a certain degree.
We must collect a lot of documents once per year from our suppliers we work together.

The program team did this manually til now which took A LOT OF TIME and isn't really efficient.
I've made a macro in excel that pulls a text from a cell which is the message text for the mail. It replaces some keywords with information in other cells like supplier name, resource name etc...

Private Sub CommandButton1_Click()
Sub SendEmail(what_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)

olMail.To = what_address
olMail.Subject = subject_line
olMail.Body = mail_body
olMail.Send

End Sub

Sub SendMassEmail()
Dim lastRow As Long


row_number = 1


Do
DoEvents
row_number = row_number + 1
Dim mail_body_message As String
Dim full_name As String
Dim Resource_name As String
mail_body_message = Sheet1.Range("J2")
full_name = Sheet1.Range("B" & row_number)
Resource_name = Sheet1.Range("D" & row_number)
mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
mail_body_message = Replace(mail_body_message, "resource_name_replace", Resource_name)
Call SendEmail(Sheet1.Range("A" & row_number), "ZeroChaos leverantörsgranskning", mail_body_message)
lastRow = Sheet1.Range("A99999").End(xlUp).Row + 1
Loop Until lastRow = "6"

MsgBox "Complete!"

End Sub
The only thing that's missing (which would be the icing on the cake):
Is it possible before sending the mails to recipients defined in a cell (have a look at the screen) that excel pulls information from another cell and writes down the information from there, also replacing keywords and attach that to the specific supplier?

\1

E.g. I just want to sent a mail to the mail address in A which also includes a word attachment which pulls information from email text says something like "dear valued supplier, please confirm that the resources: replace_resource_name) are still working under a contract with you. Print, sign and sent the document back to us via mail as a proof of employment." replace_resource_name should be the names written in D.
So I need either once code before mine from above or another solution which does the trick.

Every supplier need their own attachment with their resource names in it.

Is that even possible or am I thinking "too easy"?

I'm using Office 2013. If you need more information please let me know.

Thanks beforehands.