I wanted to select the subject line as SET SR then Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain then OlMail.Subject = "New Quote Process Input" & " " & SR!Engine_Type
This did not select the proper subject line. This selected the first line in the QY. I then added a Inputbox, however, now I have two input boxes and have to enter the number twice.
If it is possible, I would like to reduce this to just one inputbox. In adiditon, but not absolutely necessary, I would like to add the signature line. When I include the attachment OlMail.Attachments.Add "K:\Public\Quote\Instructions\PDF Instructions.pdf", the signature line disappears.
Here is the code I have so far
Public Function CreateRIT_ReportEmail()
'write the default Outlook contact name list to the active worksheet
Dim rs As DAO.Recordset
Dim SR As DAO.Recordset
Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As String
Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)
Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_ID=" & Inputbox("Enter Engine Type"))
Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain Where Engine_ID=" & Inputbox("Enter Engine Type"))
Do While rs.EOF = False
ToRecipient = rs!Email
OlMail.Recipients.Add ToRecipient
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'fill in Subject field
OlMail.Subject = "New Quote Process Input" & " " & SR!Engine_Type
OlMail.Body = "Please Review the Instructions and complete applicable sections"
OlMail.Attachments.Add "K:\Public\Quote\Instructions\PDF Instructions.pdf"
'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it
End Function