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?
 
dlmilleConnect With a Mentor Commented:
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
 
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
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
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
All Courses

From novice to tech pro — start learning today.