exporting froma query to a single row .txt file

[Graham26]

Hi, I have a parameter query in ACCESS 2013 that produces rows of email addresses for a specified class of students, that I want to export/ convert to a text file on a single row, separated by “; “, to transfer to the BCC field of an email for a mailshot. To clarify this is (hopefully) an ACCESS to OUTLOOK exercise.
I am new to VBA and have seen posts re DOCmd.TransferText and Specification, and have just enough awareness of VBA to realise I really don’t have any knowledge at all… :banghead:
I have tried exporting to a text file, but the parameter type query will not work and I would rather not go down the route of individual queries for each class of students.
Any help and support will be gratefully received.

 

[Minty]

Have a look here ;
http://allenbrowne.com/func-concat.html

 

[arnelgp]

you can have an inputbox to collect users' info and passed it as parameter in your parameter query.
set a reference to Microsoft Scripting Runtime

Public Sub ParamQryToTextFile()

    Dim objFSO As Scripting.FileSystemObject
    Dim objText As Scripting.TextStream
    Dim strContent As String
    Dim varParameter As Variant
    Dim qdf As QueryDef
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' obtain parameter from user
    varParameter = InputBox("Enter parameter:", "Parameter")
    
    ' check if user inputted something
    If Trim("" & varParameter) <> "" Then
    
        '// note you must convert to the correct datatype your parameter here
        '// ie: varParameter = CInt(varParameter)
        '//
        '//
        
        ' create an instance of our db
        Set db = CurrentDb
        
        ' open our parameter query here
        Set qdf = db.QueryDefs("theNAMEofyourParameterQueryHere")
        
        ' set the parameter value
        qdf.Parameters("theNameOfParameterHere").Value = varParameter
        
        ' open recordset from our query
        Set rs = qdf.OpenRecordset(dbOpenDynaset)
        
        With rs
            'check if there is record
            If Not (.BOF And .EOF) Then .MoveFirst
            
            ' recursed through all records
            While Not .EOF
                If Trim("" & .Fields("theNameOfFieldHoldingEmail").Value) <> "" Then
                    strContent = strContent & .Fields("theNameOfFieldHoldingEmail").Value & ";"
                End If
                .MoveNext
            Wend
            
        End With
        
        rs.Close
        qdf.Close
        
        Set rs = Nothing
        Set qdf = Nothing
        
        Set db = Nothing
        
    End If
    
    ' check if there is email address in our var
    If Len(strContent) > 0 Then
        ' there is copy to text file
        
        ' create an instance of filesystem object
        Set objFSO = New Scripting.FileSystemObject
        
        ' create text file. overwrite if exists
        ' you must provide the name of the text file
        ' on my example its Email.txt and saved on where this db is
        Set objText = objFSO.CreateTextFile(CurrentProject.Path & "\Email.txt", True)
        
        With objText
            .Write strContent
            .Close
        End With
        
        Set objFSO = Nothing
        
    End If
End Sub