Hi there,

I am very new to the VBA world so please excuse my pace in understanding your brilliant responses.

The task at hand involves sending weekly emails to 4 different sets of recipients. With each set of recipients, they will receive a table of data in the body of the email that is relevant to them. The table of data in my worksheet is dynamic with respects to the number of rows it includes on a weekly basis.

I put together a code that allowed me to get far enough into automating the emailing process but I am now stuck at the stage where I have to select/copy/paste the dynamic data range to include in the body of the email. In my code below, I set up "ca11_table" and "ca12_table" as strings (they are the dynamic data ranges) and I want to include them as part of my "mail_body_message" which is cell K2 that includes the text of the email. ca11_table and ca12_table would be located in a different worksheet.

Thanks in advance for your support!




Sub SENDEMAILS(what_address As String, what_cc As String, what_bcc 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.CC = what_cc
olMail.BCC = what_bcc
olMail.Subject = subject_line
olMail.BodyFormat = olFormatHTML
olMail.HTMLBody = mail_body
olMail.Display


End Sub


Sub Sendmassemail()


row_number = 1


Do
DoEvents
row_number = row_number + 1
Dim mail_body_message As String
Dim full_name As String
Dim ca11_table As String
Dim ca12_table As String

mail_body_message = Sheet1.Range("K2")
full_name = Sheet1.Range("D" & row_number) & " " & Sheet1.Range("E" & row_number)
mail_body_message = Replace(mail_body_message, "replace_name_here", full_name)
Call SENDEMAILS(Sheet1.Range("A" & row_number), Sheet1.Range("B" & row_number), Sheet1.Range("C" & row_number), "Weekly Email Subjectline", mail_body_message)

Loop Until row_number = 10


End Sub