Access and Outlook.... REALLY STUCK!!!!!

[Cricker]

I am having a problem. I have this database with a text field (txtEmail). I have @ 1500 people in this database (which holds info like name, address and so on..). I would like to be able to click on a push button and get all the Email addresses in that field and be able to send an email to all those people. Could someome help me......

Thanks

Chris

 

[nberryman]

Try this

It send a message to one person and BCCs it to the names returned by the query

One message to many addressees.

The following calls the code at the bottom of the Tip and uses a query QryEmail and the field EmailAddress which you would need to change to suit your query and field names.

This creates a new message and puts the Yourname@hotmail.com in the To field as this stops Outlook compaining. The MyEmailList puts all the e mail addresses returned by your query into the BCC field. As the BCC hides all the names except that recipient I thought it would be neater. There is a problem with a lack of error trapping if an invalid email address is returned. Maybe someone can come up with a cure for this.

Private Sub Command0_Click()

Dim MyEmailList As String

MyEmailList = ColumnToLine("QryEmail", "EmailAddress&quot

DoCmd.SendObject , , , "Yourname@hotmail.com", , MyEmailList, "Test Message", , True

End Sub

Copy the following code into a new module for the above to work

Public Function ColumnToLine(TblQueryName, ColumnName)
On Error GoTo ErrInFunction
Dim myRs As DAO.Recordset
Dim ResultString As String
Set myRs = CurrentDb.OpenRecordset(TblQueryName)
If myRs.RecordCount > 0 Then
Do Until myRs.EOF
ColumnToLine = ColumnToLine & IIf(Len(ColumnToLine) = 0, "", "; &quot & myRs(ColumnName)
myRs.MoveNext
Loop
End If
FinishPoint:
On Error Resume Next
'MsgBox ColumnToLine
myRs.Close
Exit Function
ErrInFunction:
ColumnToLine = "Error: " & Err.Description
Resume FinishPoint
Exit Function
End Function
Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[jrpope]

I've been having the same problemand I've tried your code. This comes up with a compile error(user-defined type not defined on line "Dim myRs As DAO.Recordset". Do you have a fix for this?

 

[Cricker]

Nope... I'm getting the same Error... Hope someone can help us out....

cw

 

[jrpope]

I'm getting pig-headed in my old age. I've been beating my head against the wall on this for a while. I think I finally stumbled on the answer. (I'm new to access, the last database I wrote was in Dbase IV)

All you have to do is replace:

Dim myRs As DAO.Recordset

with:

Dim myRs As Object

I just tried it and it actually works. Reading up on the subject I found "DAO" is old hat and losing support (being replaced with ADO or some other part of the alphabet). It may not even be supported in Access 2K, I don't know.

After all this head banging, I feel stupid for such a small fix.

Best of luck.

 

[Dan8376]

I'm going to assume you have Microsoft Outlook since you are using Microsoft Access. If you don't have Outlook then this won't help you so stop reading.

Add the Microsoft Outlook 9.0 Object Library
(note: your number could be different depending on your version of Office)

Put this code in a module, this code only works when Outlook is already open. This will put together an email for you. You can remove the ' to enable other parts of the code. The code requires that you define set strTo as a string of all of your email addresses.

Sub CreateEmailList()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim mySQL as String
Dim strTo as String
strTo = ""
'(mySQL should be a query of all your email addresses, you will need to change the field and table names that I used to equal what you have in your database)
mySQL = "SELECT EmailAddress FROM tblThatContainsEmails;"
Set db = CurrentDB
Set rs = db.OpenRecordset(mySQL)
Do Until rs.EOF
If strTo = "" Then
strTo = rs!EmailAddress
Else
strTo = rs!EmailAddress & "; " & strTo
End If
rs.MoveNext
Loop
rs.close
set rs = Nothing
set db = Nothing
'End Sub

'Sub SendMail()
'<start outlook application>
Set lobjOutlook = CreateObject(&quot;Outlook.application&quot
'<create new mail item (0)>
Set lobjMail = lobjOutlook.CreateItem(0)
'Let lobjMail.Subject = strSubject '<add subject>
'Let lobjMail.HTMLBody = strBody '<add body>
'Let lobjMail.SentOnBehalfOfName = strFrom
Let lobjMail.To = strTo '<add To>
'Let objMail.cc = strCc '<add CC list>
'Let lobjMail.bcc = strBCC '<add bcc list>
'<add attachments>
'Set lobjAttachment = lobjMail.Attachments
'lobjAttachment.Add path1, , , <Description of the file>
'lobjMail.display
'<save email as a draft>
lobjMail.Save
'<send email automatically>
'lobjMail.Send
'<Quit Mircosoft Outlook>
'lobjOutlook.Quit
End Sub

Good Luck
Dan

 

[Cricker]

I'm having a -

Run-time error '2287'

Microsoft can't open the mail session.
Check your mail applicationto make sure that it's working properly.

Do u know what to do about that???

cw

 

[Dan8376]

I don't know what could be causing that error. Make sure that you have Microsoft Outlook open when you run your procedure. Also make sure you added the Microsoft Outlook 9.0 Object Library, in your VB References.

Try this code. Its the same as above without all the extra lines. I will know if it worked unless you change the email address. If it works I should receive an email from you.

Sub SendMail()
Set lobjOutlook = CreateObject(&quot;Outlook.application&quot
Set lobjMail = lobjOutlook.CreateItem(0)
Let lobjMail.Subject = &quot;TESTING E-Mail&quot;
Let lobjMail.To = &quot;mdjohnston@statestreetkc.com&quot;
lobjMail.Send
End Sub

Dan

 

[Cricker]

Sub SendMail()
Set lobjOutlook = CreateObject(&quot;Outlook.application&quot
Set lobjMail = lobjOutlook.CreateItem(0)
Let lobjMail.Subject = &quot;TESTING E-Mail&quot;
Let lobjMail.To = &quot;mdjohnston@statestreetkc.com&quot;
lobjMail.Send
End Sub


This code works when I put in a person in the quotes... i would like to send it to the people in the QryEmail throgh Bcc... any suggestions for that??

cw

 

[kaiana]

I use a code very simmilar with no problems.

I think most of the errors are caused by the lack of references to both DAO and Outlook in your DB, Add the relevant references through tools\references wizard. Without these references you will get Compile errors.
(This is hard to find through debug if you havent seen it)

This may help?

kind regards
Jason Spence