get to from query in DoCmd.SendObject 1

[MHPGuy]

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. :

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):

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

 

[Melagan]

Where does this 'FOO' variable come from? Are you using the above SQL to populate a recordset, then populating your FOO variable from said recordset?

There are some guys here that are smart enough to put a solution together with all of the given information, but I am not one of them I need some more info.

Furthermore, although I hate to encourage people to reinvent the wheel (though in this case we'd be making a round wheel instead of a square wheel), I would suggest having a look into CDO.Message.

SendObject is more for just that...sending objects like tables and reports.

Here is an example of CDO.Message I wrote for one of my projects.

Public Sub SendEmail(TheName As String, TheAddress As String)
On Error GoTo ErrHandler
'Sends email to specified name, "TheName", to specified email address, "TheAddress", using
'C:\email.txt as the email body.
Dim strRcpt, strSender As String
Dim objMessage As CDO.Message
Dim strSMTP As String
Dim strBodyText As String
    strBodyText = ""
'====Comment this section out if you want to build message body text
'====programatically instead of in an external file. You will have to build 
'====strBodyText.
Dim f
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim objFSO As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set f = objFSO.OpenTextFile("C:\email.txt", ForReading)
    strBodyText = f.ReadAll
    f.Close
    Set f = Nothing
    Set objFSO = Nothing
'====^^^^^^^^^^^
'====End Comment

    strSMTP = "smtp.yourserver.com"
    strRcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
    strSender = Chr(34) & "Sender Name" & Chr(34) & "<senderemail@domain.com>"
    Set objMessage = CreateObject("CDO.Message")
    With objMessage
        .Subject = "Email Subject Here"
        .From = strSender
        .To = strRcpt
        .HTMLBody = strBodyText
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
        'Name or IP of remote SMTP server
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = strSMTP
        'Server port
        .Configuration.Fields.Update
        .Send
    End With
    
ExitHandler:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & ": " & Err.Description & vbCrLf & _
        "Please contact the database administrator.", vbCritical, "Error Sending Message"
    Resume ExitHandler
End Sub

~Melagan
______
"It's never too late to become what you might have been.

 

[MHPGuy]

Hey- thanks for replying!

"FOO" is basically the result of the query I'm tryng to get work. I can write the sql to get it to work, but getting access to put the result in the TO line is my problem.

I have to admit I'm a bit of a n00b at this stuff- I'm more of a crystal/BO guy, so this is out of my comfort zone. So, you've pretty much lost me.

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[Melagan]

Alrighty - I understand where you're at now; no problem!

The thing with SQL is that you can not, to my knowledge, directly populate a variable using SQL alone. If you are really wanting to use SendObject, then you're going to have to put your SQL into a recordset and then populate your variable that way. Here is what I am thinking; add this function to your form and change your command button's code to the sub below. What do you think?

Function GetFOO()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FOO As String
    'Variable for SQL
    strSQL = "SELECT TBL_CARRIERS.PREFIX " _
            & "TBL_RECIPIENTS.PHONE " _
            & "TBL_CARRIERS.SUFFIX " _
            & "TBL_CARRIERS.DOMAIN AS theRecipient" _
            & "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"
    Debug.Print strSQL 'for debugging
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    FOO = rst![theRecipient]
    GetFOO = FOO
End Function

Private Sub Command17_Click()
Dim strSubject, strBody, strEmailAddress As String
strEmailAddress = GetFOO()
strSubject = "New Referral"
strBody = "FACILITY: " & [FACILITY] & Chr$(13) & "ROOM: " & [ROOM] & Chr$(13) & "PATIENT: " & [PATIENT] & Chr$(13) & [Message] & Chr$(13) & [HIDEUSER]

    DoCmd.SendObject , "Send this Referral", , strEmailAddress, , _
        "name@gmail.com", strSubject, strBody, False

End Sub

~Melagan
______
"It's never too late to become what you might have been.

 

[MHPGuy]

I'm running into an error when I try to use that. You're pretty much speaking greek, so I really appreciate the time you're taking with me.

The error reads:

Run Time error 3075

Syntax error (missing operator) in query expression
'TBL_CARRIERS.PREFIX TBL_RECIPIENTS.PHONE
TBL CARRIERS.SUFFIX TBL_CARRIERS.DOMAIN'

When I click Debug, the following is hilighted:

 Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Any help or explanation you can give is greatly appreciated. Thansk for your work so far.

-Michael

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[lespaul]

You are missing commas between the SELECT field and a space before the FROM:

strSQL = "SELECT TBL_CARRIERS.PREFIX[COLOR=red],[/color] " _
            & "TBL_RECIPIENTS.PHONE[COLOR=red],[/color] " _
            & "TBL_CARRIERS.SUFFIX[COLOR=red],[/color] " _
            & "TBL_CARRIERS.DOMAIN AS theRecipient[COLOR=red] [/color]" _
            & "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"

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MHPGuy]

Okay, now I'm getting another error 3021- no current record. I'm in the form, and have a test record up, but apparently it can't see that. The debug hilights

    FOO = rst![theRecipient]

Is the problem possibly in the part of the sql where I'm asking it to read the form to get the recipient name? That's the part of the query I guessed on:

            & "WHERE '" & [Forms]![FRM_MESSAGE]![RECIPIENT] & "' = tbl_RECIPIENTS.NAME"

Any suggestions?



Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[Melagan]

My bad - I meant to concatenate all of those fields into one field. Try this for strSQL.

    strSQL = "SELECT TBL_CARRIERS.PREFIX & " _
            & "TBL_RECIPIENTS.PHONE & " _
            & "TBL_CARRIERS.SUFFIX & " _
            & "TBL_CARRIERS.DOMAIN AS theRecipient" _
            & "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"

If it still doesn't work, do this:

Set a break point at the line reading "Set dbs = CurrentDb"
(do so by clicking on the verticle gray bar on the left side of your code - you should see a little red dot appear)

This will cause your code to pause in the middle of execution at that point. I am interested to see what is displayed in your Immediate window - the Debug.Print strSQL line will help us out - cut and paste what it says, yeah?

~Melagan
______
"It's never too late to become what you might have been.

 

[MHPGuy]

Thanks for continuing to help!

It didn't work (still get the "no current record" message), and I set the break point as instructed. When I click the button it just takes me back to the code with the line hilighted. I've included the code I have in there now:

Option Compare Database
Function GetFOO()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim FOO As String
    'Variable for SQL
            strSQL = "SELECT TBL_CARRIERS.PREFIX & " _
            & "TBL_RECIPIENTS.PHONE & " _
            & "TBL_CARRIERS.SUFFIX & " _
            & "TBL_CARRIERS.DOMAIN AS theRecipient " _
            & "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"
    Debug.Print strSQL 'for debugging
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    FOO = rst![theRecipient]
    GetFOO = FOO
End Function

Private Sub Command17_Click()
Dim strSubject, strBody, strEmailAddress As String
strEmailAddress = GetFOO()
strSubject = "New Referral"
strBody = "FACILITY: " & [FACILITY] & Chr$(13) & "ROOM: " & [ROOM] & Chr$(13) & "PATIENT: " & [PATIENT] & Chr$(13) & [MESSAGE] & Chr$(13) & [HIDEUSER]

    DoCmd.SendObject , "Send this Referral", , strEmailAddress, , _
        "name@gmail.com", strSubject, strBody, False
End Sub

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[Melagan]

You probably have your Immediate window hidden. In your code window, hit control-g to show it. If you don't have any luck with that - change this line:
Debug.Print strSQL

to ...

Msgbox strSQL

The goal here is to see the SQL in a more readable format and make sure that it is pulling the value from your form correctly.

~Melagan
______
"It's never too late to become what you might have been.

 

[MHPGuy]

Thank you! glad I was just missing something. Here's the text from the immediate window:

SELECT TBL_CARRIERS.PREFIX & TBL_RECIPIENTS.PHONE & TBL_CARRIERS.SUFFIX & TBL_CARRIERS.DOMAIN AS theRecipient 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 'MICHAEL PHIPPS' = tbl_RECIPIENTS.NAME

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[Melagan]

And this?

    strSQL = "SELECT TBL_CARRIERS.PREFIX & " _
            & "TBL_RECIPIENTS.PHONE & " _
            & "TBL_CARRIERS.SUFFIX & " _
            & "TBL_CARRIERS.DOMAIN AS theRecipient" _
            & "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 tbl_RECIPIENTS.NAME = '" & [Forms]![FRM_MESSAGE]![RECIPIENT] & "'"

~Melagan
______
"It's never too late to become what you might have been.

 

[MHPGuy]

WOW

That did it! Thank you so much. I really appreciate all of your help.

Michael Phipps
Technical Business Analyst
Mercy Health Plans

 

[Melagan]

My pleasure - I'm glad it worked out! I would like to ask anyone else who has been watching this thread if they would have had a different approch to this project. This was the first thing I thought of but still wonder if there are any other (simpler) ways.

Cheers~

~Melagan
______
"It's never too late to become what you might have been.