Pulling table data into Outllook template (1 Viewer)

A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Hi,

I have an outlook template (attached screenshot) and some code that looks through two tables to grab required info for email template. I have to be honest I have borrowed this code from somewhere else and amended it. I am however getting syntax error: Syntax error (missing operator) in query expression '...email details...'

Not really knowing what I am doing, I'm not sure where I'm going wrong.

Here is the code:

Code: 
Private Sub Command10_Click()
strSQL = "SELECT CustomerID, Lead_Date, Client_FN, Client_SN, Mobile_No, Email_Address, [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3], Email_Sent, [SMS/WhatsApp_Sent],  ...email details... FROM Client"
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")

    strSQL = "SELECT Col2, Col3 ...note details..." _
              & " FROM NoteHistroy" _
              & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)

    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 1 To salesRST.Fields.Count
        strTable = strTable & "<td>" & salesRST.Fields(i - 0).Name & "</td>"
    Next i
    strTable = strTable & "</th>"

    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 0).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close

    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"

        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%LeadDate%", clientRST!Lead_Date)
        .HTMLBody = Replace(.HTMLBody, "%LeadFN%", clientRST!Client_FN)
        .HTMLBody = Replace(.HTMLBody, "%LeadSN%", clientRST!Client_SN)
        .HTMLBody = Replace(.HTMLBody, "%MobileNo%", clientRST!Mobile_No)
        .HTMLBody = Replace(.HTMLBody, "%EmailAddress%", clientRST!Email_Address)
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%EmailSent%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%WhatsAppSMSSent%", clientRST![SMS/WhatsApp_Sent])
              

        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)

        .Display
    End With

    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop

End Sub

Please help, thanks in advance
 

Attachments

  • Email template.PNG
    Email template.PNG
    18.8 KB · Views: 75
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
you mean this on the first line of code?

Code: 
[SMS/WhatsApp_Sent],  ...email details... FROM Client

for the sql to be valid, it needs to be enclosed in square brackets. However suspect it is a comment that needs to be removed or replaced. If removed, also remove the preceding comma
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Thank you.

Now stuck at this

Code: 
Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")

Its stating Object Required?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
go back to where you borrowed it from and check you have copied everything correctly - I suspect you are missing a reference
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Okay, thank you, done that. Slowly moving down the code. So now getting to this:

Code: 
Set salesRST = CurrentDb.OpenRecordset(strSQL)

Getting run-time error: Too few parameters. Expected 2

Sorry if I'm being a pain but as I say I'm a noob really. I have this idea in my head for a DB and gradually piecing it together from bits of code all over the internet.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
you need to provide your revised code - according to what you provided originally the sql has ' ...note details...'
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
This is where I am at so far:

Code: 
Private Sub Command10_Click()

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String

    
strSQL = "SELECT [CustomerID], [Lead_Date], [Client_FN], [Client_SN], [Mobile_No], [Email_Address], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3], Email_Sent, [SMS/WhatsApp_Sent] FROM Client"
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")

    strSQL = "SELECT Col2, Col3" _
              & " FROM NoteHistory" _
              & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)

    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 1 To salesRST.Fields.Count
        strTable = strTable & "<td>" & salesRST.Fields(i - 0).Name & "</td>"
    Next i
    strTable = strTable & "</th>"

    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 0).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close

    With MailOutlook
        .To = "toby.connew@rftfinancialservices.co.uk; richard.dew@rftfinancialservices.co.uk"
        .subject = "Unbiased Refund Request"

        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%LeadDate%", clientRST!Lead_Date)
        .HTMLBody = Replace(.HTMLBody, "%LeadFN%", clientRST!Client_FN)
        .HTMLBody = Replace(.HTMLBody, "%LeadSN%", clientRST!Client_SN)
        .HTMLBody = Replace(.HTMLBody, "%MobileNo%", clientRST!Mobile_No)
        .HTMLBody = Replace(.HTMLBody, "%EmailAddress%", clientRST!Email_Address)
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%EmailSent%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%WhatsAppSMSSent%", clientRST![SMS/WhatsApp_Sent])
              

        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)

        .Display
    End With

    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop

End Sub
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
So are your fields called 'col1' and 'col2'?
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
No they are not but I did clock that last night and changed them to NoteDate & Note. Now getting a different error! Is it always this frustrating? 🤣. Error now is 'item not found in this collection' highlighting this line:

Code: 
StrTable = StrTable & "<TD> & salesRST.Fields(I - 0).Name & "</td≥"
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
is it always this frustrating?
Click to expand...
only when you copy code and don't understand what it is doing:).

Suspect your 'i-0' is not correct it doesn't do anything. suspect you have changed it from i-1
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Think I'll have to take that one on the chin seeing as your absolutely correct 😁

I haven't changed that bit but will do when back in front of pc and let you know how I get on.

Thanks for your help on this really do appreciate it 👍
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
Do you have Option Explicit at the top of every module? Do you compile your code before trying to run it? If not I strongly advise you do. Will highlight all compilation errors so you only have to worry about runtime ones (typically around data problems) when you execute it

You can arrange for Option Explicit to appear automatically for new modules by going to Tools>Options on the VBE menu and ticking Require Variable Declaration

the way many developers would write that bit of code is

Code: 
For i = 0 To salesRST.Fields.Count-1
        strTable = strTable & "<td>" & salesRST.Fields(i ).Name & "</td>"
    Next i
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Okay so now code running without any errors. I've added option explicit and had to define a few more objects. Some of the info is pulling into the template and some is not. Here is the code so far:

Code: 
Private Sub Command10_Click()

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant

   
strSQL = "SELECT [CustomerID], [Lead_Date], [Client_FN], [Client_SN], [Mobile_No], [Email_Address], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3], [Email_Sent], [SMS/WhatsApp_Sent] FROM Client" _
& " WHERE CustomerID = " & Forms!SubmitRefundF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")

    strSQL = "SELECT NoteDate, Note" _
              & " FROM NoteHistory" _
              & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)

    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 1 To salesRST.Fields.Count
        strTable = strTable & "<td>" & salesRST.Fields(i - 1).Name & "</td>"
    Next i
    strTable = strTable & "</th>"

    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close

    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"

        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%LeadDate%", clientRST!Lead_Date)
        .HTMLBody = Replace(.HTMLBody, "%LeadFN%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%LeadSN%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%MobileNo%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%EmailAddress%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%EmailSent%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%WhatsAppSMSSent%", clientRST![SMS/WhatsApp_Sent])
             

        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)

        .Display
    End With

    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop

End Sub
 

Attachments

  • Email template some detail.PNG
    Email template some detail.PNG
    22.6 KB · Views: 68
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Okay so I changed the template slightly to all simple lowercase words for the place holders as follows:

Code: 
.HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%email_sent%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%message%", clientRST![SMS/WhatsApp_Sent])

And now all the info pulling through to the template!!!!!!

One last thing though............ How do I remove the two column headings (see attached highlighted in red) please?
 

Attachments

  • Email template some detail working.PNG
    Email template some detail working.PNG
    19.5 KB · Views: 73
C

CJ_London

Super Moderator
Staff member
Local time
Today, 02:21
Joined
Feb 19, 2013
Messages
16,607
sorry, don't really understand the question - would have thought just remove the code? and perhaps modify the template?
 
A

allen675

Member
Local time
Today, 02:21
Joined
Jul 13, 2022
Messages
124
Removed this and seems to have done the trick 👍

Code: 
& salesRST.Fields(i).Name

Thanks for all your help @CJ_London
 
You must log in or register to reply here.

Similar threads

A
Solved Help with handling of Null
2 3
Replies
53
Views
2,264
allen675
A
L
Solved Help on blank Email body
2
Replies
27
Views
1,810
bastanu
bastanu
MajP
Hierarchical Data, Self Referencing Tables, and Recursion Part 2
Replies
6
Views
1,226
json5545
J
M
Solved VBA access to outlook email with table: aligning table cells
Replies
5
Views
1,878
miacino
M
T
Hiding HTMLBody table rows if values are 0
Replies
3
Views
706
plog
P

Users who are viewing this thread

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