We help IT Professionals succeed at work.

VBA: Getting SQL into String to use in Mail function.

Geerd
Geerd asked
on
Medium Priority
398 Views
Last Modified: 2008-03-04
Hello my dear Experts!

I've got a form called frmMain. This form has a number of tabs. One of those tabs is used to email data to certain users.

The field (To: in outlook) should be populated by the email adresses of users I've got in the database. The SQL to retrieve the email adresses is:

**********

SELECT [tblDeelnemer].[Deelnemer_Email]
FROM tblReservering INNER JOIN (tblDeelnemer INNER JOIN tblDeelnemer_Reservering ON [tblDeelnemer].[Deelnemer_ID]=[tblDeelnemer_Reservering].[Deelnemer_ID]) ON [tblReservering].[Reserverings_ID]=[tblDeelnemer_Reservering].[Reserverings_ID]
WHERE ((([tblDeelnemer_Reservering].[Reserverings_ID])=[Forms]![frmMain]![Reserverings_ID]));

**********

On the Tab I've also got a button "Bevestigen" which initiates the email procedure. It's got the following code:

**********

Private Sub Bevestigen_Click()

    ' Prevent error screen if user cancels without sending mail.
    On Error Resume Next
   
    Dim strToWhom     As String
    Dim strMsgBody    As String
    Dim intSeeOutlook As Integer
    Dim strSubject    As String
       
    strMsgBody = Me.txtBody
    strSubject = Me.txtSubject
       
    DoCmd.SendObject acSendQuery, "qryDatum_Overzicht", acFormatRTF, _
              strToWhom, "Zoetermeer@info.nl", , strSubject, _
              strMsgBody, intSeeOutlook

End Sub

**********

I want to populate the string strToWhom with email adresses (Above SQL) .

Any help is welcome!
Comment
Watch Question

Commented:
Hi Geerd

I don't know how to do it with the SQL, but I can show you how to to do it another way,

1-
Add a field in the SQL to count the number of resultant emails.
2-
Create a contiuous Form call it EmailForm which will read its Emails form the above SQL, call the To email field as ToEmail, call the count as TotalRecords
3-
This code should be in Private Sub Bevestigen_Click(), before the rest of the existing code:

strToWhom=""
docmd.open, acForm "EmailForm"
For i=1 to TotalRecords
strToWhom=strToWhom & ";" & me!ToEmail
if i<TotalRecords then docmd.gotorecord, acNext
next i
strToWhom=left(strToWhom,2)
docmd.close "EmailForm"


thats it,

Please fine tune the code as I am doing it off my head.

Hopethis helps

jaffer
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
save your SQL as a query called qselMailDistributionList


Private Sub Bevestigen_Click()

    ' Prevent error screen if user cancels without sending mail.
    On Error Resume Next
    'I would capture the exact error and bypass that one rather than skip all errors


    Dim strToWhom     As String
    Dim strMsgBody    As String
    Dim intSeeOutlook As Integer
    Dim strSubject    As String
    Dim rst As DAO.Recordset

    Set rst = CurrentDB.OpenRecordset("SELECT * FROM qselMailDistributionList")

    Do While Not rst.EOF
        strToWhom= strToWhom & rst!Deelnemer_Email  & ";"
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
       
    strMsgBody = Me.txtBody
    strSubject = Me.txtSubject
       
    DoCmd.SendObject acSendQuery, "qryDatum_Overzicht", acFormatRTF, _
              strToWhom, "Zoetermeer@info.nl", , strSubject, _
              strMsgBody, intSeeOutlook

End Sub

Steve
CERTIFIED EXPERT
Top Expert 2016

Commented:
Try this

Private Sub Bevestigen_Click()
Dim rst As Recordset, db As Database
Dim strEmail as String
Dim strToWhom As String

strEmail = "SELECT [tblDeelnemer].[Deelnemer_Email] "
strEmail = strEmail & "FROM tblReservering INNER JOIN (tblDeelnemer INNER JOIN tblDeelnemer_Reservering "
strEmail = strEmail & "ON [tblDeelnemer].[Deelnemer_ID]=[tblDeelnemer_Reservering].[Deelnemer_ID]) "
strEmail = strEmail & "ON [tblReservering].[Reserverings_ID]=[tblDeelnemer_Reservering].[Reserverings_ID] "
strEmail = strEmail & "WHERE ((([tblDeelnemer_Reservering].[Reserverings_ID])=[Forms]![frmMain]![Reserverings_ID]));"

Set db = CurrentDb()
Set rst=db.OpenRecordset("strEmail")
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strToWhom = strToWhom &";" &  rst.Fields(0)
      rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
  End If


Author

Commented:
Hi Walt,

your solution looks good. I pasted the date into my form.

Could you show me how I can break up the SQL...... it must be someting like & _
but I'm nog sure. The second line that starts with  "FROM" is coloured red.

Ive got a good feeling about your solution



Oh yea,
either put it all all one line (works but not pretty) or add quote and & and _'s
"SELECT [tblDeelnemer].[Deelnemer_Email]" & _
"FROM tblReservering INNER JOIN (tblDeelnemer INNER JOIN tblDeelnemer_Reservering ON [tblDeelnemer].[Deelnemer_ID]" & _
"=[tblDeelnemer_Reservering].[Deelnemer_ID]) ON [tblReservering].[Reserverings_ID]=[tblDeelnemer_Reservering].[Reserverings_ID]" & _
"WHERE ((([tblDeelnemer_Reservering].[Reserverings_ID])=[Forms]![frmMain]![Reserverings_ID]));"

That help?

Walt


Author

Commented:
Hi Walt.....

I don't exactly know what is going on now. I did what you told me and I think it's pretty good. But when I push my button everything grinds to a halt. Access is not responding anymore. I need to start up again. I don't know whats going on because I don't even get an error message. I'm using the code below.

Private Sub Bevestigen_Click()

    ' Prevent error screen if user cancels without sending mail.
    On Error Resume Next
   
    Dim strToWhom     As String
    Dim strMsgBody    As String
    Dim intSeeOutlook As Integer
    Dim strSubject    As String
       
    'hier de code van expert
   
    Dim rs As DAO.Recordset
    Dim sSQL

sSQL = "SELECT [tblDeelnemer].[Deelnemer_Email]" & _
       "FROM tblReservering INNER JOIN (tblDeelnemer INNER JOIN tblDeelnemer_Reservering ON [tblDeelnemer].[Deelnemer_ID]=[tblDeelnemer_Reservering].[Deelnemer_ID]) ON [tblReservering].[Reserverings_ID]=[tblDeelnemer_Reservering].[Reserverings_ID]" & _
       "WHERE ((([tblDeelnemer_Reservering].[Reserverings_ID])=[Forms]![frmMain]![Reserverings_ID]));"

Set rs = CurrentDb.OpenRecordset(sSQL)
strToWhom = ""
Do Until rs.EOF
 strToWhom = strToWhom & rs.Fields(0) & ","
rs.MoveNext
Loop
Set rs = Nothing
strToWhom = Left(strToWhom, Len(strToWhom) - 1) ' strip final comma
   
    strMsgBody = Me.txtBody
    strSubject = Me.txtSubject
   
   
   
    DoCmd.SendObject acSendQuery, "qryDatum_Overzicht", acFormatRTF, _
              strToWhom, "Zoetermeer@info.nl", , strSubject, _
              strMsgBody, intSeeOutlook

End Sub


Do you know what's going on?

ThanX

BTW: thanks for all the comments by the other experts, I can only handle one at a time :-)

Author

Commented:
Walt,

Perhaps we need to Dim the sSQL?

Well geerd
You have dim sSQL
Everything looks ok so I'd put a breakpoint in and make sure strTowhom is being built.
If not it would seem your SQL is not returning anything.
Is it possible that strToWhom is TOO long?

Walt

Author

Commented:
No, strToWhom has only got 2 email adresses (about 30 characters)
I've got the SQL in a query that returns 2 two email adresses when frmMain is open.

I can't debug because everything locks when I push the button.....

What do you mean with a "breakpoint" ?
Stop your code to debug.  
If you are sitting on the line of code hit F9.  It will turn red.  Walk through your code from there.

Walt

Author

Commented:
When I'm viewing the code and press the run button with de frmMain open I can't execute the code.
A popup window asks me for the macro????

When I try to insert a breakpoint for debugging a message tells me I can't insert one.

Hellllllpppppp please!!!!

I'll increase the point!!!
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
- To put in a breakpoint, the code execution must be stopped.  It sounds like you may have already been in break mode.
The event on your button should say [event procedure], does it?  If it does not, whatever you have there, Access thinks is the name of a macro.
Click on that line then click the ... to the right.

Walt
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.