I'm very much a novice to VBA and any help is very much appreciated.

I need to send each client every month a couple of documents for that month (could be anywhere from 0 to 5 documents usually). Every month a new folder is created and all the files for all clients start getting kept in there, in the beginning of the following month we need to send all of these to each client.

I was able to put together a spreadsheet where the outlook application can parse everything out (to, CC, subject, body) and generate emails personalized to each client but I'm having issues finding the logic where it will go into the folder mentioned in column G and find all the files that contain a company's name which is in column A. The folder in column G will be updated automatically every month since the folder changes so no worries in having the right path in there.

Below is my script where I was just attaching a single file and had the full path to the file in column G, where as now I want to have it just go into the folder level and find all the files that have each company's name in there.:

Sub SendEmail(what_address As String, carbon_copy As String, subject_line As String, mail_body As String, Attachments As String)
Dim olApp As Outlook.Application
Dim myMail As Outlook.MailItem
Set olApp = CreateObject("Outlook.Application")

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

olMail.To = what_address
olMail.CC = carbon_copy
olMail.Subject = subject_line
olMail.Body = mail_body

 olMail.Attachments.Add Attachments

olMail.Display

End Sub


Sub SendMassEmail()

row_number = 1

Do
DoEvents
row_number = row_number + 1

 Call SendEmail(Sheet1.Range("B" & row_number), Sheet1.Range("C" & row_number), Sheet1.Range("E" & row_number), Sheet1.Range("F" & row_number), Sheet1.Range("G" & row_number))

Loop While Sheet1.Range("A" & row_number) <> ""

End Sub