Excel recipient list to send attachments

[Idethrad]

Hi there, I have started working on a macro that will allow me to use an excel workbook to manage emails that I have to send every day. Each of these emails has anywhere from 5-10 reports attached to it as PDFs and each email has to go to a different list of people.

I have been working on this for a little while now
and have run into all types of trouble. Having decided
to return to the basics I want to give you my plan for
the code here so that one of you geniouses can tell
me if this is even possible . And hopefully when
I am finished building this it can be useful for
everyone else out there too.

List of requirements:

Retrieve the recipient list for an email from a row in excel
Retrieve the Subject from a row in Excel
Retrieve the body from a row...

Look up the names of each attachment from a row
Attach these files to the email (retrieved from a
static folder filled with the files before
executing the macro)

Confirm the contents before sending

My research has hinted that all of these things
should be possible but its such a big job.

I am super keen to hear all your thoughts on this!!

Thanks in advance for any help you can offer

[Leith Ross]

Hello Idethrad,

Welcome to the Forum!

This macro should get you going in the right direction. If you need to make any changes let me know. The summary explains the worksheet layout. The first cell is set to "A1". You can change this if you need to. It is marked in red.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

[Idethrad]

This is MAD!!! Thanks so much Leith your a lifesaver.

I have installed this code and shaped it to my existing spreadsheets and its very impressive

Couple of questions just so that I understand everything though:

When I run this on a large set of data (50-150) it gets stuck with all of the emails open on the screen. What is the command for saving them to the drafts automatically.

It would be cool to use filters on the rows to be able to selectively send smaller groups of emails from this list, is there a way to only consider non-hidden rows in this function?

How important is the clean up procedure at the end of the function? Obviously setting the variables to Nothing is important but if I already have outlook open does the macro have to close it or can I safely remove that?

Thanks again so much for all your help.

[Leith Ross]

Hello Idethrad,

Let me start with the first question...

When I run this on a large set of data (50-150) it gets stuck with all of the emails open on the screen. What is the command for saving them to the drafts automatically.

I am not sure what you mean by a set of data. Can you expand on this for me?

Your second question...

It would be cool to use filters on the rows to be able to selectively send smaller groups of emails from this list, is there a way to only consider non-hidden rows in this function?

This can be done. I would need to modify the macro to accommodate filtering the data.

Your third question...

How important is the clean up procedure at the end of the function? Obviously setting the variables to Nothing is important but if I already have outlook open does the macro have to close it or can I safely remove that?

The macro opens a separate instance of Outlook and closes the instance it created. So if Outlook is already open, the macro will not disturb your work. The macro also releases any created objects from memory.

Sincerely,
Leith Ross

[Idethrad]

What I mean by sets of data is if I run the function on a worksheet now that has 50-150 rows in it, each row generates an email. This is great and works perfectly except that each email created sits waiting for the "would you like to save this email to drafts folder" [yes, no, cancel]. messagebox. I love being able to see the emails before sending them from the drafts folder and clicking yes for each email is a small price to pay. I was just wondering if there was a way to skip this step?

As for filtering data: would the best way to do this be to check each row as it was processed to see if it was hidden or not?

The clean up procedures do open a new instance of outlook but they seem to shut all instance upon completion. This isnt a big deal at all Im just curious

Anyway I cant tell you how excited I am about all of this, that you again.