Solved

Emailing attachments to multiple recipients based on file name

Posted on 2006-10-24
10
169 Views
Last Modified: 2010-04-30
We have software that will produce reports in PDF and save them to directories based on client number and the date they were created.  The software will email reports but requires that you insert recipient emails for each report, each time it is run.  I am trying to write something that will find those PDFs and email them out on a regular basis.  The reports will always have their report number and trailing digits so multiple versions of the same report will not replace existing PDFs (ex. 0945101.pdf and 0945102.pdf).  The last 2 digits are incremented for each report with the same number.  I have emailed with attachments, used databases, and sorted through files and folders looking for changes.  I can find the files that are new and email them.  The problem is, how to setup something for us to easily manage, which format to use, and is easy for me to code.  The idea is that we would set it up once and not have to do it each time we run a report to email.

As I can see it now, there are a few possible situations for clients:
1.  One person gets all reports
2.  Individuals get one or more reports based on report number (09451*.pdf based on above example)
        Some reports may go to more than one person
3.  One or more individuals get one or more reports each and another individual gets all remaining reports
4.  A combination of any of the above

I thought about having a file or database that would allow me to designate a catch-all email (for all reports not going to specific individuals and new reports added that are not setup), an all reports email, and emails with specific reports.  I'm not sure how to structure it or how to loop through the files to attach them to email.  Let me know if I need to clarify.

Thanks,

Traigo
0
Comment
Question by:traigo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17799238
Hi traigo,

I would set up a database that shows who gets what, open a recordset, loop through the users, and then
email out the reports as attachments.  Without more specific info from you, it's hard for me to get more
dspecific on the details...

Regards,

Patrick
0
 

Author Comment

by:traigo
ID: 17799260
One of the main problems is how to structure the data.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17799480
I am thinking two tables, and assuming all the files are stored in a common directory:

tblUsers
----------------------------------------------------------------
UserID (PK)
UserLName
UserFName
UserEmailAddr

tblGetFiles
----------------------------------------------------------------
UserID (PK, FK)
FileNameMask



So, if user 123 gets 0945101.pdf, 0945102.pdf, and any that match 099*.pdf, that user gets three records in tblGetFiles:

123                0945101.pdf
123                0945102.pdf
123                099*.pdf



Base a recordset on the query:

SELECT u.*, f.FileNameMask
FROM tblUsers u INNER JOIN
    tblGetFiles f ON u.UserID = f.UserID


Then loop through the recordset, and create an email message for each record, and attach 1+ file to each message.
It will be slow, but it's possible.

Patrick
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:traigo
ID: 17800630
The files are stored in directories based on the client name and date of the reports like:

reports
  |_client-100
        |_20061024

I can sort through all the files in each of the folders/subfolders and find out if they are new.  I want to send one email with all files attached to each recipient, so they won't get 20 emails. (pdfs are an average of < 20k)
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 17800656
Still doable, but that will certainly require that you focus on the recipient side rather than the file side.
0
 

Author Comment

by:traigo
ID: 17800682
So, using your database model above, how would I accomplish this including the catch-all acccount.  If I were to use *.pdf that would select all pdfs, but how would I select all pdf's not sent to an email addres?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17802607
I'd have to think about that; the answer depends a lot on the directory structure.  I am thinking that
getting the whole thing working is too big a scope for a single question.

Patrick
0
 

Author Comment

by:traigo
ID: 17805130
Not sure if it helps but it's the only nvarchar(max) and the only field in the record larger than 254
0
 

Author Comment

by:traigo
ID: 17805132
sorry, wrong question
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question