Replace line break in email using Replace()

Question

In the process of trying to automate an Outlook Email with VBA through Excel.

Towards the end of my code I have a string of values in an Excel cell separated by ;# (Example: ;#abacus;#bicycle;#cheese;#).

Once I get to the .HTMLBody section of the email creation portion, I'm using Replace() to remove the separator.

.HTMLBody = Replace(.HTMLBody, ";#", ", ")

This is great as it separates the list with a comma. I have another piece that handles the beginning as well (the ;# before the beginning of my list). This looks for the specific instance of "List: , "

.HTMLBody = Replace(.HTMLBody, "List: , ", "List:")

Which should now read: "List: abacus, bicycle, cheese, "

I'm attempting to now remove the extra comma and space after the end of my list ("cheese, " should end as "cheese"). List will always have different values with different lengths in it, not necessarily ending in cheese, but always ending in ;# based on the value separator.

I know the last value always follows with a return < br > in the email, but when I try this code, it doesn't work (line breaks have included spaces around brackets to not break code block, they don't in my real VBA code):

.HTMLBody = Replace(.HTMLBody, ", < br >< br >", "< br >< br >")

Based on my crazy system of replacing values (in this case, aiming to replace three times), I feel like it should work, but it does not.

Is VBA not capable of searching for a string with line breaks in it and replacing it with just line breaks?

I also feel like there is a better way to do this - my VBA is shoddy at best.

Answer 1

GD Chris,

It appears you approach the problem in reverse ? Further, you indicate your start string to be:

";#abacus;#bicycle;#cheese;#"

Yet you indicate in your second 'Replace' step that there is also a value present "List:" ? This should either be part of the initial string otherwise it cannot be found in the second Replace statement ?

Anyway assuming your initial String is:

"List:;#abacus;#bicycle;#cheese;#"

Your objective is to manipulate this string to build the .HTMLbody component.

This can be easily done by splitting the string based on the delimiter ";#"

And rebuilding for non-empty strings as per below, where s will be your start string:

Function HTMLBody(s As String) As String

    Dim b As String

    s = Replace(s, "List:", vbNullString)   'Removes the "List:" section
    aStr = Split(s, ";#")    'Split the string in an array through the ";#" delimiter

    For Each a In aStr    'Loop through all array values
        If Not a = vbNullString Then   'Test if array value is not empty
            If b = vbNullString Then   'Test if b is 'uninitialised' to allow addition of first item without the ","
                b = a
            Else
                b = b & "," & a
            End If
        End If
    Next

    b = "List:" & b     'Add "List:" back onto the build string

    HTMLBody = b    'Set the HTMLBody() return value

End Function

You can then call the "HTMLBody" function by:

Sub callHTMLBody()

    .HTMLbody = HTMLBody("List:;#abacus;#bicycle;#cheese;#")

End Sub

That should get you the correct String manipulation ?