Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post

    I have an Access 2007 database, which holds Membership data, including Monthy Dues.

    A Query will provide me with the Membership details, the Dues Opening Balance, Current Dues and Closing Balance data.

    I am currently using this data in a Mail Merge using Word 2007. (Successfully), with the output showing the Dues data and an Address box taylored for Window Envelopes.

    BUT, to save postage, I would like to email merge these notices to individuals (different balances for each recipient) via Outlook. (I also need to add an attachment, which is the same for each recipient)

    I have perused the Internet, but this only adds to my confusion as each solution is not what I require.

    Can any kind soul please point me in the right direction. I am assuming that any code would loop through each record in my select query.


    By the way. Happy New Year to all.

    Devious

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you use code to perform the current merge, very little change is needed to change it into an email merge.

    Here is some merge code I use, for a normal merge.
    Code:
    	
    With appWord
     	.ActiveDocument.MailMerge.OpenDataSource Name:=strdbName, LinkToSource:=True, Connection:=strQuery, SubType:=wdMergeSubTypeWord2000
     	.ActiveDocument.MailMerge.Destination = wdSendToNewDocument
     	.ActiveDocument.MailMerge.Execute
     End With
    For an email merge it becomes

    Code:
    With appWord
     	.ActiveDocument.MailMerge.OpenDataSource Name:=strdbName, LinkToSource:=True, Connection:=strQuery, SubType:=wdMergeSubTypeWord2000
     	.ActiveDocument.MailMerge.MailAsAttachment = False
     	.ActiveDocument.MailMerge.Destination = wdSendToEmail
     	.ActiveDocument.MailMerge.MailSubject = strSubject
     	.ActiveDocument.MailMerge.MailAddressFieldName = "Email"
     	.ActiveDocument.MailMerge.Execute
    End With
    Then make sure that the field with the email address is in the query, and provide the subject of the email via the form that launches all this.

    But...I just remembered, this Email Merge option does not support attachments. So if you really want an attachment you would need to resort to looping through a recordset. If you could include the attachment as part of the Word template this method would work. Is that an option?
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If you are running the merge from Word rather than Access, you can choose to send the results of the merge via email, and you can send the resulting document as a Word document attachment. That approach doesn't require any code.
    Wendell

  4. #4
    New Lounger
    Join Date
    Jan 2010
    Location
    Phuket
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to mail merge email addresses in Access 2003 from a word document and I can get as far as trying to select the recipients. It won't let me choose the addresses I want. Whatever I click on everything remains the same.

    Any thoughts?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think we need to know a bit more about how far you get in the process.

    When you Select Recipients, you need to navigate to the correct database file. then from within that file you choose a table or query as the datasource.

    Then from within that table/query you get to select fields to use, and select recipients.

    Which steps of this process work, and so where do you get stuck?
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Most ISPs will limit the number of emails that can be sent at once. Your code would have to split the emails into groups of maybe 50. I wrote code that did just that in Access 2003, and it worked a treat. But inexplicably Outlook can stop sending them. No one in the Lounge could find a way to get Outlook to work consistently.
    There is a company that sells emailing programs that can be accessed through VBA. These bypass Outlook altogether and can send any amount of emails in one hit. A while ago I purchased such a program called PB EMail. However I never got around to using it .
    Regards,
    Peter

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Tony Kelsey-Stead View Post
    I'm trying to mail merge email addresses in Access 2003 from a word document and I can get as far as trying to select the recipients. It won't let me choose the addresses I want. Whatever I click on everything remains the same.

    Any thoughts?
    I'm not clear on exactly what you are trying to do. Are you trying to use a Word document as the basis for an email being sent from Access, or are you trying to use email addresses stored in an Access database as the data source for a Word Mail Merge?
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell, I was just trying to guide 'Devious'.
    In time gone by I wrote an Access vba procedure to send bulk emails. In doing that I had to break them up into groups of no more than 50 to get past the ISP's restrictions. It all worked perfectly except frequently Outlook would stop sending about halfway through. No one in the Lounge (including Hans) could find a reason - it was just a quirk of Outlook. I purchased 'PB EMail' to overcome Outlook's quirks, but never used it. I have well and truly finished with that project now and have no desire to revisit it.
    Regards,
    Peter

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Actually, I was replying to Tony Kelsey-Stead, not to Devious. That Lounger has a similar problem apparently, but the post wasn't terribly clear, at least to me.

    Your experience with Outlook is different from ours, but I should point out that we are linking Outlook to Exchange Server. In that arrangement we has sent as many as 60K personalized emails and had it work reliably and repeatedly. Of course 10% of the emails were always wrong and bounced. I have also used Automation from Access to Outlook to generate personalized emails with attachments - in fact we do that pretty regularly. But you need something like ClickYes or Redemption if you are going to do very many.
    Wendell

Posting Permissions

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