Setting up a process to email specific attachments to different lists of emails

Hi,
I am trying to think of a way to enable different excel workbooks to be emailed out to different people.

Here is an example of the background:
i have 20 excel workbooks,
these will be generated every month, and the end result is for them to be emailed to the corresponding people.

the names of the workbooks will always be the same each month.
so Workbook1, Workbook2, Workbook3.... so on...

I would like for Workbook1 to email to client@thisaddress.com
and Workbook2 to email to client@thisotheraddress.com and so on..

i'm not sure what is the best approach to this, i use SSIS so i'm wondering is there some way to set this up there.
Or would it be possible to use a macro to do this.

has anyone done this before, and if so have you some sample code i could look at to see if i can generate this process?

kind regards,
p
PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
There are quite a few examples on Ron deBruin's website.  Here's a link to the relevant page:  http://www.rondebruin.nl/sendmail.htm

It appears to me that you could have a list of workbook names (say, in a specified folder) and the column to the right would have the email address for each successive workbook.

You could then process that list, either opening each workbook and using SendMail, or just construct the email with the workbook as an attachment.

I think this gets you started.

From this, perhaps you could choose the method you prefer, and perhaps a mockup of your list in a workbook and start on the macro.  From your comments, it appears that you might be a "take an example and go!" type of person so I'll stop here, though if further assistance is needed, I can help you finish out the code.

If so, please advise if you're using Outlook or form of email service (whether Outlook can be used or if CDO is necessary.)

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
I will give it a go Dave, thanks for the link. I will be using Outlook.
Going to give it a go now, i'll let you know how i get on.

Thank you!
0
PutochAuthor Commented:
http://www.rondebruin.nl/mail/folder2/files.htm

Thank you for that link, i used this reference, and it worked a gem.
I might try and see if there is another way of doing this, so that I can set something up so that there is a button on the worksheet i'm trying to email, so when the worksheet is checked, the person can just click send and it will email to the proper candidate. I'm sure there is something on line to do this.

thank you!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dlmilleCommented:
Thanks.

In that situation, something as simple as the .SendMail method should do the trick.

Dave
0
PutochAuthor Commented:
Oh i wonder would anyone know how to add something to this VB to avoid the mail from sending if there was no attachement?

You see this macro lets you prepair a list of all of the peopel you want to email and the attachements that they should recieve.

i want to set this up so i can run it monthly/weekly or when ever, and sometimes some of the people on this list may not have an attachmenet for that perticular time.
the email will still be sent even though there is no attachement.

how can i avoid this?
thank you
0
dlmilleCommented:
Have your code check to see if there is an attachment (check to see if the file exists, or check the file's date if its being updated, etc.) before kicking off the email.

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.