Exporting field value to a word document

[szymek_d]

Hi.

I'd like to export only email addresses separated by ";" to a word document based on a result of a query.
Query gets build dynamically in VB behind the form as there are many search criterias. so user can eneter one field or many in the form to build the query. Queries work and i can extract and display the email in a report or another form, but now i'd like to export it to a word document or outlook (but i would prefer word).

Can anyone please give me advice how i should approch this issue.

Thank you.

 

[BAzZ]

Create a Word Template, with bookmarks in it (Insert->Bookmarks). The bookmarks are the place holders for the data from the database. e.g. If you want to insert someones first name create a bookmark called "FirstName" this will then get "txtFirstName" from the database.
Save the template in the same location as the database.

Create a new Module and a new Sub with code similar to this:

Sub ViewWithWord([I]frmName [/I] As Form_[I]frmName[/I])

    Dim objWord As Word.Application
    Dim rst As Recordset
    Dim strSQL As String
    
    ' Launch Word and load the invoice template
    Set objWord = New Word.Application
    objWord.Documents.Add _
     Application.CurrentProject.Path & "\[I]WordTemplate[/I].dot"
    objWord.Visible = True
    
    ' Add header information using predefined bookmarks
    With objWord.ActiveDocument.Bookmarks
        .Item("("[I]Bookmark2[/I]").Range.Text = [I]frmName[/I].[I]fieldToInsert1[/I]
        '.Item("[I]Bookmark2[/I]").Range.Text = [I]frmName[/I].[I]fieldToInsert2[/I]
       
    End With
End Sub

On your form have a command button that calls the above sub, then hopefully a new word document will open with your data inserted.

Hope this helps.

Cheers

 

[szymek_d]

Thank you. I will attempt to give it a try first thing in the morning.

But just a quick question.

how come when i declare object

dim objWord as Word.Application

it doen't work. Am i missing some libraries?
The error i get is "user-defined type not defined"

 

[BAzZ]

yep your right! in the VBA window, you need to add reference to the "Microsoft Word xx.x Object Library" (xx.x = Version of Word i.e. 10.0)

(Tools->References...)

Hope it helps.

 

[szymek_d]

fixed the referencing problem.. thank you.

now i figured out a quick way to output query result to a text file:

DoCmd.OutputTo acOutputQuery, "zzztemp", acFormatTXT

but the result is displayed:

----------------------
| E-mail |
----------------------
| dan@sadfs.com |
----------------------
| stuff@sdljsdlkf.pl |
----------------------
| fruf@molson.ca |
----------------------

is there a way to re-formate the output so the emails are ";" separated?

 

[gpurger]

hi,
I would think one way of sorting by ';' is to record a macro in word and either use it in word or copy into access and adjust the code to match.
I would create a new word document with button to do this. It is reasonably easy.

Gordon

 

[szymek_d]

actually i was just thinking.

Would it be possible to put query result in a memo box separated by ";"? that would be actually the easiest for me when i'm using this db. This way i'll be able to cut and paste from the memo box.
Would this be possible?

Maybe i'll clarify a bit more.
The query return say 20 email addresses and that's all. so there are 20 rows. So now is there a way to display it as one expression (one field) just separated by ";".

I really appreciate your help and patience..

 

[godofhell]

Szymek_d, I have attached a sample database I created with the code needed to export your email addresses to a text box. You will notice that Used a list box to list all the addresses I want to export then use the text box to produce the final string. I created a For Next statement so that I can count the items on the list box, then I write the value for the email colum to a text box, then increment the value of i until it reaches the count number obtained by the count of the list items. This is tested code.

Private Sub cmdAddresses_Click()
'Counts the amount of addresses in the listEmails list box and sets the loop start
For i = 0 To listEmails.ListCount
'Writes the results to a textbox called txtResults, using the value in Colum 0
'of the list box. My list box only contains 1 colum called emailaddress
'The loop uses (0, i - 1) so that it goes to the end of the value obtained by the count
'and removes 1 loop from happening. This prevents the extra ; from being written
'Results without the (0, i - 1) show as test6@test.com;; (notice the end ;
'Resultst with the (0, i -1) test6@test.com;
txtResults.Value = txtResults & listEmails.Column(0, i - 1) & ";"
Next i

End Sub

 

[szymek_d]

godofhell; Thank you sooo much!!! This is perfect. It works for me. Now i'm just thinking; is there a way to skip exporting to listbox first. What i have is a query that gets build dynamically containing all the email addresses that meet my search criteria. Then i have a button "Print" which by pressing, will display all those emails from a query in a memo box. So i guess what i'm asking is: is it possible to loop through query in vba and append value of each record (as query contains only email addresses) to a memo box.

Thank you once again!