Excel VBA code to write Print Area as PDF and email

Excel VBA code to write Print Area as PDF and email
Hello, I have created a excel workbook that uses =Indirect(“Data!A &RowIndex”) to pull data through into my news letter layout.

In my news letter I have:
Member First Name in cell (Data!A1 &RowIndex)
Member Last Name in cell (Data!B1 &RowIndex)
Arrears in cell (Data!C1 &RowIndex)
Dues in cell (Data!D1 &RowIndex)
Total in cell (Data!E1 &RowIndex)
Email address in cell (Data!F1 &RowIndex)
The email body in Form cells B2:I48
the email subject is “Monthly Meeting”

I have a list of members’ Names in a worksheet called "Data" in Columns A & B

The email address is also in “Form” G5 if it is easier to extract from there.

The formula =Indirect(“xxx &RowIndex”) updates each newsletter and my current code produces an email with Print Range embedded in the news letter. I previously adapted a routine that printed the newsletter for each member to be mailed out. It can be emailed, but manually. I am just looking to amend this code to print to PDF, and then send the emails automatically.

It is currently locked to two outputs during the testing phase. This is shown on the “Form” sheet at cell “C5”.

Here is my current code:

Public Const APPNAME As String = "Sample-1"
Option Explicit

Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim MailDest As String
Dim i As Integer

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Sheets("Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")

If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow
Range("RowIndex") = i
ActiveSheet.Range("B7:I48").Select
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
'.Introduction = "This is a sample worksheet."
.Item.to = "(email addresses here)"
.Item.Subject = "Monthly Meeting"
.Item.Send
'.Item.Display
End With
Next i
End Sub

Thanks Carim. Nesting loops in VBA is beyond me. I usually hunt on line for something readily adapted, as in my example where I altered the output from hard printing to email. This is why I requested online help.
While I have a mental picture of what needs to be done I don't know how to do it.
Regards...George