Sending Saved PDFs in a folder

[darcus]

Hi

I hope you can help with a little sheet of mine. The sheet is structered as follows:
SCRATCH - Column A contains Reference numbers
CUST - Column A contains Reference numbers. Column AF contains email addresses.
C:\PDF\Sheets - Is the folder in which files are saved.

Files are made with my spreadsheet as PDF files. The name of the file is structured as follows:
SHEET XXX-YYYYY.pdf

XXX is the same as the reference number in SCRATCH and first column in CUST.
YYYYY is a number generated by the sheet and is date dependent.

What I want to do is Read column A for XXX in SCRATCH to find email addresses which are looked up against CUST sheet in column AF. Then make an email and attach SHEET XXX-YYYYY.pdf (the XXX named PDF is the same as the XXX in the SCRATCH sheet). Then send the email. Then it cycles to the next XXX on SCRATCH and starts again.

The thing is, i don't have OUTLOOK on my system. I was wondering if I used CDO if that would be easier.

I have come across the following code on this site...

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SendMultipleEmails()
On Error Resume Next


Dim Mail_Object, OutApp As Variant, lastRow As Variant
Dim i As Integer
Dim sht As Sheet1


Set sht = ThisWorkbook.Worksheets("Sheet1")


    lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
  


For i = 2 To lastRow


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


    With OutApp
    .Subject = "Testing MultiEmails"
    .Body = "Hello TEST !"
    .To = Cells(i, 1).Value
    .Attachments.Add Cells(i, 2).Value
    .send
    End With
        
    If i = lastRow Then
        GoTo Done
    Else
        Calculate
        Sleep (5000) ' delay 1 second
    End If
Next i
   


debugs:
If Err.Description <> "" Then MsgBox Err.Description


Done:


MsgBox "All emails have been sent. "

End Sub

Thank you.