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

    Angry Need help with mail merge via email with multiple rows

    I've spent the last 8 hours trying everything!!! I've tried coding it, I've tried the directory tutorials here. I can do mail merge, it works, but I can't have it send out different things 5 times in a separate email each time.

    I've tried the GMayor add in and it says it's incompatible with 2010 Word
    I've tried Doug Robertson's add in and I get Value errors (I've emailed him for support, no joy)

    Very simple what I want to do, I just want to take a spreadsheet with multiple rows of the same name, but different data in the other columns. It's schedules and times and location. I want to email these people their schedule, but consolidated with ALL their schedules per name.

    I'm usually very proficient with this kind of thing, and unfortunately the database system will only spit the information out as is.

    Brief back story, I'm the coordinator for the International Children's Games in Canada this August, I need to email 1300 volunteers their schedules so they know where to show up and work for the kid's sporting events around the city.

    It's on behalf of the United Way, and all volunteer, so I don't have time to struggle anymore, but I know some of you on this site are super smart and hopefully can help me out and lead me in the right direction.

    If someone could support me, hell, I'm man enough to admit when something is over my head, I'll beg, if someone could do it for me, 1600 kids from all over the world would be forever grateful!

    I've attached the files I was working with...

    Mailmergetestschedules.xlsx

    mailmerge.docx

    I've literally spent the past 2 days trying to figure this out. The xlsx file attached is only a sample, but the actual list would be about 8000 rows, so it's WAY to much work to do it manually!

    I know it's asking a lot, but I appreciate any assistance that anyone can provide!

    THANKS!
    Last edited by MachX; 2013-08-02 at 16:15.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MachX,

    Welcome to the Lounge as a new poster!

    Do you by any chance have Microsoft Access? If so which version?
    This would make it much easier to do.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do, and it's 2010. I thought about this, and knew it was a viable option, but that would mean dusting off my old university database skills which I have no time for.

    What do you suggest I do with Access and how?

    Thanks for the warm welcome!

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MachX,

    1. Open a New (empty) Database file.
    2. Link to your Excel workbook/worksheet.
    LinkExcelAsTable.JPG
    3. Queries {Table Creation} to extract the data into 2 tables (Person Table {Name, Email, UniqueID}; Assignment Table {UniqueID (not the same one), Person UniqueID, Date, start time, end time, etc.})
    4. Create a report that prints each person's schedule with page break between people.
    5. Write a small VBA routine to loop through the Person Table calling the report with the Person UniqueID as a Where clause and use the builtin feature to write report to PDF file then have the code email it to the person's email address.

    You can probably find code examples that will do most of these operations online. If I can find the time I'll tackle parts for you and maybe some others here will also so keep us informed of your progress. HTH
    Last edited by RetiredGeek; 2013-08-02 at 20:52.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MachX,

    Ok, here's another piece.

    After linking the workbook/worksheet I created a Table creation query as follows:
    qryMkTblCreatePeopleTable.JPG
    Before running this query you need to enter SQL View by right-clicking on the design window and selecting SQL View then add the word DISTINCT as shown below:
    Code:
    SELECT DISTINCT SourceData.INDLASTNAME, SourceData.INDFIRSTNAME, SourceData.INDSEX, SourceData.EMAIL INTO People
    FROM SourceData;
    You can then click the Run button on the ribbon to create the table.

    Next you need to edit the People Table definition and add the primary key.
    PrimaryKey.JPG

    Resulting Table View
    PeopleTable2.JPG
    That's about all for tonight.

    HTH
    Last edited by RetiredGeek; 2013-08-03 at 12:06.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MachX,

    Next step create Assignments table:

    MakeTable Query:
    qryMkTblCreateAssignments.JPG

    Note: the Join uses Email address since that is a unique identifier that is in both the SourceData and People table.

    Created Table:
    AssignmentsBeforePK.JPG

    Next we have to create a Primary Key for the Assignments Table. In order to do this we first have to change the IDNo field type to Number/Long Integer since you can't have 2 AutoNumber fields in one table. So make that change then save the table then you can add the AssignNo field w/type AutoNumber and save again.
    AssignmentsAfterPK.JPG

    The resulting data sheet:
    AssignmentsFinal.JPG

    Of course I've not included a lot of your fields in either table for two reasons:
    1. I don't know which apply to the Person and which apply to the Assignment.
    2. I don't know if you really need them, e.g. Date If you have Year/Month/Day fields date is redundant!

    You can, of course, include any fields you feel necessary in either of the Make Table Queries.

    Now that you have your initial data loaded into Access you can delete the SourceData link as it is no longer needed.

    Next step write the Report.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MachX,

    Ok here's my stab at the report.
    Please note that if you look at the report in Access it doesn't show the page break. However, if you go to File -> Print -> Print Preview you'll see the page break which won't be much of a problem anyway when the report is produced by code using a filter to only generate one person at a time.

    First we generate a query that joins the two tables and calculate some fields like Date.
    qryAssignmentreportData.JPG

    Now we create the Report using the Query as the Data Source.
    ReportDesignAssignments.JPG

    Resulting Report:
    Assignment Report.JPG

    Here's the test database in a Zip file.
    MachX.zip

    Next Step code to do the emailing:

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    New Lounger
    Join Date
    Aug 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are a star! Thanks soo much!

Posting Permissions

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