Excel, send emails, vba, question

[forums1167]

Hello, I need help with VBA code. I am sending ticket resolved emails thru excel starting in the third row. I would like to loop this so if we have say 6 resolved tickets, it will loop and display each email in Outlook. So far, this code is still only working for one email, it will not display 6 emails for 6 resolved tickets. I have tried a while loop and for loop, and this does not work. Any help and advice is much appreciated, thank you.

Sub TicketResolvedLoop()

Dim OutApp As Object
Dim Outmail As Object
Dim strBody As String
Dim SigString As String
Dim Signature As String
Dim i As Integer
Set OutApp = CreateObject("Outlook.Application")
OutApp.session.logon
Set Outmail = OutApp.CreateItem(0)

i = 3
While Cells(i, 1).Value <> ""

strBody = "Hello," & vbCrLf & vbCrLf & "The ticket that was opened for this for this issue has been resolved." & vbCrLf & vbCrLf _
& Cells(i, 1) & " " & Cells(i, 2) & " " & Cells(i, 3) & " " & Cells(i, 4) & " " & Cells(i, 5) & vbCrLf & vbCrLf _
& "If you do not believe that this issue is resolved, please Reply to All within 3 business days so that we may re-open the ticket. If the issue is resolved then no reply is needed." & _
" We appreciate the opportunity to serve you and have a great day!" & vbCrLf & vbCrLf _
& "Thanks,"
'hello
SigString = Environ("appdata") & _
"\Microsoft\Signatures\main.txt"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If

On Error Resume Next
With Outmail
.To = Cells(i, 3) & "; myself" 'change "[email protected]" into "[email protected]" to make the macro really work
.CC = ""
.BCC = ""
.Subject = "Ticket Resolved"
.Body = strBody & vbNewLine & vbNewLine & Signature
.Display
End With
On Error GoTo 0
Set Outmail = Nothing
Set OutApp = Nothing
i = i + 1
Wend
End Sub


Also, this uses function inside the module of:

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

[WasWodge]

Forum Rules
Please read and adhere to these simple rules!

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.
http://www.mrexcel.com/forum/excel-q...-question.html


Someone else can shed some light on the use of code tags

[cytop]

And OzGrid - http://www.ozgrid.com/forum/showthread.php?t=177746