End if without block if Error (1 Viewer)

D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
I have this table and would like to send one email to Names of individuals who share same country.
NameCountryCitySchoolEmail
AdamTRNCLefkosaCIUAdam@mail.com
MinaTRNCLefkosaNEUMina@mail.com
GraemeUSAMissouriMUNIGraeme@mail.com
BriannUSANebraskaNBUNIBriann@mail.com
BettyUKHullHUNIBetty@mail.com

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

NameCountryCitySchoolEmail
AdamTRNCLefkosaCIUAdam@mail.com
MinaTRNCLefkosaNEUMina@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
 
Last edited:
Sort by date Sort by votes
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
theDBguy said:
Hi. Which line gets highlighted when you go to debug mode?
Click to expand...
Hi, kindly see the post where I have highlighted the line in Green. The first "End if" line gets highlighted in Debug mode. Thank you
 
Upvote 0 Downvote
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:57
Joined
Oct 29, 2018
Messages
21,474
Donnie1ben said:
Hi, kindly see the post where I have highlighted the line in Green. The first "End if" line gets highlighted in Debug mode. Thank you
Click to expand...
I see. Did you say you copied and pasted the above from the Internet into your database? If so, try retyping the whole thing from scratch. Sometimes, you end up copying something you can't see that causes unexpected issues.
 
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
theDBguy said:
I see. Did you say you copied and pasted the above from the Internet into your database? If so, try retyping the whole thing from scratch. Sometimes, you end up copying something you can't see that causes unexpected issues.
Click to expand...
Actually I did retype the entire code from scratch. It's quite annoying when the debug solutions I see online don't work.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:57
Joined
May 7, 2009
Messages
19,245
you can check-out and test this demo?
 

Attachments

  • test_HTM.accdb
    640 KB · Views: 106
Upvote 0 Downvote
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:57
Joined
Feb 28, 2001
Messages
27,191
OK, technically, an in-line IF is legal without a closing END IF, but I have sometimes had issues sometimes. Try changing the block to look like this (with some lines omitted for brevity). THIS IS A GUESS - but is like something I've seen before.

Code: 
    For x = 1 To rec.recordCount
        lCnt = lCnt + 1
        ReDim Preserve aBody(1 To lCnt)     
...   {omitted}
        strEmail = strEmail & rec!Email & ";"
        If x < rec.recordCount Then 
             rec.MoveNext
        ElseIf (x = rec.recordCount) Or (strCountry <> rec!Country) Then
            'create the email
            Set olItem = olApp.CreateItem(0)
...    {omitted}
            olItem.Display
            lCnt = 0
            strEmail = ""
            strCountry = rec!Country
        End If
    Next
 
Upvote 0 Downvote
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 06:57
Joined
Sep 21, 2011
Messages
14,310
This compiles fine for me as well?
In fact the link of that code had the same error?, yet June7 was able to compile it OK?
 
Last edited:
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
arnelgp said:
you can check-out and test this demo?
Click to expand...
Hi, this works perfect as I required. I cannot thank you enough.:) 🙏🙏 I have some more questions, I would like to input text in the body of the email before the start of the table, in form of a salutation message : "Dear Sir/Madam, Please find attached your corresponding information". Likewise at the end of the table, a closing message like, "Thank you for your patronage".

Do I need to declare a string variable like:

Dim aBody As String

and call aBody = "Dear Sir/Madam, Please find attached your corresponding information".
 
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
arnelgp said:
here check the code on the .htmlbody.
Click to expand...
Hi, I also would like to return select columns to user, for instance "Country" AS [Your Country] and City AS [Your City].

I try to do this in the "zzforEmail" query so the table returns Your Country and Your City as the only columns in the outlook email.

For some reason, when I click the form, it does a quick popup in the background and returns results for (Select * from Dummy).

Also, for calling inserting a signature to the footer of the email, I initialise Dim Sig As String and concatenate the strings within the body text but it outputs nothing.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:57
Joined
May 7, 2009
Messages
19,245
Donnie1ben said:
Also, for calling inserting a signature to the footer of the email, I initialise Dim Sig
Click to expand...
create first your signature in Outlook.
Open Outlook, then File->Option->Mail->Signature.

If you already have your signature created in Outlook.
Tell me your "signature" name.
in the meantime, check the new code of the Button.

also, the query is not needed, ever. i made it for a test
and realize it's not of any use.
 

Attachments

  • test_HTM.accdb
    976 KB · Views: 80
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
arnelgp said:
create first your signature in Outlook.
Open Outlook, then File->Option->Mail->Signature.

If you already have your signature created in Outlook.
Tell me your "signature" name.
in the meantime, check the new code of the Button.

also, the query is not needed, ever. i made it for a test
and realize it's not of any use.
Click to expand...
I think I was not clear in my question. the current db returns all records in the table for unique users. However, this time I want to return only select columns and corresponding rows in the form of "Country","Name" and "City" with alias Your country, Your name, Your city. Hence I was asking if a groupby by these columns would suffice.
 
Upvote 0 Downvote
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:57
Joined
May 7, 2009
Messages
19,245
Donnie1ben said:
ence I was asking if a groupby by these columns would suffice.
Click to expand...
if you run the new uploaded db it will have a column of "My Country", "My City" and you
can see it in the code. whatever is missing or you want to add to the query you can modify
the SQL string in the code of the button.
 
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
arnelgp said:
if you run the new uploaded db it will have a column of "My Country", "My City" and you
can see it in the code. whatever is missing or you want to add to the query you can modify
the SQL string in the code of the button.
Click to expand...
Hi, thank you @arnelgp for the time you put into helping me get a solution.
 
Upvote 0 Downvote
D

Donnie1ben

New member
Local time
Today, 07:57
Joined
Aug 15, 2022
Messages
17
I want to modify the current solution given that currently the attached file generates an email according to students who belong to the same country. I have included a second table (Principal) to the attachment, and would like the "recipient" to come from "Principal" table.

Scenario: Nat is a course advisor to Adam and Mina.
Recipient should be Nat given that these are the students Nat is responsible for.

My solution would be to create a second recordset to retrieve distinct emails from Principal table using common "Name" column found in both tables and call recipient to be "Mail" from Principal table.

I try this and it works but takes only the Name and Mail column for generating the email.

Principal Table
Course_advisorNameMailCountries
NatAdamNat1@mail.comTRNC
NatMinaNat1@mail.comTRNC


Dummy Table
NameCountryCitySchoolEmail
AdamTRNCLefkosaCIUAdam@mail.com
MinaTRNCLefkosaNEUMina@mail.com
 

Attachments

  • test_HTM.accdb
    976 KB · Views: 81
  • sample email.PNG
    sample email.PNG
    14.5 KB · Views: 67
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

D
  • Question
Send an outlook email to each record in the table in a table format
Replies
4
Views
859
Donnie1ben
D
M
Solved VBA access to outlook email with table: aligning table cells
Replies
5
Views
1,904
miacino
M
U
Send emails to Unique users in Range
Replies
5
Views
1,167
Donnie1ben
D
A
Trying to add a second HTML table to email.. struggling.
Replies
4
Views
823
theDBguy
theDBguy
T
Use a Form Text Box for Email Address in VBA Function
Replies
7
Views
1,314
usama_hawas
U

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom