VBA Mail Merge

[Neilster]

Hi All

I want a click button that will start a mail merge in Access, so far I've tinkered around with some code over the past week and when running the below code which just opens the Word doc but does nothing else.

Any help would be lovely

Dim MailMergeFile As String
Dim objWord As Word.Document
Dim i As Integer

Set objWord = GetObject("C:\Users\CustomerDB\Desktop\mymerge.docx", "Word.Document")

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:="C:\Users\User\Desktop\CustomerDB.accdb", _
LinkToSource:=True, _
Connection:="TABLE TblCustomerDetails", _
SQLStatement:="SELECT * FROM [TblCustomerDetails]"

objWord.MailMerge.Execute


For i = 1 To objWord.Application.Documents.Count - 1
objWord.Application.Documents(i).Close wdSaveNo
Next i

:banghead::banghead::banghead:

 

[RuralGuy]

I would just use the Super Easy Word Merge the Albert Kallal has posted on his site:
http://www.kallal.ca/msaccess/msaccess.html

 

[Neilster]

Thanks for getting back to me, looks very intersting.

 

[RuralGuy]

It is just one line of code in your current project and it works great.

 

[Neilster]

Not sure i understand what you mean?

The code above just opens Word but doesn't start a mail merge.

And the same with the link you gave me, I down loaded the DB and it just opens Word and not mail merge.

 

[GaryPanic]

read the instructions - you have to add the fields in as normal

 

[GaryPanic]

are you wanting a mail merge of x records or individual record

super easy word merge is ideal for 1 record merging into word
create the template and then push the button works a treat
ensure you d/b is split

 

[Neilster]

I'd like the option to mail merge one or lots of records.

 

[Neilster]

Also I've followed theeasy word merge and I just get compile errors throughout the code. :banghead::banghead:

 

[RuralGuy]

Did you import the two modules and the two forms into your db?

 

[gemma-the-husky]

this any use?

    Set objWordDoc = objWord.documents.Open(MergeDocument)
 
    objWordDoc.MailMerge.OpenDataSource _
        [COLOR=red]Name:=MergeData[/COLOR], ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=0, _
        Connection:="", SQLStatement:="", SQLStatement1:=""
     objWordDoc.MailMerge.Destination = 0 'wdSendToNewDocument
    
    objWordDoc.MailMerge.Execute

in this case the mergedata is a csv
maybe the way you are specifying your record source is not correct.

 

[Neilster]

Hi guys

Thanks for all your help! it looks like I may have cracked it with easy mail merge. Will keep you posted if any different.

 

[Neilster]

My only issue with Super Easy Word Merge by Albert D. Kallal is that when you filter your databasefor certain criteria like say a specific city and then you do the mail merge it still merges the entire database????

 

[RuralGuy]

How are you invoking the WordMerge. You hand it an SQL statement, right?

 

[Neilster]

Yes an SQL

 

[RuralGuy]

Does that SQL have a where clause or some sort of limiting clause in it?

 

[Neilster]

It runs from this

Set rstOutput = CurrentDb.OpenRecordset(strSql, , dbSeeChanges)

 

[RuralGuy]

Sorry about the delay but site did not tell me of your post. Where is your line of code that invokes the MailMerge? Please post it for me to see.