Using Microsoft Access to create an email merge 1

[memmiller]

Hi everyone,
I am new here and glad I came across this site!
I need to setup Microsoft Access and or Outlook or Outlook Express to merge the email field in Access with a outgoing message for mass mailings.. does anyone know how to do this or if it is even possible without using 3 party software and if it isn't possible without using 3rd party software, what software would you recommend?

I really do hope I can get this solved and look forward to your responses.
Thanks,
Mike

 

[GeekGirlau]

Hi Mike,

Copy and paste the following code into a module in your database.

You will then need to add a reference to Microsoft Outlook. In the module, select Tools, References. Tick the Microsoft Outlook Object Library, then select OK.

To run the code, you can call the function from within any existing code, or just create a button on a form and change the "On Click" event to "=SendEmail()"

Public Function SendEmail() as Boolean
Dim olApp As Outlook.Application
Dim oMail As Outlook.MailItem
Dim db As DAO.Database
Dim rec As DAO.Recordset


On Error GoTo HandleErr
Set db = CurrentDb

' query extracting email addresses,
' or SQL statement to do the same
Set rec = db.OpenRecordset("myQuery", dbOpenSnapshot)

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application&quot

If Err.Number <> 0 Then
Set olApp = CreateObject(&quot;Outlook.Application&quot
End If

On Error GoTo HandleErr

Do Until rec.EOF
Set oMail = molApp.CreateItem(olMailItem)

With oMail
' use name of field from query containing e-mail address
.To = rec!EmailAddress
.Subject = &quot;Subject line&quot;
.Body = &quot;Text for the body of the e-mail&quot;
.Send
End With

rec.MoveNext
Loop

SendEmail = True


ExitHere:
On Error Resume Next
rec.Close
Set db = Nothing
Set oMail = Nothing
Set olApp = Nothing
Exit Function

HandleErr:
Select Case Err.Number
Case 91, 429
MsgBox &quot;An error has occurred!&quot; & vbCrLf & vbCrLf & _
&quot;Cannot connect to Microsoft Outlook.&quot;, vbExclamation, _
&quot;Outlook Error&quot;
Resume ExitHere

Case Else
MsgBox Err.Number & &quot;: &quot; & Err.Description, vbExclamation, _
&quot;An error has occurred&quot;
Resume ExitHere
End Select
End Function

 

[DIRENE]

I would like to sent email to customer I set up your function and change myquery to qrycustomer (name of my query . I have a form with a command button on it on click =sendEmail.

I'm getting a 424 object reguired error message when I click the button.

Can you Please tell me what I'm doing wrong and where the function is getting the subject and body of the email from. I take it that the email address is coming for my query.

Thank you Irene

 

[GeekGirlau]

Oops - the bad typing fairy strikes again!

Change ...

Set oMail = molApp.CreateItem(olMailItem)

to

Set oMail = olApp.CreateItem(olMailItem)

 

[Helen267]

Hi there

I'm trying to do something similar to Mike, ie. a mass mailout of a simple email in Outlook Express (with a Word attachment, so I can't use SendObject), using email addresses stored in an Access database.

I understand the code above, but I get an undefined user error when trying to run the script, because I don't have an Outlook reference set up. This is because under Tools -> References, there are no Outlook libraries to check! I've tried using the browse function (navigating to the Outlook Express folder), but I get an error when trying to add these *.dll files.

Is this library something I have to download? I'm using MS Outlook Express 6. Any help would be greatly appreciated!

Thanks

Helen in NZ

 

[GeekGirlau]

Helen, I haven't tested this but there is a reference for "Microsoft Outlook Express 5.0 Type Library" - please note that your version number may be different. On my computer this is pointing to MSOE.DLL in the directory C:\Program Files\Outlook Express

 

[DIRENE]

That worked but I'm now getting an error message in outlook.

The message is:

Your message did not reach some or all of the intended recipients.

Subject: Test Access
Sent: 4/14/2004 8:15 AM

The following recipient(s) could not be reached:

'ID1900@AOL.COM' on 4/14/2004 8:15 AM
553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1)

I'm using outlook 2002.

Thank you

Irene

 

[DIRENE]

I got it. The error in outlook was because our cable company change our outgoing address and did not tell us. Thanks for all your help. Irene

 

[Helen267]

Thanks for the response - I have that file as well, but Access won't let me use it as a reference (I get an error saying "Can't add a reference to the specified file"), and there are no references at all to any Outlook libraries in the select list!

Helen

 

[GeekGirlau]

Helen, you're right - you can't directly manipulate Outlook Express in the same way as you can Outlook.

I found some information - check out the following link: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=Xns9025E8880lylefairyahoocom@24.2.9.58&rnum=4]Email to Access from Outlook Express[/url].

As you will see, it's a LOT easier to do via Outlook, but this may not always be an option.

 

[Helen267]

Thanks again. As this mailout is only required once a year, I might just do a simple manual extract of the list of email addresses (there are only about 100 records), and import it as a text file to Outlook Express. I think it will be a lot easier!

Helen