Pulling table data into Outllook template

[allen675]

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:

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

 

[CJ_London]

you mean this on the first line of 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

 

[allen675]

Thank you.

Now stuck at this

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

Its stating Object Required?

 

[CJ_London]

go back to where you borrowed it from and check you have copied everything correctly - I suspect you are missing a reference

 

[allen675]

Okay, thank you, done that. Slowly moving down the code. So now getting to this:

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.

 

[CJ_London]

you need to provide your revised code - according to what you provided originally the sql has ' ...note details...'

 

[allen675]

This is where I am at so far:

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

 

[CJ_London]

So are your fields called 'col1' and 'col2'?

 

[allen675]

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:

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

 

[CJ_London]

is it always this frustrating?

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

 

[allen675]

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

 

[CJ_London]

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

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

 

[allen675]

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:

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

 

[allen675]

Okay so I changed the template slightly to all simple lowercase words for the place holders as follows:

.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?

 

[CJ_London]

sorry, don't really understand the question - would have thought just remove the code? and perhaps modify the template?

 

[allen675]

Removed this and seems to have done the trick

& salesRST.Fields(i).Name

Thanks for all your help @CJ_London