Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

get "to" from SQL in DoCmd.SendObject

Status
Not open for further replies.

MHPGuy

IS-IT--Management
Mar 14, 2002
143
US
I posted this (incorrectly) in queries, hoping I get a better result here...

Any help you can give on this would be great. I've got everything ready but the "TO", and it's giving me a hell of a time...

I'm trying to get my form to send an email to the "recipient" that is listed on the form. The email is basically going to a wireless carrer, which will make an SMS message from the email. The recipient is listed in a combo box on the form that is pulling from tbl_RECIPIENTS.NAME . The "TO" address is basically supposed to be formatted TBL_CARRIERS.PREFIX & tbl_RECIPIENTS.PHONE & TBL_CARRIERS.SUFFIX & TBL_CARRIERS.DOMAIN .

I've tried using the following sql to pull the info (into "FOO" below), but I can't get it to work. :

Code:
SQL:  
SELECT TBL_CARRIERS.PREFIX, TBL_RECIPIENTS.PHONE, TBL_CARRIERS.SUFFIX, TBL_CARRIERS.DOMAIN FROM TBL_RECIPIENTS RIGHT JOIN (TBL_CARRIERS RIGHT JOIN TBL_MESSAGE ON TBL_CARRIERS.CARRIER = TBL_MESSAGE.CARRIER) ON TBL_RECIPIENTS.NAME = TBL_MESSAGE.RECIPIENT WHERE [Forms]![FRM_MESSAGE]![RECIPIENT]= tbl_RECIPIENTS.NAME 
Access 2003
relevant tables/fields/forms:

tbl_RECIPIENTS.NAME
tbl_RECIPIENTS.PHONE
tbl_RECIPIENTS.CARRIER (LOOKUP TO tbl_CARRIERS.CARRIER)
TBL_CARRIERS.CARRIER
TBL_CARRIERS.PREFIX
TBL_CARRIERS.DOMAIN
TBL_CARRIERS.SUFFIX
[Forms]![FRM_MESSAGE]![RECIPIENT]


Here's the code I'm using to generate the email (FOO replaces the "TO" address):

Code:
Private Sub Command17_Click()
Dim subject As String, Body As String, strEmailAddress As String
strEmailAddress = FOO
subject = "New Referral"
Body = "FACILITY: " & [FACILITY] & Chr$(13) & "ROOM: " & [ROOM] & Chr$(13) & "PATIENT: " & [PATIENT] & Chr$(13) & [MESSAGE] & Chr$(13) & [HIDEUSER]
DoCmd.SendObject , "Send this Referral", aForm, strEmailAddress, , "name@gmail.com", subject, Body, False

End Sub

Michael Phipps
Technical Business Analyst
Mercy Health Plans
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top