VBA Code to send e-mails Loop without Do error?! (1 Viewer)

[dpelizzari]

I took code I had written previously and modified it to send e-mails in HTML format... but for some reason I can't figure out, after adding the recordset information, I am getting a "Loop without Do" compile error, I have compared this repeatedly to my old code, which works fine... I can't figure out why it is erroring... Here is the code:

Public Function SendEmail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim myOlApp As Outlook.Application
Dim MyItem As MailItem
Dim StrCustomer As String
Dim StrSupervisor As String
Dim strCurDate As Date
Dim StrUsageType As String
Set myOlApp = Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("Query1")
Do Until MailList.EOF
Set MyItem = myOlApp.CreateItemFromTemplate("c:\Cost_Savings_Recommendations_Template.oft")
StrCustomer = MailList("Customer_email")
If MailList("Customer_EP") = -1 Then
StrCustomer = ""
End If
StrSupervisor = MailList("Supervisor_email")
If MailList("Supervisor_EP") = -1 Then
StrSupervisor = ""
strCurDate = MailList("Response Date")
If MailList("plan_change_type") = "Text" Then
StrUsageType = "Messages"
End If
If MailList("plan_change_type") = "Voice" Then
StrUsageType = "Minutes"
End If
If MailList("plan_change_type") = "Data" Then
StrUsageType = "Data"
End If
MyItem.BodyFormat = olFormatHTML
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%username%", MailList("Customer"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%plan_type%", MailList("plan_change_type"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%reply_date%", strCurDate)
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%phone_number%", MailList("Phone_Number"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%start_date%", MailList("start_date"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%end_date%", MailList("end_date"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%Vendor%", MailList("Vendor"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%current_plan%", MailList("Current_Plan"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%usage_type%", StrUsageType)
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%avg_monthly_usage%", MailList("avg_monthly_usage"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%avg_monthly_spend%", MailList("avg_monthly_spend"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%description%", MailList("New Plan"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%local_curency%", MailList("local_currency"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%annualized_savings%", MailList("annualizedsavings"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "%Vendor%", MailList("Vendor"))
MyItem.To = StrCustomer
MyItem.CC = StrSupervisor
MyItem.Subject = "Action Required:  Mobile Cost Savings Recommendations for " & MailList("phone_number") & " Change to " & MailList("plan_change_type") & " Recommended " & MailList("Date Recommended")
'MyItem.Display
MyItem.Send
Set MyItem = Nothing
MailList.MoveNext
Loop
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function

 

[pbaldy]

Offhand it doesn't appear this If has an End If:

If MailList("Supervisor_EP") = -1 Then

 

[dpelizzari]

Wow, would you believe I was missing an End If which was causing the compile to throw the error?

 

[pbaldy]

Yes, I would.