Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    NOTE: This topic has been split off from Word 97-2007 Mailmerge Tutorial: Create Sorted Listings (v1.52)
    HansV


    Hi Macropod.

    Firstly I would like to thank you for taking the time to put together the tutorial. It has certainly got me well on the way to achieving what I was aiming for.

    I am still encountering a slight (I hope!) problem though. My aim is to create a merge to outlook listing a customers invoices, which I managed using your "Merging by Catalog/Directory to E-Mail" tutorial. However, I would also like to include a total for the invoices which is where I am having an issue. I have managed this for a letter format using other sections of the tutorial but those documents cannot then be used as the source to merge into an e-mail format.

    Any help would be gratefully received.

    Many thanks!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='monkeyeb' post='784883' date='16-Jul-2009 20:25']Hi Macropod.

    Firstly I would like to thank you for taking the time to put together the tutorial. It has certainly got me well on the way to achieving what I was aiming for.

    I am still encountering a slight (I hope!) problem though. My aim is to create a merge to outlook listing a customers invoices, which I managed using your "Merging by Catalog/Directory to E-Mail" tutorial. However, I would also like to include a total for the invoices which is where I am having an issue. I have managed this for a letter format using other sections of the tutorial but those documents cannot then be used as the source to merge into an e-mail format.

    Any help would be gratefully received.

    Many thanks![/quote]
    Hi monkeyeb,

    Welcome to the Lounge.

    The TableSummarize macro on page 18 of the tutorial shows how you can programmatically add a 'totals' row to a table. You could use something along those lines to add the totals to your data. To use the TableSummarize macro in conjunction with the merge to email process, add that code to your code module, then insert the line 'Call TableSummarize' just before the 'End Sub' line in the 'TableJoiner' macro.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for getting back to me so quickly Paul.

    I've been working on this for a couple of days now but must admit that this is not my area of expertise and I'm getting nowhere fast!

    What I am hoping to create is a merge to 1 e-mail for each group which, along with some text ,will include the follow format table outlined in boxes (TAS as this example):

    Hobart Summerrell $2,200
    Launceston Maher $9,900
    Total: $12,100


    At the moment I am using the "Merging by Catalog/Directory to E-Mail" code from page 24 of your tutorial to create the table. I think I have setup the macros to then create the correct table to create the merge to e-mail but it is not working. I have attached a copy of where I have reached in the process, would you be kind enough to check that I have the macro to setup the table done correctly or have made any other obvious error?

    Thanks again,
    Chris
    Attached Files Attached Files

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

    The solution turns out to be a little more complicated than I originally anticipated. Sorry 'bout that. For what you want, replace the 'TableJoiner' sub with:
    Code:
    Sub TableJoiner()
    Dim oTbl As Table
    For Each oTbl In ActiveDocument.Tables
      With oTbl.Range.Next
    		If .Information(wdWithInTable) = False Then .Delete
      End With
    Next
    Call TableSummarize
    ActiveDocument.Fields.Unlink
    End Sub
    Now, when you run the 'EmailMergeTableMaker' sub, everything should work OK.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for working out that fix. I thought we (ok, mostly you!) had cracked it as it was working perfectly. Unfortunatly I have now encountered a problem when I try to use this on my data rather than yours. Although the total remains, it is not picking anything up so remains at $0.

    I have attached the data source I created (testdata) along with the word document I am creating the first merge on (Mainpage) prior to then merging to e-mail. Would you mind taking a look to see if you can change where I am going wrong?

    Thanks once again!

    Chris
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='monkeyeb' post='786508' date='28-Jul-2009 20:57']Thanks for working out that fix. I thought we (ok, mostly you!) had cracked it as it was working perfectly. Unfortunatly I have now encountered a problem when I try to use this on my data rather than yours. Although the total remains, it is not picking anything up so remains at $0.

    I have attached the data source I created (testdata) along with the word document I am creating the first merge on (Mainpage) prior to then merging to e-mail. Would you mind taking a look to see if you can change where I am going wrong?

    Thanks once again!

    Chris[/quote]
    Hi Chris,

    The mailmerge approach was based on the assumption that there'd only be one string of digits in the 'Data' column, whereas you've got 6 - the numbers in the invoise, the day,month & year and, finally, the amount. Consequently the macro had no idea what to sum. Attached is a revised copy of your document, with a modified field construction and a new version of the macros to work with your data.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have used the revised item you provided in the last post but when I try to run the EmailMergeTableMaker with the amended Tablejoiner sub you gave me in your post dated 25/07 it creates a total for each line.

    Another issue is that it won't create one field (e.g. Data) which can then be selected for the merge to e-mail because the table has vertically merged cells and brings up runtime error 5991. If I select "end" rather than "debug" I end up with the attached.

    Sorry to keep coming to you with problem after problem!!
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I forgot to add that whilst I can resolve the issue with the vertically merged cells by removing the seperate boxes for "invoice", "due date" and "value" and can therefore get all that info back under one field titled "Data", it then goes back to the same issue of not totalling correct which undoes what you done previously.

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

    In my last post, I said:
    Attached is a revised copy of your document, with a modified field construction and a new version of the macros to work with your data.
    with the expectation that you'd use the macros in that document. All you need to do is to run the mailmerge, then the 'EmailMergeTableMaker' macro that's in the 'MainPage' document I returned to you. I've re-tested that macro and confirmed that it works with the data file you supplied.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul.

    My apologies, I didn't read your message properly so didn't pick up that you have included the amended macro!

    I have completed the following steps:

    • Opened the document you attached and attached the data source "testdata".
    • Run the merge (I have attached a copy of what is produced titled 1stMerge)
    • On that document I then run the new macro "Module1.EmailMergeTableMaker" (if I try to run NewMacros.EmailMergeTableMaker then I get the runtime error)


    I have attached a copy of what is then produced after completing the steps above (titled Recipients). It has seperated the info so it can be merged to e-mail so that is one problem overcome now. However, the issue that I am still struggling with is the totalling. At present it is giving a total for each invoice rather than a total of all the invoices for each recipient. Is this something that can be overcome?

    I really am grateful for your help with this an I promise I will leave you alone eventually!!

    Many thanks,
    Chris
    Attached Files Attached Files

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [Hi Chris,

    The Section breaks in your '1stMerge' file show that it wasn't created using a Directory or Catalog mailmerge.

    As for the 'Module1' vs 'NewMacros' issue, I suggest deleting anything in 'NewMacros' that is copied from this thread or the tutorial itself. That way there'll be no cause for confusion.

    I'm attaching another update to your 'MainPage' document. This time, after making sure it's connected to your 'TestData' file for a Directory or Catalog merge (which it should do by default), don't execute the merge or run the 'EmailMergeTableMaker' macro. Instead, simply run the 'RunMerge' macro.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #12
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Paul, that is superb! It's even been idiot-proofed for me!

    I have one final thing that I would like to see if it is possible to do..... Can the values for the invoices and the value of the total be shown to 2 decimal places? When I have amended the invoice values in the source data it will show the 2 decimal places in the merge unless it finishes with a 0. e.g 100.11 shows as 100.11 but 100.10 shows as 100.1 and 100.00 shows as 100. And I have no idea how to change the total figure to show the decimal places, I have now learnt that I am not qualified to touch the macros and it will only end in their destruction!!

    I have also tried changing the testdata completely (as this will be necessary going forward in any case) and it added the invoice values in the merge to 14 decimal places for some reason? A copy of the new changed data source and merge result is attached.

    Would you be able to take a look and see if you could amend the file you attached previously to rectify this? After that I'll leave you alone I promise!!

    Thanks,
    Chris
    Attached Files Attached Files

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='monkeyeb' post='786837' date='31-Jul-2009 01:42']Thanks Paul, that is superb! It's even been idiot-proofed for me!

    I have one final thing that I would like to see if it is possible to do..... Can the values for the invoices and the value of the total be shown to 2 decimal places? When I have amended the invoice values in the source data it will show the 2 decimal places in the merge unless it finishes with a 0. e.g 100.11 shows as 100.11 but 100.10 shows as 100.1 and 100.00 shows as 100. And I have no idea how to change the total figure to show the decimal places, I have now learnt that I am not qualified to touch the macros and it will only end in their destruction!!

    I have also tried changing the testdata completely (as this will be necessary going forward in any case) and it added the invoice values in the merge to 14 decimal places for some reason? A copy of the new changed data source and merge result is attached.

    Would you be able to take a look and see if you could amend the file you attached previously to rectify this? After that I'll leave you alone I promise!!

    Thanks,
    Chris[/quote]
    Hi Chris,

    If you toggle the field code view (Alt-F9), you'll see a field coded as {MERGEFIELD Value}. To control the decimal formatting, you need to add a numeric picture switch to this field, thus:
    . edit the field so that you get {MERGEFIELD Value \# ,0.00} (or whatever other numeric format you prefer - see below);
    . position the cursor anywhere in this field and press F9 to update it.
    The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

    In the 'TableSummarize' macro is the line:
    .Fields.Add FldPos, Text:="=SUM(ABOVE) \# ""'Total: '$,0""", PreserveFormatting:=False
    Change this to:
    .Fields.Add FldPos, Text:="=SUM(ABOVE) \# ""'Total: ',0.00""", PreserveFormatting:=False

    That's all there is to it.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    New Lounger
    Join Date
    Jul 2009
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Paul,

    I made those amendments to the Macro and it cleared up the issue so thanks very much.

    Now I am able to start running this I have pick up a couple of problems that I didn't notice before:

    1. As well as invoices, I am also including credit notes which therefore have a negative value. This is picked up and totalled fine on the source date spreadsheet but the total created when the merge is run is having an issue with this. I have attached a copy of a merge and highlighted a couple of examples.

    2. Is it possible to change the date format from the current style to DD/MM/YYYY?

    3. Is it possible to amend the index.php and merge macro so that a further column is created on the merged data giving the account number so that can then be used as a further field in the body of the text when merging to e-mail? I hope that was clearly explained but have attached an example of what I'm waffling on about.

    Thanks,
    Chris
    Attached Files Attached Files

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

    I am unable to replicate your problem with the values not summing correctly. You could try adding the line:
    .Fields.Update
    before the line:
    .Fields.Unlink
    in the 'TableSummarize' sub.

    Alternatively, since it seems your data source has the group totals, you could modify the mergefield coding to insert that field in a table row on its own whenever there's a group change, and not use the 'TableSummarize' sub.

    As for the dates, that's a simple matter of editing the 'Due_Date' mergefield so that it reads 'MERGEFIELD Due_Date \@ "DD/MM/YYYY"'.

    I don't know what you mean by your reference to 'index.php' - there's nothing I can see like that in anything to do with the merge process. If you want an extra column for a single account record per group then, yes, that can be added between the email address and grouped data as per your sample documents. To do that, you'd need to add an extra column to each of the tables and modify the 'EmailMergeTableMaker' macro so that it handles the extra column correctly.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

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