End if without block if Error

Donnie1ben · Aug 22, 2022

I have this table and would like to send one email to Names of individuals who share same country.

Name	Country	City	School	Email
Adam	TRNC	Lefkosa	CIU	Adam@mail.com
Mina	TRNC	Lefkosa	NEU	Mina@mail.com
Graeme	USA	Missouri	MUNI	Graeme@mail.com
Briann	USA	Nebraska	NBUNI	Briann@mail.com
Betty	UK	Hull	HUNI	Betty@mail.com

Thus Adam and Mina will receive one email in Table format as well as same for Graeme and Briann

Name	Country	City	School	Email
Adam	TRNC	Lefkosa	CIU	Adam@mail.com
Mina	TRNC	Lefkosa	NEU	Mina@mail.com

I found a sample solution online but encounter the "End if without block if" when I implement same code. I tried countless techniques I found online but none seems to work.

The highlighted "End if" gets highlighted in debug mode

See sample code:

Code:

Public Sub NewEmail()

Dim olApp As Object
Dim olItem As Variant
Dim rec As DAO.Recordset
Dim aRow(1 To 5) As String
Dim aBody() As String
Dim lCnt As Long
Dim strCountry As String
Dim strHTML As String
Dim strEmail As String
Dim x As Integer

strHTML = "<HTML><body><table border='2'><tr><th>"
strHTML = strHTML & "Name</th><th>"
strHTML = strHTML & "Country</th><th>"
strHTML = strHTML & "City</th><th>"
strHTML = strHTML & "School</th><th>"
strHTML = strHTML & "Email</th>"
strHTML = strHTML & "XXX</table></body></html>"

lCnt = 1

Set olApp = CreateObject("Outlook.application")

Set rec = CurrentDb.OpenRecordset("SELECT * FROM DummyTable ORDER BY Country, City, School, Name")

If Not (rec.BOF And rec.EOF) Then
    strCountry = rec!Country
    For x = 1 To rec.recordCount
        lCnt = lCnt + 1
        ReDim Preserve aBody(1 To lCnt)
        aRow(1) = rec("Name") & "</td><td>"
        aRow(2) = rec("Country") & "</td><td>"
        aRow(3) = rec("City") & "</td><td>"
        aRow(4) = rec("School") & "</td><td>"
        aRow(5) = rec("Email") & "</td>"
        aBody(lCnt) = "<tr><td>" & Join(aRow, "") & "</tr>"
        strEmail = strEmail & rec!Email & ";"
        If x < rec.recordCount Then rec.MoveNext
        If x = rec.recordCount Or strCountry <> rec!Country Then
            'create the email
            Set olItem = olApp.CreateItem(0)
            olItem.Display
            olItem.To = "my email"
            olItem.BCC = strEmail
            olItem.Subject = "Test E-mail"
            olItem.HTMLBody = Replace(strHTML, "xxx", Join(aBody, vbNewLine))
            olItem.Display
            lCnt = 0
            strEmail = ""
            strCountry = rec!Country
        End If
    Next
End If

strEmail = ""
strCountry = rec!Country
End If
Next
End If

Donnie1ben · Sep 8, 2022

arnelgp said:
here check and test.

hi, I think I worded my question wrongly. I am trying to say that the email should be generated from "dummy" table and the "recipient" of the email should be "course advisor". For example, Adam and Mina share same course advisor whose name is Nat. Therefore Nat who is a course advisor to both, should receive information about Adam and Mina from student table.

Code:

sql = "select * from Principal WHERE course_advisor = '" & !course_advisor & "';"


sRecipients = sRecipients & rs2!mail.Value & ";"

Code:

I tried to change these two lines. It produces the recipient email as I want, but does not include contents of the "dummy" table, more or less the "student" table.

arnelgp · Sep 8, 2022

here again. check the code.

Donnie1ben · Sep 9, 2022

arnelgp said:
here again. check the code.

Hi @arnelgp, I have checked the code. I encounter an error when I modify the code to my existing table. kindly note this error is not in the attachment above. Specifically, these two lines

Code:

sql = "select * from forEmail WHERE VariableName = '" & !VariableName & "';"

Code:

sRecipients = rs1!mail

"VariableName is name of column in this case."

I was wondering if there is an alternative way to go about this.

I cant post data table due to data compliance

End if without block if Error

Donnie1ben

New member

Donnie1ben

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

Donnie1ben

New member

Similar threads

Users who are viewing this thread