Access VBA Email Syntax Error

[Steven19]

Im using Access with the outlook reference library enabled.

I'm trying to play with automating an email using VBA, Ive done this a few times, but not with a Dlookup in the middle of a sentence. I am trying to get the schedule type from my lookup from a query and the msg part returns a syntax error. What punctuation is wrong for this to not work?

Private Sub btnReminder_Click()Dim Msg As String


Msg = "Hi,<P>" & _
"As part of our BRC the following " DLookup("[ScheduleTypes]", "[QryScheduleOverdue]", "ScheduleID=" & Me.ScheduleID )" is overdue. <P>" & _
"<P>" & _
"Please update me with the date that this task will be completed and submitted to me by the close of play tomorrow. <P>" & _
"<P>" & _
"If I do not hear back from you by the close of play tomorrow then I will have to raise this non-conformity with the MD. <P>" & _
"<P>" & _
"<P>" & _
"<P>" & _
"Kind Regards"


Dim O As Outlook.Application
Dim M As Outlook.MailItem


Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)


With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
        .To = DLookup("[WarehouseManager]", "[tblContactemails]")
        .CC = DLookup("[DeputyWarehouseManager]", "[tblContactemails]")
        .Subject = [ScheduleTypes] & " Overdue "
        .Display
End With


Set M = Nothing
Set O = Nothing
End Sub

[Welshgasman]

Do not try to be clever.
Get each part of your data, then you can check it with a debug.print or just hover over it.
You obtained the scheduletype once but did not store it? , so you would need to retrieve it again.

[June7]

You have to concatenate the variable input - whether or not you set a declared variable to the DLookup() retrieved value.

"As part of our BRC the following " & DLookup("[ScheduleTypes]", "[QryScheduleOverdue]", "ScheduleID=" & Me.ScheduleID) & " is overdue. <P>" & _

How is ScheduleID selected - a combobox? A properly constructed combobox could eliminate need for DLookup().

What data are you working with - what is "close of play"?

[Welshgasman]

On my phone admittedly, but I thought the the issue was .Subject ? with nothing for ScheduleTypes?

[June7]

Multiple issues with code. Lack of concatenation is just one.

If [ScheduleTypes] is already a field/control on form, why do you need DLookup() to find it?

[Welshgasman]

@June7

'Close of play' is a term for the end of the working day.

[June7]

Okay, on this side of the pond we just say "end of day".

Does calling the workday "play" make it more appealing to be there?

[Welshgasman]

Same rubbish as 'touch base' etc.