Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 23rd, 2005, 04:47 AM
Registered User
Join Date: Sep 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mail Merge VBA Query

Hi people,
Hope you can help me with a problem, it's rather a simple one and I apologise for my ignorance.

Ok a problem with defining a select query for a mail merge.
I want to have a pop-up box and then use this value as the query data for a mail merge and have written the following quick macro:

Sub test_mail_merge()
' test_mail_merge Macro
' Macro by JPC
Dim payno As Integer
payno = InputBox("payroll number", "Payroll Selection", 0)

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Documents and Settings\client\Desktop\test merge data.csv WHERE (Payroll = payno)"

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub

IT seems to be using the variable as a literal value, if i replace the "payno" variable in the SELECT query with a real value the merge works fine and only merges the one record, i'm obviously using the defined variable "payno" incorrectly, have tried removing brackets ect but no luck, any ideas.

Old October 14th, 2005, 05:25 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts


You are right that payno is being used as a literal value. You simply need to build up the query in parts. So instead use:

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\Documents and Settings\client\Desktop\test merge data.csv WHERE (Payroll = " & CStr(payno) & ")"

In case you dont know, & joins strings together. You need the CStr function to convert payno to a string, as string & number doesn't make sense.

Hope this helps
Philip Cole

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge in Web aspBegineer83 ASP.NET 2.0 Basics 12 January 4th, 2008 06:00 PM
General Mail Merge Query iaingblack Word VBA 0 March 12th, 2007 05:01 PM
Need Help In Mail Merge raghur Access VBA 0 September 8th, 2005 05:46 AM
Access/Vba mail merge to word help alfonse Access VBA 0 July 28th, 2005 05:11 AM
mail merge from query nabrown78 Access VBA 3 April 4th, 2005 08:23 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.