Automate a mail from excel with attachment

joe2687

Hi,

I'm trying to automate a system through excel for sending emails to customers.
What's happening at the moment is that information is being manually entered into a spreadsheet for a customer (sales order, purchase order, contact, address etc). Then that customer has to be emailed a copy of sales order acknowledgement PDF, with a generic text body (Thanks you for ordering blah blah)


What I'd like to do (and I've no idea if it's possible) is to automate this procedure so that, for example, you'd have:


Customer: Joe Bloggs
SO: 12345
PO: 54321
Email: j.bloggs@whatever.com; sales@whatever.com


If there was some sort of script/macro you could run in Excel to make it take the above details for any customers (all fields under same headings, with multiple emails) and open a generic mail template like:


Dear [customer name]

This is a confirmation email relating to your order [SO]


Regards,
Accounts.


Also, I have a folder with various sales order acknowledgements PDF's (all named after the SO eg: SO12345.pdf_ - is there a way to automate attaching this files to said email?



Is this possible at all?

Fysh

It sounds like you want to start off looking at MailMerge (see tutorial here for starters), and possibly get a bit clever with scripting if that's not enough (see, for example, the resources here).

MailMerge is basically a way of getting Word to generate customer-specific versions of letters from a template file and a data set (your Excel sheet). Adding attachments will require some work with a Macro of some sort (eg see here).

Overheal

Why does my spideysense say that this is something that is probably manageable using Access?

joe2687

Thanks for the replies - I'll look into what you said Fysh and post back results

Fysh

Overheal wrote: »

Why does my spideysense say that this is something that is probably manageable using Access?

Because this sounds like one of the many cases of a requirement that, long-term, is probably best served by a database system, but which has been served initially by a spreadsheet system and is now approaching the point where moving from one to the other will be non-trivial in terms of time and effort required?

It's worth noting that Microsoft have documentation here on importing Excel spreadsheet data into Access, and more documentation here on how to use data from an Access db for mail-merge purposes.

As a one-off, using Excel is probably easier. As a long-term thing, moving to something database-driven like Access is worth considering as it will make some things easier.

joe2687

Ideally I think in the long term, it's worth noting that a well designed CRM system should be able to handle this. This is again in process plaaning and scheduled later this year - I'm looking for a fix I can implement to take some pressure off the person who is manually having to enter these details, and send these emails.

Renno

Have a look at the solutions offered at http://www.rondebruin.nl/sendmail.htm

Appreciate that the solution is not as pure as starting off with some form of db, but there's a lot the OP should be able to work with there

joe2687

Renno wrote: »

Have a look at the solutions offered at http://www.rondebruin.nl/sendmail.htm

Thanks - yes google got me here a few times and had a good look at it. Seems to be the answer for sending all/some of a workbook through an email - the functionality I'm looking for is merely a mail "button" if you like - to generate a mail template from information contained in Rows A,B, C etc and automatically attach a file from a pre-defined location (using the same naming convention as data in ROW A) to send to email recipient.

Renno

The code on the examples on that link will do exactly what you want - you may just have to change a few things to get it to exactly match your requirements.

Use http://www.rondebruin.nl/mail/change.htm as a basis.

I'm no expert (so can't really help with how you have to change the code to get exactly what you need), but managed to carry out a similar task last year - starting with this code and changing a few things.

I have a macro that sends out mails on a Wednesday morning when a cell reference reveals that a report is overdue. An attachment can be included with that (I think) . All you need to change is to have the trigger as a button instead of a cell value

joe2687

Renno wrote: »

I have a macro that sends out mails on a Wednesday morning when a cell reference reveals that a report is overdue. An attachment can be included with that (I think) . All you need to change is to have the trigger as a button instead of a cell value

OK cheers - going to have a look at this tonight. My coding skills are extremely limited so it's going to be trial and error I imagine.

Overheal

All else fails, if it's for a business reason, and you can justify the expense versus the future labor costs, you could always hire a well versed consultant to implement a database program for you rather than try to fumble around and maybe get it half right. Time is money. I don't change my oil, I have to study mechanical engineering...