Hi all,

I have a membership database which has the members email addresses in it.

I want to export the email addresses to a text file with the seperator for outlook or open office.

I want to do this so I can just copy and paste the string into the email program.

I have not been able to figure out how to get the table information into one continious string.

cheers

JD:(

Welcome to the site. You know you can email straight from Access? Lot's of info here:

http://granite.ab.ca/access/email.htm

To build your string, within a loop of the addresses populate a variable:

strAddresses = strAddresses & rs!EmailAddress & ";"

Thanks for the response, I need the files as I email from my linux box at home. Work may not be impressed if I start sending out hundreds of emails.

So I would just code this in a macro and a initiate with a button?

I'd use VBA, and you could start it from a button. Look at CreateTextFile in VBA help for a way to create a text file from the string variable.

I'd use VBA, and you could start it from a button. Look at CreateTextFile in VBA help for a way to create a text file from the string variable.


Sorry to be a pain but its been a very long time since I touched VBA, could you help out with some sample code?

There is sample code for the looping in the link I posted, I already posted how the string would be built within that loop, and there is sample code in VBA help for creating the text file. Why don't you dive in and give it a try, and we'll fix it if you get stuck?

There is sample code for the looping in the link I posted, I already posted how the string would be built within that loop, and there is sample code in VBA help for creating the text file. Why don't you dive in and give it a try, and we'll fix it if you get stuck?


Where is the VBA help located?

I could only find it in excel, is that the one you are talking about?

Open the VBA editor (Alt-F11 from within Access), then Help is in the usual place.

Feeling a bit stupid, work has office 2003 but have access 97 SR2 installed for whatever reason.

ALT F11 gives no response from inside access.

I don't have 97 installed on anything, but I assume if you create a command button, go into its properties on the event tab, you can put the cursor on the click event and then click on the ellipsis (...) to get into the code editor.

This is what I found and changed around to work.


Function Email_address_out_Outlook() As Long
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim mylist As String

Open "C:\salbl_email_address_outlook.txt" For Output As #1
Set rs = DBEngine(0)(0).OpenRecordset("email_outlook")
Do While Not rs.EOF
For Each fld In rs.Fields
mylist = mylist & fld.Value
Next
rs.MoveNext
Loop
Print #1, mylist

rs.Close
Close #1
Set rs = Nothing
End Function

It works however I have only one field so it could be simplified.

Any suggestions?

Change this:

For Each fld In rs.Fields
mylist = mylist & fld.Value
Next

to this

mylist = mylist & rs!EmailAddressField & ";"

Tried with the table and a query but just went into infinite loop.

The field in the table or the query is called "EmailAddress"

Is access 97 somehow different?

Function Email_address_out_Outlook() As Long
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim mylist As String

Open "C:\salbl_email_address_outlook.txt" For Output As #1
Set rs = DBEngine(0)(0).OpenRecordset("sa_lbl_members")

Do While Not rs.EOF

mylist = mylist & rs!EmailAddress & ";"

rs.MoveNext (oops removed this and its needed to work)

Loop
Print #1, mylist

rs.Close
Close #1
Set rs = Nothing
End Function

I did not say to get rid of this:

rs.MoveNext

which is still needed.

DOH!

What can I say but thanks for your patience and help, all happy now.

No problem; glad we got it sorted out.