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?
Code:
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