Writing txt. logs during a loop

[dkkendal]

Need the following code to produce a txt. log on an error, but return the loop interger it was using when the error occured, then retunr to the loop.


Sub email()

Dim OutApp As Object, OutMail As Object
Dim strbody As String
Dim cell As Range
Set cell = Range("J1")

For i = 1 To cell.Value
On Error GoTo errHandler:
strbody =

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

y = "C:\Users\dave.kendall\OneDrive - Christians Against Poverty\Client statements\Output\With Watermark" & Cells(i, 6)

With OutMail
.Display
.Subject = "Christians Against Poverty Client Statement - " & Cells(i, 3)
.Attachments.Add y
.To = Cells(i, 7)
.SentOnBehalfOfName = "dave.kendall@capaust.org"
.HTMLBody = strbody & "
" & .HTMLBody
.Send

End With

'Delete any temp files created
Set OutMail = Nothing
Set OutApp = Nothing

Nexti:
Next i

Exit Sub

errHandler:
Logger "Error", Err.Source, Err.Description
Resume Nexti

End Sub


Sub Logger(sType As String, sSource As String, sDetails As String)

Dim sFilename As String
sFilename = "C:\Users\dave.kendall\OneDrive - Christians Against Poverty\Client statements\Log\logging.txt"


' Open the file to write
Dim filenumber As Variant
filenumber = FreeFile
Open sFilename For Append As #filenumber

Print #filenumber , CStr(Now) & "," & sType & "," & sSource _
& "," & sDetails & "," & Application.UserName

Close #filenumber

End Sub

 

[nemmi69]

Try this

Option Explicit


Sub email()
Dim i As Integer, iErr As Integer
Dim OutApp As Object, OutMail As Object
Dim strbody As String
Dim cell As Range
Set cell = Range("J1")


For i = 1 To cell.Value
iErr = i
On Error GoTo errHandler:
strbody =


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


y = "C:\Users\dave.kendall\OneDrive - Christians Against Poverty\Client statements\Output\With Watermark" & Cells(i, 6)


With OutMail
.Display
.Subject = "Christians Against Poverty Client Statement - " & Cells(i, 3)
.Attachments.Add y
.To = Cells(i, 7)
.SentOnBehalfOfName = "dave.kendall@capaust.org"
.HTMLBody = strbody & ""
" & .HTMLBody
.Send


End With


'Delete any temp files created
Set OutMail = Nothing
Set OutApp = Nothing


Nexti:
Next i


Exit Sub


errHandler:
Logger "Error", Err.Source, Err.Description, iErr
Resume Nexti


End Sub




Sub Logger(sType As String, sSource As String, sDetails As String, sLoop As Integer)


Dim sFilename As String
sFilename = "C:\Users\dave.kendall\OneDrive - Christians Against Poverty\Client statements\Log\logging.txt"




' Open the file to write
Dim filenumber As Variant
filenumber = FreeFile
Open sFilename For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenumber]#filenumber[/URL] 


Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenumber]#filenumber[/URL] , CStr(Now) & "," & sType & "," & sSource _
& "," & sDetails & "," & Application.UserName


Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenumber]#filenumber[/URL] 


End Sub

PS found other errors but did not know how to fix.

 

[dkkendal]

Thank you, I've been able to use your reply to find my solution. Needed to add the sloop into the print, within the looger sub. However you gave me enough to point me in the right direction.

Be interested to know what errors you found as it is running as expected for me, which lines gave you issues. I did remove some of the code when I posted the thread. So it may simply be that.

Thank you again for prompt response.