Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    I am looking for some help in creating a directory mail merge. I have data in spreadsheet in the following format.

    Publisher Books Email
    PubA Title One abc@test.com
    PubA Title Two abc@test.com
    PubA Title Three abc@test.com
    PubB Book One xyx@abc.com
    PubB Book Two xyx@abc.com
    PubC One Volume mnop@abd.net
    PubD Another Text email@newemail.com
    PubE First of Many Books last@last.edu.com
    PubE Second of Many Books last@last.edu.com
    PubE Third of Many Books last@last.edu.com
    PubE Last of Many Books last@last.edu.com

    I have created a directory mail merge in which I have got one letter for each publisher consisting of books list I need to order. i.e. One letter to PubA with all three titles. My letter varies with publisher according to number of books.

    The problem I am facing is how to email these letters to just one email Id using outlook.
    For example, I want to send my directory mail merge to Publisher PubA just once on his email abc@test.com and not three times as shown in my excel spreadsheet.

    I have tried merging with new documents and emailing but it mails all 5 letters together to all the emails present in the spreadsheet.

    I want individual letters to specific recipients only.

    I hope I have explained my problem. Any feedback or help would be highly appreciated. Thanks in advance.

    I have attached the sample file for your reference.
    -
    Aroma
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    Our moderator and Word MVP macropod has written a tutorial on how to perform this kind of mail merge: Word 97-2007 Mailmerge Tutorial: Create Sorted Listings (v1.4).

  3. #3
    New Lounger
    Join Date
    May 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='775830' date='19-May-2009 15:39']Welcome to the Lounge!

    Our moderator and Word MVP macropod has written a tutorial on how to perform this kind of mail merge: Word 97-2007 Mailmerge Tutorial: Create Sorted Listings (v1.4).[/quote]

    Hi Hans,

    Thanks for the reply. I have been through the tutorial. I know how to create directory/catalogue merge. But I want to email these directory mail merge using outlook.

    Kindly advise me if it is possible or not.

    Cheers,
    aroma

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I apologize, I didn't think this through. You can't combine a catalogue / directory mail merge and a mail merge to e-mail - they're mutually exclusive choices.

    Perhaps macropod will come up with a clever workaround, but I think you'll have to resort to using VBA code instead of a mail merge.
    - Open the data source.
    - Loop through the rows.
    - Each time you encounter a new e-mail address, start a new e-mail message.
    - Assemble the body of the e-mail.
    - Send the message.

    You'd need to automate both Excel and Outlook from Word, which requires that you're familiar with Word VBA, Excel VBA and Outlook VBA...

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='HansV' post='775866' date='19-May-2009 04:50']You'd need to automate both Excel and Outlook from Word, which requires that you're familiar with Word VBA, Excel VBA and Outlook VBA.[/quote]
    Perhaps the OP could do this with an Excel Macro instancing Outlook...
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='JohnBF' post='775878' date='19-May-2009 16:03']Perhaps the OP could do this with an Excel Macro instancing Outlook...[/quote]
    Yes, it should be possible to cut out Word entirely.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='aroma658' post='775856' date='19-May-2009 04:35']I want to email these directory mail merge using outlook.[/quote]
    Here's some basic starter code to run in Excel without using Mail Merge. You WILL need to set a reference in the Excel VBE under Tools | References to the "Microsoft Outlook v.00 Object Library".

    [codebox]Sub mailem()
    Dim rngPubList As Range, rngCell As Range
    Dim strMessageBody As String
    Dim appOutlook As Object, msgM As Object

    ' sort to group for each publisher
    ThisWorkbook.Worksheets("Sample Data").Columns("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending

    With ThisWorkbook.Worksheets("Sample Data")
    ' Get publisher data range
    Set rngPubList = Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
    End With
    Set appOutlook = CreateObject("Outlook.Application")

    For Each rngCell In rngPubList
    strMessageBody = strMessageBody & rngCell.Offset(0, 1) & vbCrLf
    If rngCell.Value <> rngCell.Offset(1, 0).Value Then
    ' send the message
    With appOutlook
    Set msgM = .CreateItem(olMailItem)
    With msgM
    .BodyFormat = olFormatHTML
    .Recipients.Add rngCell.Offset(0, 2).Value
    .Subject = "Book order"
    .Body = strMessageBody
    .Save
    ' .Send ' uncomment when the code operates as you wish
    End With
    Set msgM = Nothing
    End With
    strMessageBody = ""
    End If
    Next rngCell

    Set appOutlook = Nothing
    Set rngPubList = Nothing
    End Sub
    [/codebox]
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    May 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John/Hans,

    Thanks for the reply. I am sorry Hans I posted same post in word as well. I was not sure where to put it. I won't repeat it again.

    John I will start working around the codes and see how it goes. I am at a beginner level in VBA so I think I migth take time to write it.

    Thanks,
    Aroma

    [quote name='JohnBF' post='776006' date='20-May-2009 09:11']Here's some basic starter code to run in Excel without using Mail Merge. You WILL need to set a reference in the Excel VBE to the "Microsoft Outlook v.00" library.

    [codebox]Sub mailem()
    Dim rngPubList As Range, rngCell As Range
    Dim strMessageBody As String
    Dim appOutlook As Object, msgM As Object

    ' sort to group for each publisher
    ThisWorkbook.Worksheets("Sample Data").Columns("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending

    With ThisWorkbook.Worksheets("Sample Data")
    ' Get publisher data range
    Set rngPubList = Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
    End With
    Set appOutlook = CreateObject("Outlook.Application")

    For Each rngCell In rngPubList
    strMessageBody = strMessageBody & rngCell.Offset(0, 1) & vbCrLf
    If rngCell.Value <> rngCell.Offset(1, 0).Value Then
    ' send the message
    With appOutlook
    Set msgM = .CreateItem(olMailItem)
    With msgM
    .BodyFormat = olFormatHTML
    .Recipients.Add rngCell.Offset(0, 2).Value
    .Subject = "Book order"
    .Body = strMessageBody
    .Save
    ' .Send ' uncomment when the code operates as you wish
    End With
    Set msgM = Nothing
    End With
    strMessageBody = ""
    End If
    Next rngCell

    appOutlook.Quit
    Set appOutlook = Nothing
    Set rngPubList = Nothing
    End Sub
    [/codebox][/quote]

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='aroma658' post='776007' date='19-May-2009 17:17']I will start working around the codes and see how it goes. I am at a beginner level in VBA so I think I might take time to write it.[/quote]
    We'll do what we can to help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='aroma658' post='775856' date='19-May-2009 20:35']Hi Hans,

    Thanks for the reply. I have been through the tutorial. I know how to create directory/catalogue merge. But I want to email these directory mail merge using outlook.

    Kindly advise me if it is possible or not.

    Cheers,
    aroma[/quote]
    Hi Aroma,

    Attached is a zip file showing how you can use the catalog/directory merge process in conjunction with merging to email. There's a brief 'tutorial' file along with a mailmerge main document and an Excel data source based on the ones from my Word 97-2007 Mailmerge Tutorial.

    I note that Hans locked your post on this topic in the Word forum, where I'd suggested raising this issue. I wasn't aware at the time there was already an ongoing discussion in this forum.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    loxd (2014-10-30)

Posting Permissions

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