How do I add the below SQL query AS the recipient of an Outlook email?
SELECT
E.Email
FROM
Employee E
INNER JOIN ProposalTracking P ON E.Initials = P.UW
Here's the VBA code which merely opens a new email in Outlook so far:
Private Sub btnEmail_Click()
Dim varName As Variant
Dim varSubject As Variant
Dim varBody As Variant
varName = "first.last@example.com"
varSubject = "TPS Report"
varBody = "Did you see the memo? We use the new cover sheets now."
DoCmd.SendObject , , , varName, , varSubject, varBody, True, False
End Sub
One way of doing it through DAO:
Dim email as string
Dim sqlSTR as string
Dim r As DAO.Recordset
Dim dbs as DAO.database
set dbs = currentDB()
sqlSTR = "SELECT E.Email FROM Employee E INNER JOIN ProposalTracking P ON E.Initials = P.UW"
Set r = dbs.OpenRecordset(Name:=sqlSTR , Type:=dbOpenSnapshot)
varName = r![Email]
r.close
Although, if doing it this way, I would add variable in the where clause. It would look like this:
Dim varSubject As Variant
Dim varBody As Variant
Dim email as string
Dim sqlSTR as string
Dim r As DAO.Recordset
Dim varInitials as string
Dim dbs as DAO.database
set dbs = currentDB()
varInitials = "P.UW"
sqlSTR = "SELECT E.Email FROM Employee E INNER JOIN ProposalTracking P ON E.Initials = P.UW WHERE " & someField & " = & " & """" & someVariable & """"
Set r = dbs.OpenRecordset(Name:=sqlSTR , Type:=dbOpenSnapshot)
varName = r![Email]
r.close
varSubject = "TPS Report"
varBody = "Did you see the memo? We use the new cover sheets now."
DoCmd.SendObject , , , varName, , , varSubject, varBody, True, False
In the sqlSTR, replace someField with whatever your matching is (for example, P.UW) and someVariable with some value you wish to match (e.g. "John"). If it's an int value, remove the & """" from around the someVariable value
