VBA to send emails with different attachments to each seperate email

[gillyr7]

Hello,

I am looking for a VBA that can automatically send emails to many different recipients, each with a different attachment based on the range associated with the email address.

For example, I have a range in A1:F30, with an email address in H1, with a different range in A32:F62, with a different email address in H32, and so on. This can go on for 100-600 ranges, or 30 rows x 600 ranges =18000+ rows, depending on the data.

I need the vba to send each range to the email address to the right of the range in column H.

I have a file attached, that shows an example format of the data for 3 ranges. Again, this needs to continue for a possible 600 ranges.

In the file, there is a VBA that does exactly what I need, except that it displays each range in the body of the email. The only change is that I need the range as an attachment for each email. This one change is what I am looking for.

Please do not direct me to Ron De Bruin's website as I have been there many times.

Please help.Test Sample.xlsm

[Tinbendr]

Change subject line, text , etc as required.

You'll have to add Outlook as a reference.

From the VBE, Tools, References, scroll down until you find Microsoft Outlook, Check the box, then OK. Then Save.

Please Login or Register  to view this content.

[gillyr7]

This works perfectly. Thank you so much.

[gillyr7]

Quick question, this code worked the way it was supposed to. However, the code ended up only sending 35 out of 600 emails that were in my file. (3,000 out of 41,000 rows). It looks like it mostly went in order for the first 30 emails (from top of file going down), but then the last 5 emails looked like they were toward the bottom of the spreadsheet. Any idea why this is happening? Is 600 emails just too much for Excel to handle at one time?

[Tinbendr]

It could be that the XlDown isn't properly picking up the correct range.

We might be able to search for 'Store' to properly define the ranges.

We might could use the email address also. Is the email address ALWAYS on the row of the start of the range? And is there any other text in that column?

[gillyr7]

Email Template Sample File.xlsmThe email is always in the row of the start of the range. There is no other text in that column either. I attached the file I am using, but switched to .Display instead of .Send. The first 30 or so ranges sent. The range in row 2189 was the last to send out of the first 30 ranges, so row 2252 did not send. Please note, the attached file in reality has about 42,000 rows, but it was too big to attach. 5 ranges starting between rows 25,000 and 35,000 also sent, but I could not see any differences in the ranges than from the beginning ranges.

[Tinbendr]

OK, I will have a look, but it'll be tomorrow before I can work on this.

[gillyr7]

Thank you. Another thing to point out... I experimented by deleting the first 20,000 or so rows, and the same thing happened with the new ranges at the top. the first 36 ranges were sent in an email, but stopped after that. (Earlier 35 ranges were sent, 30 from the top and 5 from the bottom). Since such a similar number of ranges were sent both times, I am wondering if it is actually a size issue.

[Tinbendr]

... I am wondering if it is actually a size issue.

It's probably a timing issue. I tested the code and it correctly selects the correct ranges, so I'm pretty sure it's a timing issue.

As the emails pile up, Outlook tries to manage them. But the thing is, Excel doesn't know the state of Outlook. It's just sending the data to Outlook without prejudice.

So, I'm assuming once we get this worked out, you'll turn the Display back to Send? If so, I found some code that checks the Outlook outbox. If any unsent messages are present, the Excel code will loop until they are gone thus correcting the timing issue.

If this doesn't work, you may have to post in an Outlook forum since my Outlook skills are thin.

[gillyr7]

There is nothing left in the outbox after it is done running as far as I saw. Yes, it should be switched to .send. Is there anything that would cause the code to send one every five seconds or even more? This does not need to go quickly.

[Tinbendr]

Well we could add a timed delay.

Please Login or Register  to view this content.

is a five second delay.

[gillyr7]

Is there a way to use that delay in between sending messages though? I really watched the code as it ran when I used .Display. It seems the ranges are being created faster than it can pull the email address. The first 35 or so worked, and after that, there were email drafts created that were the correct ranges in order, however, those drafts did not have any email addresses to send to. It was blank. I think it might work if we can put a 5 second delay in between sending each email address.

[gillyr7]

I have been thinking about this. Since Outlook can send at least 30 emails everytime, could we have macro loop until 30 emails are sent? This macro could then be copied for emails 31-60, 61-90, all the way to 600. One macro could call all 20 macros that end after sending 30 emails each giving some time in between each macro to make sure they all send. I am still new to VBA and do not know how to loop.

[Tinbendr]

could we have macro loop until 30 emails are sent?

Yes. I thought of this as well. I'm really trying to prevent you from having to baby-sit the code.

Since you've revealed your level of VBA knowledge , I thought I'd talk about the outbox code. That will not work with the Display since the mail is not actually sent, so it's not in the Outbox. I ran out of time last night to test it using Send.

Super busy at work so I'll have to wait until I get home to do more testing.

[gillyr7]

Thank you for all of your help so far!