Access Email

[cbuechner]

I am learning some code and am generally interested. I am building a database. For this purpose I have the following.

Router2: Table that has "Family", "Department", "Name"
EMails: Table that has "Name" , "EMail"
Eng_types: Table that has "engine_type", and "Family"

Routerchain: Quary that combines all of this and creates an email list. This is where I am trying to send the email from.

I have this so far. When I put in the quary a filter [Engine_Type:], this doesn't work. I also want the subject line to include "Please review the following new part" and "Engine Part". If I take out the quary filter, it works, but I get an email with all of the emails. I want it filtered.



Public Function CreateRIT_ReportEmail()


'write the default Outlook contact name list to the active worksheet
Dim rs 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")
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"


'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Function

[cbuechner]

I was able to figure out the subject line

Public Function CreateRIT_ReportEmail()

'write the default Outlook contact name list to the active worksheet
Dim rs 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")
Set SR = CurrentDb.OpenRecordset("SELECT engine_type from routerchain")
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

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Function

[CJ_London]

If I take out the quary filter, it works, but I get an email with all of the emails. I want it filtered.

I presume you mean a criteria. would be really helpful to know what you actually used as a filter ('a filter [Engine_Type:]' doesn't mean anything), it needs to be equal to something and I see nothing in function as to what that may be or what datatype.

have you tried

Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_Type=" & inputbox("Enter Engine Type"))

This assumes engine type is numeric

[cbuechner]

Sorry,

Yes I mean criteria. I added a criteria to the query. What you have would work, but it is not numeric.

I thought of a new way to do this to get what I want. I added a combo box (combo16) to routerchain form. I select the value.

Set rs = CurrentDb.OpenRecordset("SELECT Email FROM routerchain WHERE Engine_Type=" & Forms!Routerchain!combo16")

Not working. Either way is helpful.

[cbuechner]

OK,

I added the Engine_ID to the quarry and used your code and it works.

Thank you!

[CJ_London]

Happy to help, good luck with your project