Looping Email- Multiple Attachments but I Only Want Selected Attachment

**cschoyer** · 06-29-2012, 01:21 PM

Hopefully someone will be able to help me figure this one out. I have done some research, and so far no luck.

I have a function that loops through my spreadsheet and checks the due date for an invoice and whether or not it has been approved and sends out a reminder email to the approver with the invoice as an attachment.

The first email sent works great, the second attaches the first invoice and the newly selected invoice, the third email attaches the first 2 and the selected one, and so on.

Does anyone know of a way to clear out attachments after the mail is sent so only the selection attaches to the next email? I appreciate any help I can get!

Here's my coding:

Please Login or Register  to view this content.

**Leith Ross** · 06-29-2012, 03:27 PM

Hello cschoyer,

The Attachments is a Collection object which is part of the Message object. I have added the line to clear all the attachments after the email is sent. The added line is in bold.

Please Login or Register  to view this content.

**cschoyer** · 06-29-2012, 03:47 PM

When I run this excel stops working and I get a window that says "Microsoft Excel has stopped working - Windows can check online for a solution to the problem and try to recover your information."

...?

**Leith Ross** · 06-29-2012, 03:53 PM

Hello cschoyer ,

I am not sure why you are getting that message. The code line added is part of the standard Windows CDO library of functions and should not cause Excel to crash. Perhaps it has to do with the timing of sending the attachment before it is deleted. Let's move the the delete statement to the beginning and see it that works.

Please Login or Register  to view this content.

**cschoyer** · 06-29-2012, 04:25 PM

Hm, even with the delete all at the beginning, it still causes excel to crash.

**Leith Ross** · 06-29-2012, 04:31 PM

Hello cschoyer,

Are any of the emails being sent or does it crash on the first email?

**cschoyer** · 06-29-2012, 04:31 PM

it crashes on the first email

**Leith Ross** · 06-29-2012, 06:03 PM

Hello cschoyer ,

After looking over your macro, I found a few problems. Since this obviously only part of the code, these may not be problems but simply variables that set elsewhere.

Mail_Server this not set anywhere in the macro. It should be set to name of the mail server you are using, like "www.google.com".
Your port is set to 25 which is fine if the server does not require you to log in.
iConf was incorrectly assigned as CDO.Configuration when it should have been CDO.Message.Configuration.

Make the needed corrections to 1 and 2 in the code below. Once you have, try running it again.

Please Login or Register  to view this content.

**cschoyer** · 07-02-2012, 09:16 AM

Your right, this is only part of my code so issues 1 and 2 are taken care of elsewhere.

Thanks for picking up on error 3- this code works brilliantly, exactly as I wanted it to. Thank you so much for your help!