+ Reply to Thread
Results 1 to 15 of 15

VBA to send emails with different attachments to each seperate email

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    VBA to send emails with different attachments to each seperate email

    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
    Last edited by gillyr7; 01-10-2014 at 04:42 PM.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

    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.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    This works perfectly. Thank you so much.

  4. #4
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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?

  5. #5
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

    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?

  6. #6
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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.

  7. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

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

  8. #8
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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.

  9. #9
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

    Quote Originally Posted by gillyr7 View Post
    ... 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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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.

  11. #11
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

    Well we could add a timed delay.

    Please Login or Register  to view this content.
    is a five second delay.

  12. #12
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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.

  13. #13
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    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.

  14. #14
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    Re: VBA to send emails with different attachments to each seperate email

    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.

  15. #15
    Registered User
    Join Date
    09-20-2013
    Location
    Hartland, WI
    MS-Off Ver
    Excel 2013 Professional Plus
    Posts
    73

    Re: VBA to send emails with different attachments to each seperate email

    Thank you for all of your help so far!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vba to send email and attachments via lotus 8.5
    By dan2010 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-19-2015, 11:36 AM
  2. VBA to send multiple emails with different attachments
    By gillyr7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2014, 02:33 PM
  3. Problem using a macro to send multiple emails with separate attachments
    By joshthorntonwhite in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2013, 07:31 AM
  4. [SOLVED] Email send 2 attachments instead of 1
    By unley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2013, 01:47 AM
  5. [SOLVED] Need Macro that will create & send emails w/ attachments from a list in Excel
    By rollerden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2012, 07:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1