Solved

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

Posted on 2004-04-03
16
269 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!
0
Comment
Question by:Geerd
16 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 10747714
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
0
 
LVL 17

Accepted Solution

by:
walterecook earned 175 total points
ID: 10747724
So you need a recordset?
Set a reference to DAo 3.6 Object library

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

I haven't tested this but it looks pretty good.
Hope it helps
Walt
0
 
LVL 39

Expert Comment

by:stevbe
ID: 10747731
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10747741
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


0
 
LVL 1

Author Comment

by:Geerd
ID: 10747743
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



0
 
LVL 17

Expert Comment

by:walterecook
ID: 10747792
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


0
 
LVL 1

Author Comment

by:Geerd
ID: 10747809
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 :-)
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Geerd
ID: 10747817
Walt,

Perhaps we need to Dim the sSQL?

0
 
LVL 17

Expert Comment

by:walterecook
ID: 10747888
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
0
 
LVL 1

Author Comment

by:Geerd
ID: 10747896
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" ?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10747912
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
0
 
LVL 1

Author Comment

by:Geerd
ID: 10748162
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!!!
0
 
LVL 5

Assisted Solution

by:Emanon_Consulting
Emanon_Consulting earned 175 total points
ID: 10748327
Hi Geerd,

If you're interested...
I use a form with a couple of tabs on it to gererate email to multiple contacts.
This is what it looks like...

Mine sends to one 'To' address and multiple 'Cc' address's
User types Subject and Body and can select what report from the application they want to attach.  It is set up for only one attachment (at this time).
I use Unbound Fields for all.  The 'To' and 'Cc' email addresses come from record source queries.

txtTo                    Combo Box (bad naming convention should be 'cboTo')
txtSubject             Text Box
txtBody                 Text Box
cboObject             Combo Box (yeah for naming convention)
lstSelectContacts   List Box

I have a Command button (cmdSendEmail) to execute my code.
I also have a command button (cmdSelectAll) to select all of the contacts in the list box for 'Cc'
And a command button (cmdDeselectAll) to deselect all the contacts in the list box for 'Cc'

Here's all the code...  (forgive the lack of comments)

'************Start Code*****************
Option Compare Database
Option Explicit

Dim lngCount As Long
Dim lngListCount As Long
Dim lstCC As Access.ListBox

Private Sub cmdSelectAll_Click()

On Error GoTo ErrorHandler

    Set lstCC = Me![lstSelectContacts]
    lngListCount = Me![lstSelectContacts].ListCount
   
    For lngCount = 0 To lngListCount
        lstCC.Selected(lngCount) = True
    Next lngCount
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Private Sub cmdDeselectAll_Click()

On Error GoTo ErrorHandler

    Set lstCC = Me![lstSelectContacts]
    lngListCount = Me![lstSelectContacts].ListCount
   
    For lngCount = 0 To lngListCount
        lstCC.Selected(lngCount) = False
    Next lngCount
   
ErrorHandlerExit:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

End Sub

Private Sub cmdSendEmail_Click()

    ' Prevent error screen if user cancels without sending mail.
    On Error Resume Next
   
    Dim strTo As String
    Dim strCC As String
    Dim varCC As Variant
    Dim strEMailRecipient
   
    Dim strAttachment As String
    Dim varAttach As Variant
    Dim lstAttach As Access.ListBox
    Dim strEMailAttachment
    Dim strSubject As String
    Dim strMsgBody As String
    Dim intSeeOutlook As Integer
   
    If IsNull(Me.txtTo) Then
        MsgBox "Please enter a To... Contact", vbInformation, conAppName
        Me.txtTo.SetFocus
        GoTo ErrorHandlerExit
    ElseIf IsNull(Me.txtSubject) Then
        MsgBox "Please enter a subject", vbInformation, conAppName
        Me.txtSubject.SetFocus
        GoTo ErrorHandlerExit
    ElseIf IsNull(Me.txtBody) Then
        MsgBox "Please enter a message body", vbInformation, conAppName
        Me.txtBody.SetFocus
        GoTo ErrorHandlerExit
    End If
   
    ' Determine if user wants to preview message in Outlook window.
    intSeeOutlook = MsgBox("Preview e-mail message before sending?", _
                        vbYesNo, conAppName)

    ' If user wants to directly send item, get recipient's address.
    If intSeeOutlook = vbNo Then
        intSeeOutlook = False
    End If
   
    Set lstCC = Me.lstSelectContacts
Set lstAttach = Me.lstSelectAttachment
       
        'Add contacts to variable for CC field
        For Each varCC In lstCC.ItemsSelected
            'Check for email address
            strEMailRecipient = Nz(lstCC.Column(1, varCC))
            Debug.Print "EMail address: " & strEMailRecipient
            If strEMailRecipient <> "" Then
                strCC = strCC & strEMailRecipient & ";"
            End If
        Next varCC
       
'Add Attachments to variable for Attachment field
For Each varAttach In lstAttach.ItemsSelected
    'Check for ?
    strEMailAttachment = Nz(lstAttach.Column(1, varAttach))
    Debug.Print "EMail attachment: " & strEMailAttachment
    If strEMailAttachment <> "" Then
        strAttachment = strAttachment & strEMailAttachment & ";"
    End If
Next varAttach
       
        strCC = Left(strCC, Len(strCC) - 1)
        strTo = Me.txtTo.Value
        strSubject = Me.txtSubject.Value
        strMsgBody = Me.txtBody
        strAttachment = Me.cboObject
   
    ' Send in RTF format.
    ' Open Outlook window if intSeeOutlook is True.
    DoCmd.SendObject acSendReport, strAttachment, acFormatSNP, _
              strTo, strCC, , strSubject, _
              strMsgBody, intSeeOutlook
             
ErrorHandlerExit:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

End Sub

'************Code End*******************

Hope some of this is helpful...
Good Luck!
Cheers
M
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10748571
- 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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now