Solved

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

Posted on 2012-04-02
6
232 Views
Last Modified: 2012-04-03
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
Comment
Question by:Putoch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37798408
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
 

Author Comment

by:Putoch
ID: 37798709
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
 

Author Comment

by:Putoch
ID: 37801271
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 42

Expert Comment

by:dlmille
ID: 37801635
Thanks.

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

Dave
0
 

Author Comment

by:Putoch
ID: 37803205
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37803363
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

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question