+ Reply to Thread
Results 1 to 11 of 11

VBA to Outlook - Help with Looping

  1. #1
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    VBA to Outlook - Help with Looping

    Hi, I am a relatively new user and am needing help in looping a macro which will send customized emails to a list of recipients using data from Excel.

    I am using the code below, which works perfectly for the first row. However when Outlook opens, it generates identical emails on an endless loop. The intention is to send emails to all the recipients in Column P using the data from other columns until it reaches an empty row or cell.

    Any suggestions would be greatly appreciated! Thanks.


    Please Login or Register  to view this content.
    Last edited by MorganWJ; 08-25-2021 at 01:11 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    Take a look at a similar loop request that i helped with here: https://www.excelforum.com/excel-pro...ml#post5559778

    Without a sample file hard to test your routine, but looking at this line: Do While Cells("P12").Value <> "" it will always be true, because it continues to look at that one cell.

    In the link above, they were deleting the row after they ran their routine, so it will need to be tweaked a bit for you if your rows are not being deleted after each email is sent.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    Re: VBA to Outlook - Help with Looping

    Hi, thanks for responding. I uploaded a sanitized version of the file I'm working with if you wouldn't mind taking a look?

  4. #4
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    I cant test if the email portion works, but try this:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    Re: VBA to Outlook - Help with Looping

    Thanks so much! It is looping through the email addresses, but not the other fields. I guess I would need to reference the other cells to cycle to the next row as well. Each subsequent row in the "Replace" section has a unique entry that drops into the email body. This is probably a super simple for you , but I'm not sure how to change that reference? Could you push me past the finish line???

  6. #6
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    Opps thats my fault, i just noticed everything was hardcoded to row 12, let me see if i can update to work for everything..
    My bad..

  7. #7
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    Try this: Sorry about that

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    Re: VBA to Outlook - Help with Looping

    Thanks. Strangely enough, it's referencing the cell numbers in the email text now. Any ideas? Here's a sample:

    Good Morning,

    Employee U12W12 is identified as occupying space in two different buildings. We need your assistance to correct this discrepancy.

    The Space Management System indicates U12 is located in X12 D12.

    HR System indicates U12 is located in Y12 C12.

    If X12 is the correct location, U12 will need to update the location in HR System to reflect the correct building assignment. The instructions for correcting the building assignment in HR System are included below.

    If Y12 is the correct location, please respond to this email with U12 's workstation number and we will make the correction in GDI. If assistance is needed to identify the workstation number, please let us know. In order to provide assistance, please provide location details, including: floor, area, and room or suite number if applicable.
    Thank you in advance for your attention to this matter.


  9. #9
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    did you update this line to match your actual range of value?

    Please Login or Register  to view this content.
    Also, prior to the above changes, your email was being built correctly with valid values?

    Where is this located on your sample file?
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    862

    Re: VBA to Outlook - Help with Looping

    I found it in your original sample file, but just opening it the text inside the textbox seems to be hardcoded
    Attached Images Attached Images

  11. #11
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    Re: VBA to Outlook - Help with Looping

    So Close! It works in .display, but when I change to .send, it gives me a Run Time Error '287'.

    I changed this section below to fix the text box issue, so it now reflects the associated cell data in the email body.
    Please Login or Register  to view this content.
    Any ideas?
    Last edited by MorganWJ; 08-26-2021 at 10:23 AM.

+ 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. [SOLVED] Looping doesn't work, putting appointments in Outlook via Excel
    By Fean in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2020, 06:19 AM
  2. Replies: 1
    Last Post: 08-21-2018, 01:48 PM
  3. Outlook looping
    By Spirit ICT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-04-2017, 08:51 AM
  4. Looping through Filter and then copying the data so I can paste or put into outlook body
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-28-2016, 12:12 AM
  5. [SOLVED] Using Exell VBA to launch Outlook Email while separating lines in the body of Outlook.
    By Tazyote in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-27-2013, 01:18 PM
  6. [SOLVED] VBA Macro to print to Pdf format and place as attachment in Outlook (Excel & Outlook 2007)
    By Webman1012 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2013, 01:25 PM
  7. looping through range of cells & sending multiple emails with outlook
    By splosczynski in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2008, 01:49 PM

Tags for this Thread

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