Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Combining cells in rows with the same ID into one cell for email mail merge

    Hi

    I have a mailmerge data file ready for an email mailmerge but am struggling to get multiple rows with the same ID into the same email. Each recipient may have multiple items that needs to be listed on the same email. From what i can see online you cant use a pivot table to do this, which is a huge bummer, as thath would resolve the problem, so the other option is catalogue/directory, but that doesnt quite cut it. So i need to get each recipient's items merged into one cell.

    So, for example of the data i have is :
    img1.JPG

    And what the excel data file to look like in the end is:
    img2.JPG

    Is there an easy way to achieve this in excel or Access, so that i dont have to go down the route of using directory/catalogues and then additional VBA scripts to produce the load file?

    your help is massively appreciated!

    Thanks!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    You can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
    http://windowssecrets.com/forums/sho...merge-Tutorial
    The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

    For some worked examples, see the attachments to the posts at:
    http://www.msofficeforums.com/mail-m...html#post23345
    http://www.msofficeforums.com/mail-m...html#post30327
    Last edited by macropod; 2013-11-25 at 13:10. Reason: Updated links
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Apr 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks, Macropod, I've already downloaded your tutorial yesterday and read through it, and I know it will work. However, as you've also mentioned at the bottom of your tutorial, I think there would be a simpler way of achieving what i need, by manipulating the data in Access or Excel with a macro or VBA, before even getting to the mail merge part and instead of having to use multiple mail merge documents etc.

    Does anyone know how?

    Thanks

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Henk,

    The process envisaged in the tutorial wouldn't involve any changes to your data source; rather you'd read the data in from the unmodified data source and do the processing in memory (or perhaps on a temporary worksheet). In such a scenario, you wouldn't be using Word & mailmerge at all - everything would be done in Excel.

    With the process outlined in the tutorial, the macro takes care of all the processing once the basic documents are set up - you don't have to do any of the merging yourself.

    What is seems to me you want to do is to shift some of the processing to Excel, but use a standard email merge at the end. That's not particularly difficult, but I don't know if anyone has a macro you could simply run with your data; each user's requirements are different and, in your case, those requirements appear to entail discarding repeated data from four columns and merging data from at least four more. The basic code for that could be:
    Code:
    Sub MailmergePrepare()
    Application.ScreenUpdating = False
    Dim xlWkShtIn As Worksheet, xlWkShtOut As Worksheet
    Dim LRow As Long, LCol As Long, StrNm As String
    Dim I As Long, J As Long, K As Long
    StrNm = "MergeSheet"
    On Error GoTo Abort
    Set xlWkShtIn = ActiveSheet
    If SheetExists(StrNm) Then
      With Sheets(StrNm)
        .Range(.UsedRange.Address).Clear
      End With
      Set xlWkShtOut = Sheets(StrNm)
    Else
      Set xlWkShtOut = Worksheets.Add
      xlWkShtOut.Name = StrNm
    End If
    With xlWkShtIn
      LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
      LCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
      For I = 1 To 2
        Application.StatusBar = "Processing Row " & I & " of " & LRow
        For J = 1 To LCol
          xlWkShtOut.Cells(I, J).Value = .Cells(I, J).Value
        Next J
      Next I
      K = 2
      For I = 3 To LRow
        Application.StatusBar = "Processing Row " & I & " of " & LRow
        If .Cells(I, 4).Value <> xlWkShtOut.Cells(K, 4).Value Then
          K = K + 1
          For J = 1 To LCol
            xlWkShtOut.Cells(K, J).Value = .Cells(I, J).Value
          Next J
        Else
          For J = 5 To LCol
            xlWkShtOut.Cells(K, J).Value = xlWkShtOut.Cells(K, J).Value & Chr(10) & .Cells(I, J).Value
          Next J
        End If
      Next I
    End With
    GoTo Terminate
    Abort:
    MsgBox "Processing Error On Line " & I, vbCritical
    Terminate:
    Application.ScreenUpdating = True
    End Sub
     '
    Function SheetExists(SheetName As String) As Boolean
    ' returns TRUE if the sheet exists in the active workbook
      SheetExists = False
      On Error GoTo NoSuchSheet
      If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
      End If
    NoSuchSheet:
    End Function
    Note: the above code assumes the data source is the active sheets and sends its output to a worksheet named 'MergeSheet'. I'd suggest changing 'Set xlWkShtIn = ActiveSheet' to use the input worksheet's name.
    Last edited by macropod; 2013-04-28 at 17:06.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. The Following User Says Thank You to macropod For This Useful Post:

    Henkdafreak (2013-04-28)

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Henkdafreak

    ..see attached workbook.

    Hope this gives you exactly what you wanted.

    My processing assumes you want to merge all items placed under a specific Order Ref.
    So it will assume that the date, cust-num, name and email will be the same if the Order Ref is the same.

    Please let me know if you need any further explanations.

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Radooza (2014-05-29)

  8. #6
    New Lounger
    Join Date
    Apr 2013
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Macropod, you ROCKSTAR!

    Thats perfect, works like a beaut!

  9. #7
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,568
    Thanks
    5
    Thanked 1,056 Times in 925 Posts
    The seven posts formerly in the thread starting with a question from user abbynburns have been moved to http://windowssecrets.com/forums/sho...l=1#post930884 in the Word forum.

    Joe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •