Email Merge from Access

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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 .

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote