Search PDF File in Directory and send email using SMTP

[bhsd]

I have customer table and i want to send email with pdf attachment that match search criteria by custID.

Cust Table :
custID, custName, custEmail
001, ITCOM, itcom@...
002, PCGLOBAL, pcglobal@...

File Directory :
999-001-1111.pdf


999-002-1112.pdf

The email will send to itcom@... with file attach : 999-001-1111.pdf and pcglobal@... with file attach : 999-002-1112.pdf

i have hundreds email customer in customer table, i want to send email with PDF attachment to all customer who have match PDF filename with custID (001,002,....). yes i need a send button to send all match this criteria.

Please help how to achieve this using access VBA.

Thank you very much
bhsd

[rpeare]

you could like this:

dim rst as recordset
dim sSourceFolder
dim fs
dim fsFolder
dim fsFile 
dim sCustID as string
dim sFileID as string
dim sEmail as String

set rst = currentdb.openrecordset("SELECT CustID, CustEmail from tblCustomer")
sSourceFolder = "C:\test\"
set fs = createobject("Scripting.filesystemobject")

do while rst.eof <> true
    sCustID = rst!custid
    sEmail = rst!custemail
    set fsFolder = fs.getfolder(sSourceFolder)
    for each fsfile in fsfolder.files
        sfileid =  mid(fsfile.name, instr(fsfile.name, "-") + 1, instrrev(fsfile.name, "-") - (instr(fsfile.name, "-") + 1))
        debug.print sfileid
        if sfileid = scustid then
            'put your email code here sending method here
        endif
    next fsfile
loop

1. What this code does is cycle through your customers and extracts the customer ID
2. Cycles through all the files in a specific folder looking for a matching customer ID (c:\test\ in this example)
3. Send the file if a matching customer ID is found

this line

sfileid =  mid(fsfile.name, instr(fsfile.name, "-") + 1, instrrev(fsfile.name, "-") - (instr(fsfile.name, "-") + 1))

extracts the customer ID from the file name but I may not have it correct, you'll have to play around with it, I made it as generic as possible in case your customer ID's are more than 3 digits

so if your filename is

AAA-BBB-CCC.pdf

sfileid = MID("AAA-BBB-CCC.PDF", 4 + 1, 8 - (4 + 1))
or
sfileid = MID("AAA-BBB-CCC.PDF", 5, 3)
which should return
"BBB" as your customer ID

Lastly this assumes all your PDF's are in a single folder and have been exported prior to the time you run this code. it would rely on the target directory being cleared out regularly so only 'new' files are put there for email purposes.

anyway there are a lot of tweaks etc you can do to this (like only exporting a PDF if there are records to export and creating an email at the time the pdf is created instead of after the fact).

[bhsd]

Thank you for your help rpeare, after editing some code this works as i expected.