Extracting All Values from a For Each Loop in VBA

Learn how to extract multiple values from a For Each loop using regex in VBA and display them in an email. Step-by-step instructions for effective text extraction.
---
This video is based on the question https://stackoverflow.com/q/68600238/ asked by the user 'Isonj21' ( https://stackoverflow.com/u/16565781/ ) and on the answer https://stackoverflow.com/a/68604379/ provided by the user 'Алексей Р' ( https://stackoverflow.com/u/15035314/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to get all values from the for each loop that has regex matches and display it on email?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting All Values from a For Each Loop in VBA

When working with emails in VBA, you might encounter a scenario where you need to extract specific values from the body of an email and display them in a new email. This is especially common when dealing with formatted text such as item lists. In this guide, we will explore how to extract all matching values from an email's body using regular expressions and present them in a new email.

The Problem

Imagine you have an existing email with the following content:

01131004378-Item1

01121109880-Item2

01983345661-Item3

You want to extract the number sequences from each item (the first 11-digit value before the hyphen) using a For Each loop and display them neatly in a new email. However, you might notice that your original script only retains the last number extracted instead of all values.

Understanding the Solution

The root of the problem lies in how the values were being stored in the variable item. In the initial code, each iteration of the loop replaced the previous value instead of appending it. Here's how to resolve this issue effectively.

Step-by-Step Solution

Define the Regex Pattern:
The first step is to define the regular expression (regex) pattern that matches the desired number format. For this case, the pattern is (\d{11}(?=[-])) which captures any sequence of 11 digits that precedes a hyphen.

Store Multiple Values:
Instead of overwriting item in each loop iteration, we will concatenate the extracted values to a single string.

Construct the Email Body:
We will then format the extracted values into an HTML structure suitable for display in an email.

Example Code

Here’s how to implement the solution:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Regex Definition: The regex is set to find sequences of 11 digits that are followed by a hyphen.

Loop Through Matches: For each match, instead of replacing item, we concatenate the current match to itemsList and format it as a table cell (<td>).

Email Construction: Finally, we build the HTML body for the new email and display it.

Expected Output

When the above macro runs successfully, the newly composed email will contain:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following these steps, you can effectively extract all values from an email body and display them in a newly composed email using VBA. This technique enhances your ability to automate email processing tasks with precision and efficiency. Happy coding!

Видео Extracting All Values from a For Each Loop in VBA канала vlogize