Macro checks the database for "Yes" if blank continues and processes the statement which is saved and emailed. However upon reaching the last row continues to loop . See Next X at bottom. What am i missing to stop it from looping.? My first attempt at VBA. Assistance appreciated.
For y = 2 To 50
If Worksheets("Database").Cells(y, 9).Value = company Then
clientEmail = Worksheets("Database").Cells(y, 26)
Exit For
End If
Next y
'Set the corresponding values on the Statement
Worksheets("Statement").Cells(6, 10).Value = account
Worksheets("Statement").Cells(7, 8).Value = company
'Loop through the Statement sheet and as long as the payment status doesn't _
'change, write it to the table on the statement
StartTableRowa = 15
Do While Worksheets("Database").Cells(x, 6).Value = account And Worksheets("Database").Cells(x, 16) = "BILLED"
conf = Worksheets("Database").Cells(x, 1).Value
puDate = Worksheets("Database").Cells(x, 3).Value
invoiceNo = Worksheets("Database").Cells(x, 23).Value
PmtStatus = Worksheets("Database").Cells(x, 16).Value
passenger = Worksheets("Database").Cells(x, 8).Value
TripTotal = Worksheets("Database").Cells(x, 18).Value
Worksheets("Statement").Cells(StartTableRowa, 3) = puDate
Worksheets("Statement").Cells(StartTableRowa, 4) = invoiceNo
Worksheets("Statement").Cells(StartTableRowa, 5) = PmtStatus
Worksheets("Statement").Cells(StartTableRowa, 6) = conf
Worksheets("Statement").Cells(StartTableRowa, 7) = passenger
Worksheets("Statement").Cells(StartTableRowa, 9) = TripTotal
Worksheets("Database").Cells(x, 27).Value = "Yes"
x = x + 1
StartTableRowa = StartTableRowa + 1
Loop
'Need to subtract one from x to loop through the row again
x = x - 1
Worksheets("Statement").Activate
statementFile = ActiveWorkbook.Path & "/" & ActiveSheet.Name & "_" & _
company & ".pdf"
'Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=statementFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
'Set Outlook information
Set mailapp = CreateObject("Outlook.Application")
Set myMail = mailapp.CreateItem(0)
With myMail
.To = clientEmail
.CC = ""
.BCC = ""
.Subject = " Statement " & account
.HTMLBody = "Please see the attached statement. Thank you for your business."
.display
.attachments.Add statementFile
End With
'Clear data on invoice
Worksheets("Statement").Cells(7, 8).Value = " "
Worksheets("Statement").Cells(5, 10).Value = " "
Worksheets("Statement").Cells(6, 10).Value = " "
'Clear data in table on invoice
For t = 15 To 20
Worksheets("Statement").Cells(t, 3).Value = ""
Worksheets("Statement").Cells(t, 4).Value = ""
Worksheets("Statement").Cells(t, 5).Value = ""
Worksheets("Statement").Cells(t, 6).Value = ""
Worksheets("Statement").Cells(t, 7).Value = ""
Worksheets("Statement").Cells(t, 8).Value = ""
Worksheets("Statement").Cells(t, 9).Value = ""
Next t
End If
Next x
MsgBox ("All emails have been generated.")
End Sub
Good morning everyone,
could you attach a demo of the file, without confidential data, it would be much easier for someone to help, recreating everything takes a long time
Miguel,
As Miguel has said we need a workbook with the code so that we dont waste time recreating data.
that said, having a quick look though your code it doesnt even look complete - i cant see where you have started the X loop - i can see a start and end Y, and a start and end t, but not X
Thanks guys, I never thought of posting the whole code as it is rather long. I shall bear that in mind for the future.
Painstakingly I retyped the whole code over again and it is working. Whether there was a space or not in the first time I did it is unknown.
You may close this item.
Peter
