[RESOLVED] Using .TextBody when sending emails via Visual Basic

[flacosueco]

Hi,

I need some help with the following code because I need to write an email whose body stems from a number of col and rows in the excel sheet. In order to give the email the output format I wish, I need to use two loops. The problem is that .Textbody overwrites the previous iteration so I end up with only the text from the last iteration in my email. Anyone can see a way out?

students = 2
lastquest = 4
With cdomsg
For rw = 2 To students + 1
.To = Cells(rw, 2)
.From = "[email protected]"
.Subject = "Resultados de Tecnología de " + Cells(rw, 1)
For col = lastquest + 2 To 3 Step -1
.TextBody = Cells(1, col) & ": " & Cells(rw, col) & vbNewLine
Next
.send
Next
End With


This is how the data in the Excel sheet look like:

Alumno Email Cuestionario 1 Cuestionario 2 Cuestionario 3 Cuestionario 4
Esther Hernández [email protected] 7 3 5 9
Carlos Darias [email protected] 2 2 2 2

The number of columns will be increasing with time so I set a variable (lastquest) in the beginning so that it can be modified easily. The variable "students" set the number of student (in this trial just 2). I'd like the output in the email body to be like this for each person:

Cuestionario 4: 9
Cuestionario 3: 5
Cuestionario 2: 3
Cuestionario 1: 7

With the above code, I only get the last row in the email. I'm using Excel 2003 and Visual Basic 6.3. Thanks a lot for your time.

[vbfbryce]

In your For loop, update a string variable instead, then make the body equal to the string after leaving the loop,