How to write values from sheet1 to sheet2 and attach .xlsm File to Gmail and send email

[SSM34]

data sheet.jpg

I have a excel file that has two sheets.
Sheet1 has few columns and each column contains dates(reminder dates for documents expiring such as insurance card expiration date, license expiration date etc etc).
1. I want to be able find all dates that are expiring within next 30 days or less from today.
2. add them to sheet2
3. Send sheet2 via gmail to a few recepients using (smtp.gmail.com).

I have implimented all of this but its getting stuck at two points
Firstly, it only picksup the first date it encounters that meets the criteria and writes it to sheet2. Thats it. Does not go write next field.
Secondly it fails to send email via Attachments.add

[TMS]

Post a sample workbook including your current code.

[SSM34]

the error its giving is runtime error 13 "type mismatch"
source_file = "\...\VBA_EmpReminderProgram\" & ThisWorkbook.Worksheets(1).Cells(4, "N").Value

.Attachments.Add source_file

also pl see datasheet.jpg in my first post for code and datasheet.

[SSM34]

Public Sub Send_Reminder_List()
Dim myMail As CDO.Message
Set myMail = New CDO.Message
myMail.Configuration.Fields.Item("/cdo/configuration/smtpusessl") = True
myMail.Configuration.Fields.Item("/cdo/configuration/smtpauthenticate") = 1
myMail.Configuration.Fields.Item("/cdo/configuration/smtpserver") = "smtp....."
myMail.Configuration.Fields.Item("/cdo/configuration/smtpserverport") = 465
myMail.Configuration.Fields.Item("/cdo/configuration/sendusing") = 2
myMail.Configuration.Fields.Item("/cdo/configuration/sendusername") = "******"
myMail.Configuration.Fields.Item("/cdo/configuration/sendpassword") = "****"
myMail.Configuration.Fields.Update

'pp EXPIRATION CHECK AND SEND MAIL
Dim ppCell As Range
Dim source_file As Variant
For Each ppCell In Sheet1.Range("D2:D11")
If ppCell.Value = Date - 1 Then
Sheet2.Range("A2").Value = Sheet1.Range("A2").Value
Sheet2.Range("B2").Value = Sheet1.Range("B2").Value
Sheet2.Range("C2").Value = Sheet1.Range("C2").Value
Sheet2.Range("D2").Value = Sheet1.Range("D2").Value
End If
Next ppCell
ThisWorkbook.Save
source_file = "\VBA_EmpReminderProgram\" & ThisWorkbook.Worksheets(1).Cells(4, "N").Value
'source_file = Worksheets("Sheet1").Cells(3, "N").Value
With myMail
.Subject = "pp"
.From = "*****"
.To = "***"
.TextBody = "This is a sample email"
.Attachments.Add source_file
'Worksheets("Sheet1").Cells(4, "N").Value
On Error Resume Next
End With
myMail.Send
Set myMail = Nothing
'.Close SaveChanges:=True
End Sub

[SSM34]

added sample.xlsm

[SSM34]

Hi
Could you pl take a look at this?