I have created a mail merge from a query in Access and it works fine if you run it from word but I would like to know how to automate the mail merge from Access using the Command button. Please can someone advise.
You might create a button on a form with something like this in the on-click event procedure :
Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strBody As String
Dim strSubject As String
Dim strContactName As String
Set rsEmail = CurrentDb.OpenRecordset("YourQuery")
Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
strBody = rsEmail.Fields("Body").Value
strContactName = rsEmail.Fields("ContactName").Value
strSubject = "blah blah blah"
I'm somewhat of a rookie where Coding is concerned but I can understand basic code (having learned Pascal about 15 years ago but never touched it since).
Is there no way to make the command button just initiate the Merge into word as the word document is all set up and is linked to the query I want the records to come from I just want a button that runs the merge, then I can check it's picked up all the info and then mail it manually using the mailto option on the file menu.
I'll explain what it is that I'm after as I don't think I was very clear originally. What I want to do is to click a button and create a document laid out in a specific way with all the information already filled in I will then e-mail it (or Access can e-mail it).
What I tried to do initially was create a report which had all the formatting and then e-mail that to the receipient using a macro, unfortunatly the only options to send the report in are text, rich text, excel and HTML which doesn't save any of the formatting in the report and the person at the other end just gets a load of text, as I'm sending out to 3rd party it has to have the logo etc which get stripped out with these formats. To get round this I'm trying to merge the record I want with a mail template which works if I go into word, open the template and click on the merge button but I would like to just click a button on the form in Access which opens the word template and populates the database fields then I can e-mail it. Is there an easy way to do this or am I WAY out of my depth?
Alex - You're in luck because I recently had to go through figuring out exactly what you're trying to do. Not a lot of information out there regarding vba mail merge, either. Here's what to do:
**Make sure you set wordDoc to nothing at the end, or your computer will start behaving badly**
Code:
Function main(filter As String)
Dim wordDoc As New Word.Application
Set wordDoc = CreateObject("Word.Application")
Dim templatePath As String, sourceQuery As String
Dim i As Integer
Dim x As Integer
templatePath = "C:\[filepath]" 'your template here
sourceQuery = "[queryname]" 'name of the query in your database
wordDoc.Visible = True 'set connection as visible
wordDoc.Documents.Open templatePath 'open template
wordDoc.ActiveDocument.MailMerge.OpenDataSource Name:= _
"[path to your database]" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\yourdatabase.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database P" _
, SQLStatement:="SELECT * FROM `" & sourceQuery & "`", SQLStatement1:="" 'link to data source
wordDoc.ActiveDocument.MailMerge.SuppressBlankLines = True
x = wordDoc.ActiveDocument.MailMerge.DataSource.RecordCount
If x > 0 Then
For i = 1 To x 'merge+print each record individually
wordDoc.ActiveDocument.MailMerge.DataSource.FirstRecord = i
wordDoc.ActiveDocument.MailMerge.DataSource.LastRecord = i
wordDoc.ActiveDocument.MailMerge.Execute Pause:=False
wordDoc.PrintOut Background:=True
wordDoc.ActiveDocument.Close Savechanges:=False
Next i
wordDoc.Documents.Close Savechanges:=False
wordDoc.Application.Quit 'exit Word
Set wordDoc = Nothing
End If
End Function
This is a truncated version of one I wrote in access that used 10 buttons on a form to each call the main() function with a unique argument. Then a SELECT CASE series with a different templatePath and sourceQuery for each CASE. It sounds like you just have one template though.
Let me know if I removed something by accident that keeps it from working, but if you just fill in the paths for your database and template, and the query name, it should work the way it is.
Alex - You're in luck because I recently had to go through figuring out exactly what you're trying to do. Not a lot of information out there regarding vba mail merge, either. Here's what to do:
**Make sure you set wordDoc to nothing at the end, or your computer will start behaving badly**
Code:
Function main(filter As String)
Dim wordDoc As New Word.Application
Set wordDoc = CreateObject("Word.Application")
Dim templatePath As String, sourceQuery As String
Dim i As Integer
Dim x As Integer
templatePath = "C:\[filepath]" 'your template here
sourceQuery = "[queryname]" 'name of the query in your database
wordDoc.Visible = True 'set connection as visible
wordDoc.Documents.Open templatePath 'open template
wordDoc.ActiveDocument.MailMerge.OpenDataSource Name:= _
"[path to your database]" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\yourdatabase.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database P" _
, SQLStatement:="SELECT * FROM `" & sourceQuery & "`", SQLStatement1:="" 'link to data source
wordDoc.ActiveDocument.MailMerge.SuppressBlankLines = True
x = wordDoc.ActiveDocument.MailMerge.DataSource.RecordCount
If x > 0 Then
For i = 1 To x 'merge+print each record individually
wordDoc.ActiveDocument.MailMerge.DataSource.FirstRecord = i
wordDoc.ActiveDocument.MailMerge.DataSource.LastRecord = i
wordDoc.ActiveDocument.MailMerge.Execute Pause:=False
wordDoc.PrintOut Background:=True
wordDoc.ActiveDocument.Close Savechanges:=False
Next i
wordDoc.Documents.Close Savechanges:=False
wordDoc.Application.Quit 'exit Word
Set wordDoc = Nothing
End If
End Function
This is a truncated version of one I wrote in access that used 10 buttons on a form to each call the main() function with a unique argument. Then a SELECT CASE series with a different templatePath and sourceQuery for each CASE. It sounds like you just have one template though.
Let me know if I removed something by accident that keeps it from working, but if you just fill in the paths for your database and template, and the query name, it should work the way it is.
