Solved Sending email with QueryFieldAsSeparatedString (1 Viewer)

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
Good morning,

Let me preface this with I do not really know vb coding. I basically find bits and pieces here and there to try and make it work for me. I am stumped on a send email code. I have built a contacts database. This will house name, org, and contact information such as email and phone numbers. One of the key features of this database is the ability to send email to contacts based on lists created for various committees. So these lists are obviously queries. I have several queries containing lists of email addresses. The queries will be listed in a List Box in a form. The user will select a query from the list box, and click a button to send email to everyone in the query.

I am using the function QueryFieldAsSeparatedString to delimit the list of names in the Email field. That part of this works fine. What is NOT happening is being able to get the delimited list of emails into an outlook email. When I click the button, nothing happens.

Here is my code (lstQueryList is the listbox containing all the queries. See below for a pic of the form):
Code:
Private Sub btnSendEmail_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Database
   
    Dim emailTo As String
    Dim retVal As String
   
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
   
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If
   
        emailTo = QueryFieldAsSeparatedString("EmailAddress", lstQueryList.Value, , , ",")
End Sub
AccessForm.PNG
 

Isaac

Lifelong Learner
Local time
Today, 14:23
Joined
Mar 14, 2017
Messages
8,738
So you don't actually have any code to create an email. Your code stops without actually doing anything. Is that what you're intending?
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
So you don't actually have any code to create an email. Your code stops without actually doing anything. Is that what you're intending?
Thank you for your reply. No; that is not the intention. As I mentioned, I don't really know vb coding, so this had been pieced together from other codes I found hoping to achieve this end. I appreciate any suggestions you can offer.
 

Isaac

Lifelong Learner
Local time
Today, 14:23
Joined
Mar 14, 2017
Messages
8,738
Look into setting outMail as: the CreateItem method of the application, and then you can assign outMail.to = emailTo, then you can use the Display (or Send) method of the outMail variable
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
Look into setting outMail as: the CreateItem method of the application, and then you can assign outMail.to = emailTo, then you can use the Display (or Send) method of the outMail variable
Thank you. How do I code that?
 

Isaac

Lifelong Learner
Local time
Today, 14:23
Joined
Mar 14, 2017
Messages
8,738
Instead of just writing the code for you, I've chosen to point you in the right direction. Now it will take a small amount of effort on your part to study those methods. This way you actually learn something, rather than just being handed the final product. I hope that makes sense.
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
Instead of just writing the code for you, I've chosen to point you in the right direction. Now it will take a small amount of effort on your part to study those methods. This way you actually learn something, rather than just being handed the final product. I hope that makes sense.
Well gee thank you. This is only the umpteenth incarnation of this coding and approximately 14 months I've struggled with this one. I get that that is not effort enough. But thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:23
Joined
Oct 29, 2018
Messages
21,358
When I click the button, nothing happens.
Hi. Welcome to AWF!

That's probably not the case. Something has to be happening, you probably just can't see it. Do you know how to step through your code during execution?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
Does this do anything? You may need to add the reference to Outlook in your references.
Code:
try calling this function
Private Sub btnSendEmail_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Database
  
    Dim emailTo As String
    Dim retVal As String
  
     emailTo = QueryFieldAsSeparatedString("EmailAddress", lstQueryList.Value, , , ",")
     debug.print emailTo
     SendEmailOutlook(emailTo,"Test",Test")
End Sub


Public Function SendEmailOutlook( _
    MsgTo As String, _
    MsgSubject As String, _
    MsgBody As String, _
    )
    
    On Error GoTo SendEmailOutlook_Error
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem
    
    Set olMailItem = olApp.CreateItem(0)
    
    With olMailItem
    .To = MsgTo
    .Subject = MsgSubject
    .Body = MsgBody
     .Send
    End With
    Set olMailItem = Nothing
    Set olApp = Nothing
    
    On Error GoTo 0
    Exit Function

SendEmailOutlook_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendEmailOutlook, line " & Erl & "."

End Function
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
Does this do anything? You may need to add the reference to Outlook in your references.
Code:
try calling this function
Private Sub btnSendEmail_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Database

    Dim emailTo As String
    Dim retVal As String

     emailTo = QueryFieldAsSeparatedString("EmailAddress", lstQueryList.Value, , , ",")
     debug.print emailTo
     SendEmailOutlook(emailTo,"Test",Test")
End Sub


Public Function SendEmailOutlook( _
    MsgTo As String, _
    MsgSubject As String, _
    MsgBody As String, _
    )
  
    On Error GoTo SendEmailOutlook_Error
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem
  
    Set olMailItem = olApp.CreateItem(0)
  
    With olMailItem
    .To = MsgTo
    .Subject = MsgSubject
    .Body = MsgBody
     .Send
    End With
    Set olMailItem = Nothing
    Set olApp = Nothing
  
    On Error GoTo 0
    Exit Function

SendEmailOutlook_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendEmailOutlook, line " & Erl & "."

End Function
Thank you for your reply!
I'm getting syntax error on the SendEmailOutlook(emailTo,"Test","Test")
What should I be placing in those "Test" fields? (It doesn't open up the arguments to give me any direction)

(Also, yes, all the outlook reference libraries have been added)
 
Last edited:

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
Hi. Welcome to AWF!

That's probably not the case. Something has to be happening, you probably just can't see it. Do you know how to step through your code during execution?
No. I don't know what that means. Thank you,
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
I want to also add that I do have a module saved for the QueryFieldAsSeparatedString

Code:
Public Function QueryFieldAsSeparatedString(ByVal fieldName As String, _
                                            ByVal tableOrQueryName As String, _
                                            Optional ByVal criteria As String = "", _
                                            Optional ByVal sortBy As String = "", _
                                            Optional ByVal delimiter As String = ", " _
                                        ) As String

' Paramter description
'   fieldName           =   Is the name of the field containing the values
'                           we want in our comma separated string
'   tableOrQueryName    =   Is the name of table or query containing the column
'   criteria            =   The criteria to filter the data
'   sortBy              =   An optional sort expression to sort the data
'   delimiter           =   The delimiter used to separate the values. It defaults
'                           to a comma and a blank, but you can use anything you
'                           like there


    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim sql             As String
    Dim whereCondition  As String
    Dim sortExpression  As String
    Dim retVal          As String
    
    Set db = CurrentDb
    
    ' If there where any criteria passed to the function, we build a WHERE-condition for SQL
    If Len(criteria) > 0 Then
        whereCondition = " WHERE " & criteria
    End If
    
    ' If there was a sort expression passed to the function, we build a ORDER BY for SQL
    If Len(sortBy) > 0 Then
        sortExpression = " ORDER BY " & sortBy
    End If
        
    ' building the complete SQL string
    sql = "SELECT " & fieldName & " FROM " & tableOrQueryName & whereCondition & sortExpression

    ' opening a recordset
    Set rs = db.OpenRecordset(sql, dbOpenForwardOnly, dbReadOnly)
    Do Until rs.EOF
        ' here we are looping through the records and, if the value is not NULL,
        ' concatenate the field value of each record with the delimiter
        If Not IsNull(rs.Fields(0).Value) Then
            retVal = retVal & Nz(rs.Fields(0).Value, "") & delimiter
        End If
        rs.MoveNext
    Loop
    
    ' we cut away the last delimiter
    retVal = Left(retVal, Len(retVal) - Len(delimiter))
    
    ' setting the return value of the function
    QueryFieldAsSeparatedString = retVal
    
    ' cleaning up our objects
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
End Function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
Thank you for your reply!
I'm getting syntax error on the SendEmailOutlook(emailTo,"Test","Test")
What should I be placing in those "Test" fields? (It doesn't open up the arguments to give me any direction)

(Also, yes, all the outlook reference libraries have been added)
The arguments are pretty clear I would think
Code:
Public Function SendEmailOutlook( _
    MsgTo As String, _
    MsgSubject As String, _
    MsgBody As String, _
    )
MsgTo should be your string of addresses
MsgSubject should be the subject
MsgBody should be the email body

So I just passed the strings "Test" in for the body and subject. You can pass in any string you want.

What was the error"
I also put in a debug.print for your EmailTo so you can look at it and see if the correct string is built. Reading the code I would expect a proper email string of delimited emails.
name.name@something.com; name2asdf@somehting2.net.....
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
The arguments are pretty clear I would think
Code:
Public Function SendEmailOutlook( _
    MsgTo As String, _
    MsgSubject As String, _
    MsgBody As String, _
    )
MsgTo should be your string of addresses
MsgSubject should be the subject
MsgBody should be the email body

So I just passed the strings "Test" in for the body and subject. You can pass in any string you want.

What was the error"
I also put in a debug.print for your EmailTo so you can look at it and see if the correct string is built. Reading the code I would expect a proper email string of delimited emails.
name.name@something.com; name2asdf@somehting2.net.....

syntax error on the SendEmailOutlook(emailTo,"Test","Test")
Error.PNG
 

mistyinca1970

Member
Local time
Today, 14:23
Joined
Mar 17, 2021
Messages
117
I got it! Basically with a combination of everyone's suggestions MajP, your placement of the emailTo line was the kicker. Thank you so much for your help. I have now created two buttons. One that will put the string of ";" delimited emails in the To box and another that will put the same in the BCC box.
Here is the final code that worked!

Code:
Private Sub btnSendEmail_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Database
 
    Dim emailTo As String
    Dim retVal As String
 
        emailTo = QueryFieldAsSeparatedString("EmailAddress", lstQueryList.Value, , , ";")
 
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
 
    On Error Resume Next
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)
  
    On Error Resume Next
    With outMail
        .To = emailTo
        .Display
     
    End With

End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
My error is the extra comma after
MsgBody as string
 

Users who are viewing this thread

Top Bottom