Here's the situation:

• company #1 has a web site with a form on it
• it gives all the data collected from the form to company #2 in the form of the occasional Excel spreadsheet
• the data includes the usual info like name, address, email, etc, and an opt in or out field
• company #2 is looking for a way to more or less automate the handling of these spreadsheets they receive
• if an entry on the form has both an email address and an "IN" in the "Opt" field, they want to send that person and email
• the email will contain standard text (no need to fill in from other fields in the spreadsheet) and a few PDF attachments

Initially I was told that company #2 was requesting a database of some sort to keep track of all this information, and wanted to be able to send these emails out via the database. I have some reservations about this method:

• the spreadsheets are always arriving, so there would need to be a way to tell if an entry had been sent an email already - this might be difficult in an automated system
• I am very inexperienced with Access
• it might be easier in the long run to simply order each spreadsheet as you receive it so that you only saw emails and opt ins, then just copy and paste all the email addresses into an email and send it

Seems to me that last method would be much simpler, but I don't think it's as automated as they'd like. It seems to me that company #2 wants to have the following system:

• plug in the new spreadsheet
• click a button to send the email to everyone who hasn't gotten it yet

Is that even possible with Access? I think that's their preferred application since they all probably have it.

I'd really appreciate any help with this. I'm not even sure where to get started...

ps-I've attached a sample of the kind of spreadsheet Company #2 receives

I don't know anything specifically about Access, but this sounds like it would be a LOT easier in the form of a Database rather than spreadsheets. Instead of sending a complete DB refresh you would make updates and inserts as needed to the DB. You can also keep a column with a date and timestamp in it to know exactly when the corresponding email has been sent.

You could have the whole thing from start to end as a single DB, or make an intermediary step of sending data between sites/businesses as CSV and then parsed into a new DB at the second site/business.

I'm just not sure how to go about this.

I meant to add that there's only one part of the process that can't be changed, and that's how the information is sent to company #2. Sadly there is nothing to be done about that. It's weird, because I'm sure that the spreadsheets are generated from company #1's database, but this is how they chose to send the info...

I'm not an access person, but this seems like a task a database and some scripting glue could do in its sleep. The only state you need is a list of email addresses that have been emailed. The rest is "look at address, if it isn't in the list, email and add to list, otherwise do nothing"

I don't know if access is the right tool, but mysql/perl/php should be able to do this easily.

Matthew

Company #2 can keep receiving the spreadsheets and do whatever else they want with them. They just wouldn't be used to do the work you've described. With your basic description it seems like it would be pretty straight forward to do with PHP and MySQL using a single table.

The emailing and attachments can also be handled with PHP, though I'd suggest using PEAR to allow SMTP mailing instead of PHP's normal "mail()" function.

You can explain this to company 2 as the difference between a clean and manageable solution costing X dollars or a convoluted solution that will cost 2 to 3 times as much. That should help them make the right decision.

I agree with the others. The hardest problem is automating access to the Excel file. If you could get them to change it from XLS to CSV, this is something that would take someone about a day to do.

... A process which itself can be automated on your end fairly easily ...

If only it was that simple. I left my last company because change was next to impossible, even when change made sense and saved money. The lifers that worked there just didn't want to change, hence the finance software was still running on NT4, at least we did get to virtualize that and get it off the dying hardware.

Sigh

Yes, this is possible to do with Access. Since I'm in Belize at the moment, I don't have time to whip up a proof of concept, but it is possible.

Check out using the blat dll as a free way to send email from Access without automating Outlook (ick!). If you're still hammering away at this after Nov 8th or so when I get back, I'd be happy to help you out.

Thanks for the offer, John, but my needs are more immediate. I appreciate it, though.

I'm going to bite the bullet on this one. Is there someone here who will do this for me for $100? That's how much I'll get out of it, and I'm happy to send someone some money through Paypal to do it.

I need this very quickly, like by the end of today (Wednesday) or early Thursday. I'd muddle through it myself otherwise, and try to get more familiar with Access. But I'm busy all day today and can't devote any time to it.

The requirements:

• only use MS Office apps (company #2 is more like a couple people - they don't have SQL or anything - just Office)
• end result must be usable by a non-techie
• essentially two steps to the solution: 1) automated process to import new Excel spreadsheets; 2) automated process to create an email to unemailed entries (in a bcc) containing standard text and some attachments

As previously stated, the entries to be emailed are the ones that contain an email address and have the value "IN" for the "Opt" column.

A nice bonus would be an additional automated process that would spit out a simple printout of all the names that have opted in, but don't have an email address. This is secondary though.

No takers? I'm really in a pinch here... On top of it all, I got horribly sick today and I'm not feeling up to learning Access.

Nobody interested? I think I might be able to get $150 for the job. It's someone's for the taking. PM me if interested.