none
Mail Merge using two different recipient list RRS feed

  • Question

  • Hi All

    Are there any posibility to access two different recipient list( Two Excel Sheets. i.e. one recipient list in excel sheet 1, another recipient list in excel sheet 2) in one mail merge process?

    Thank You

     

    Sunday, December 25, 2011 9:36 AM

Answers

  • No, you will have to create a sheet that combines the two lists into one, and use that as the data source.
    Regards, Hans Vogelaar
    • Marked as answer by dukdpk Monday, December 26, 2011 6:20 PM
    Sunday, December 25, 2011 10:49 AM

All replies

  • No, you will have to create a sheet that combines the two lists into one, and use that as the data source.
    Regards, Hans Vogelaar
    • Marked as answer by dukdpk Monday, December 26, 2011 6:20 PM
    Sunday, December 25, 2011 10:49 AM
  • Further to what Hans said, you can differentiate the lwo lists on the one sheet by having a 'recipients' column and using either Word's mailmerge filters or a SKIPIF field in the mailmerge main document to tell Word which set of records to output.
    Cheers
    Paul Edstein
    [MS MVP - Word]
    Sunday, December 25, 2011 9:26 PM
  • It can sometimes be done from VBA using a UNION query - in the simpler case where the two sheets have identical columns, you may be able to do it using the following:

    But you do get a UNION, with the potential for duplicate rows to be removed.

     

    Dim strDS As String

    ' put your Workbook's fullname here

    strDS = "C:\xlwb\k2sheets.xlsx"

    ActiveDocument.MailMerge.OpenDataSource _

      Name:=strDS, _

      SQLStatement:= _

      " SELECT s1.* FROM `Sheet1$` s1" & _

      " UNION" & _

      " SELECT s2.* FROM `Sheet2$` s2"

    Otherwise, you have to have the space in the SQL query to spell out the individual field names  - e.g. in the sample I was using, I could do the following:
    Dim strDS As String

    ' put your Workbook's fullname here

    strDS = "C:\xlwb\k2sheets.xlsx"
    ActiveDocument.MailMerge.OpenDataSource _
      Name:=strDS, _
      SQLStatement:= _
      " SELECT s1.k, s1.t, s1.n FROM `Sheet1$` s1" & _
      " UNION" & _
      " SELECT s2.k2, s2.t2, s2.n2 FROM `Sheet2$` s2"
    I'm pretty sure you'll need to use the table alias names as I have done. There may also be the usual difficulties associated with mixed data types in Excel columns. 

     


    Peter Jamieson
    Monday, December 26, 2011 11:36 PM