Hi there, being a newbie to Access VB I have stolen code from various sources......I think you'll be able to understand what I am trying to do but at the very end on the execution of the code I recieve a "Object does not support this Propertyd .....can anyone throw any light on why. I thought I had taken care of everything??? Perhaps not??
Private Sub EmailTo_Click()
On Error GoTo Err_EmailTo_Click
Dim dbs As Database
Dim rst As Recordset
Dim stDocName As String
Dim strSql As String
Dim rs As Recordset
Set dbs = CurrentDb()
stDocName = Me!CboForms
stSub = DLookup("[Subscription]", "[tblConfiguration]")
strSql = "SELECT FarNorth.JPID, FarNorth.Title, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, SubID, Sub2.SubYear, Sub2.AmtInvoiced, Sub2.AmountPaid FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID WHERE Sub2.AmtInvoiced and Sub2.SubYear = 2024 and Sub2.SubID = " & Me![JPID]
'create recordset of JP's
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
If Me!CboForms = "Subscription Invoice" And rst![AmountPaid] = 40 Then 'Or Me!CboForms = "Subscription Reminder" Then ' Or Me!CboForms = "Subscription Reminder" And rst![AmountPaid] = 40 Then
MsgBox rst![FirstName] & " " & rst![Surname] & " has paid the full subscription for the " & rst![SubYear] & " year! You will need to forward a Subscription Reminder notice!!"
End If
'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++
' ask the user if they would like to preview the invoice before sending
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to preview the invoice before emailing for " & [Title] & " " & [Initials] & " " & [Surname] & "?"
Style = vbYesNo + vbCritical + vbDefaultButton3
Title = "Watchout!!!"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
DoCmd.OpenReport stDocName, acViewPreview, , "FarNorth.JPID=" & Me!JPID, acWindowsNormal
ElseIf Response = vbNo Then
DoCmd.OpenReport stDocName, acViewPreview, , "FarNorth.JPID=" & Me!JPID, acHidden
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, CurrentProject.Path & "" & stDocName & ".pdf", True
DoCmd.Close acReport, stDocName
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String
'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.Path & "" & stDocName_ & todayDate & ".pdf"
DoCmd.OutputTo acReport, stDocName, acFormatPDF, fileName, False
'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
.Recipient = Me!Email
.Subject = stDocName
.Body = "Please find attached a " & stDocName
.Attachments.Add fileName
.Send
End With
DoCmd.Close acReport, stDocName, acSaveNo
MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
' Log the invoice details
' Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset("tblPrintedReports")
rs.AddNew
rs!PrintDate = Now()
rs!Recipient = Me![FirstName] & " " & Me![Surname]
rs!Document = stDocName
rs.Update
'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++
Set rst = Nothing
Set rs = Nothing
MsgBox "The Subscription Invoice for " & rst![FirstName] & " " & rst![Surname] & " has been sent."
Exit_EmailTo_Click:
Exit Sub
Err_EmailTo_Click:
MsgBox Err.Description
Resume Exit_EmailTo_Click
End If
End Sub