Output array's values to mail's body

Question

I am struggling with VBA logic to build an mail body with undefined numbers of names. The names, which are located in sheet "Names" column "D", should be listed in the email body.

This should take the names and put them in an array, starting from row 2 in column "D".

Dim i As Integer
Dim j As Integer
Dim cellValue() As String

i = 2
j = 2

Do Until IsEmpty(Cells(i,4))
    cellValue(i) = Cells(i, 4).Value
    i = i + 1
Loop

Located in the mail's body:

"Names : "
Do While j <= i
    "Name " & cellValue(j)
    j = j + 1
Loop

I am not able to get any names in the email, still I have the other part of the email right.

You've got a few issues. I'd recommend searching other questions about the use of `ReDim` to build your array. Also, when trying to insert your names into your email body, you're not doing anything with this string. Every time I've done this, I've needed to concatenate one long string for the email body. Also, without any return character in that string, you're going to get your names all on the same line, rather than in a list. Showing more of your code would help as well... :-) — nwhaught, Oct 20 '15 at 18:00

score 0 · Answer 1 · edited May 23 '17 at 12:23

nwhaught is right, you've to use ReDim statement to reallocates storage space for an array variable.

Try this:

Dim i As Integer
Dim j As Integer
Dim cellValue()
Dim x : x = 0

i = 2
j = 2

Do Until IsEmpty(Cells(i,4))
    ReDim Preserve cellValue(x)
    cellValue(x) = Cells(i, 4).Value
    x = x + 1
    i = i + 1
Loop

And in your email body:

For j = LBound(cellValue) To UBound(cellValue)
    "Name " & cellValue(j)
Next

For more information on ReDim, please check this and this links.

score 0 · Accepted Answer · answered Oct 20 '15 at 18:13

I will save you the trouble of searching....

Dim i As Integer
Dim j As Integer
Dim cellValue() As String

i = 2
j = 2

Do Until IsEmpty(Cells(i,4))
    ReDim Preserve cellValue(i)
    cellValue(i) = Cells(i, 4).Value
    i = i + 1
Loop

Then in the email body:

sBody = "Names : "
Do While j <= i
    sBody = sBody & vbNewLine & "Name " & cellValue(j)
    j = j + 1
Loop

'assume you already have this, but for illustration purposes
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
   .To = 'whomever
   .Body = sBody
End With

Using the .Body property does not allow for very nice formatting. If you need that, look at .HTMLBody and how to embed HTML into the string to format it as you wish.

Output array's values to mail's body

2 Answers2