+ Reply to Thread
Results 1 to 12 of 12

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
    896

    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
    896

    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
    896

    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
    896

    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
    896

    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
    896

    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.

  12. #12
    Spammer
    Join Date
    05-11-2023
    Location
    India
    MS-Off Ver
    Ms office 7
    Posts
    8

    Re: VBA to Outlook - Help with Looping

    Looping through HTML in VBA for Outlook is similar to looping through HTML in other VBA applications. The basic steps are:

    Obtain the HTML source code of the email message you want to loop through. You can use the HTMLBody property of the MailItem object to get the HTML source code.

    Create an instance of the MSHTML.HTMLDocument object and assign the HTML source code to its innerHTML property.

    Use the various methods and properties of the MSHTML.HTMLDocument object to loop through the HTML elements and perform the desired actions.

    Here is an example of how you can loop through all the links in an email message and display their URLs in the Immediate window:

    Sub LoopThroughHTMLLinks()
    Dim olMail As MailItem
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim link As MSHTML.HTMLAnchorElement

    Set olMail = Application.ActiveExplorer.Selection(1)
    Set htmlDoc = New MSHTML.HTMLDocument

    htmlDoc.body.innerHTML = olMail.HTMLBody

    For Each link In htmlDoc.getElementsByTagName("a")
    Debug.Print link.href
    Next link
    End Sub

    In this example, we first obtain the HTML source code of the selected email message and assign it to the innerHTML property of the MSHTML.HTMLDocument object. We then use the getElementsByTagName method of the MSHTML.HTMLDocument object to retrieve all the <a> elements (i.e., links) in the HTML source code and loop through them using a For Each loop. Within the loop, we access the href property of each link element and display it in the Immediate window using the Debug.Print statement.

    Note that you will need to add a reference to the "Microsoft HTML Object Library" in your VBA project for this code to work. To do so, go to the VBA Editor, select "Tools" from the menu bar, then "References", and check the box next to "Microsoft HTML Object Library".

+ 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