Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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
0
Putoch
Asked:
Putoch
  • 3
  • 3
1 Solution
 
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now