Solved

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

Posted on 2012-04-02
6
228 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

685 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