Email column from access 2002 using outlook 2

[splats]

Hello folks

Here is my code for a command button. I am getting a type mismatch error (run time error '13'). The problem seems to be in Set rs = db.OpenRecordset(sql)

Is it possible that I need a reference checked? I am stuck! Thanks in advance!

Private Sub EmailPersonal_Click()

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "Select from [QU-EmailPersonal]"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!Email) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!Email & "; "
End If
rs.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , emailTo

End Sub

 

[JoyInOK]

Your SQL statement is missing a semicolon at the end.
sql = "Select EMail from [QU-EmailPersonal];"
and make sure that DAO is referenced in Tools/References.
Then add DAO to the front of your recordset reference to distiguish it from an ADO recordset
dim rs as DAO.Recordset

 

[splats]

Hello again JoyInOK

Once again you have enlightened me!
Here's the latest bug in the code.

Run time error '2295'
Unknown message recipient, the message was not sent.

Instead of sending this email automatically, I would like to only go to the blank email in Outlook with the list of email addresses in the emailto prompt. What would you suggest instead of:
(DoCmd.SendObject acSendNoObject, , , emailTo)?

thanks again

 

[geaker]

Try this:


Dim strEmail As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem

Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)
'Email Reports

With MailOutLook
.To = strEmail
.Subject = "Whatever you want"
.Body = "Whatever message you want"
' Next line adds an attachement if you want.

.Attachments.Add stPath
.Send

End With

Set MailOutLook = Nothing
appOutLook.Quit

Gerald

 

[JoyInOK]

Good work, geaker. Your code is similar to what I use.
Tinat, you will need to add a reference to the version of Outlook you are using, and you may have trouble if different users have different versions of Outlook.
When using Outlook 2000 or XP with the latest security updates, launching an email from another program invokes the following Message Box, for obvious reasons:
A program is trying to access email addresses you have stored in Outlook. Do you want to allow this?
If this is unexpected, it may be a virus, and you should choose "No."
The message box includes a box you must check with different lengths of time. Note that you don't have to finish the email message within that time; rather, it's the length of time Outlook will allow your program access to your email addresses (and your program needs access for only a moment once you check the box).
You should instruct your users that they need to both check the checkbox and click "Yes" to open the addressed, blank email.

 

[splats]

Hello JoyInOK and geaker

Thanks for your help. I am working from home today as my daughter is sick. I am hooked up to my work email from home. Here is the code that I have used yesterday after posting. I was close as i did manage to get Outlook to open a blank email. however, there were no addresses included in it.

Private Sub EmailSupplier_Click()

Dim db As Database, rs As DAO.Recordset, sql As String, emailTo As String
Dim MyMail As Outlook.MailItem, myOlApp As Outlook.Application

Set db = CurrentDb()
emailTo = ""
sql = "Select [BusEmail] from [QU-EmailSupplier];"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!BusEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!BusEmail & "; "
End If
rs.MoveNext
Loop

Set myOlApp = CreateObject("Outlook.Application&quot
Set MyMail = myOlApp.CreateItem(olMailItem)

MyMail.Display

End Sub

I have tried the code below but nothing happens!

Private Sub EmailSupplier_Click()

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim db As Database, rs As Recordset, sql As String, emailTo As String

Set db = CurrentDb()
emailTo = ""
sql = "Select [BusEmail] from [QU-EmailSupplier]"
Set rs = db.OpenRecordset(sql)

Do Until rs.EOF
If Not IsNull(rs!BusEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!BusEmail & "; "
End If
rs.MoveNext
Loop

Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)
'Email Reports

With MailOutLook
.To = emailTo
.Subject = "Whatever you want"
.Body = "Whatever message you want"
'Next line adds an attachement if you want.

'(I want the email code to stop here, hence the ')
'.Attachments.Add stPath
'.Send

End With

'Set MailOutLook = Nothing
'appOutLook.Quit

End Sub

Outlook 2000 is the version that I use at work. At home it is Outlook 2002.

Thanks again.

Tina

 

[splats]

It works. I figured it out! I added

MailOutLook.Display to the bottom of the With statement.

thanks again everyone!

 

[splats]

ooohh one more thing...

I built this at home over the weekend. My home system uses Access 2002 and Outlook 2002. However, at work we use Access 2002 and Outlook 2000. There is a problems with the references. I added in the reference for outlook 9.1 and moved it to the top however, there are still issues with the references. Has anyone out there encountered this type of problem? How can I fix it?

Once again I am at home today, so I cannot experiment from here.

Thanks

 

[JoyInOK]

Yeah. Uncheck the reference to Outlook 10. You may need to experiment to see if you also need to downgrade your reference to Microsoft Office 10

 

[splats]

Thanks JoyInOK

I figured it out. I didn't realize that I had to uncheck the reference for Outlook10, close the references pop up screen and then enter in the reference for Outlook 9.1
Now it works fine!

thanks again.

tina

 

[splats]

Ok another ripple in the database. I seemed to have fixed the problem through my own computer. However, when i have a user try to use the database, there are problems. MSCOMCT2.0CX version 2.0 is a missing reference. Why would this show up only on the user's computer and not on my computer? What should I be looking for and how can it be fixed?

thanks

 

[splats]

never mind, I fixed it. There was a reference that i have on my system but that my users do not have. so i removed it. It works now!

Whew!

 

[vladab]

Hi, folks,

I want to send e-mail message to our clients via Outlook, taking their addresses from Access 2000 database. I am using the code similar to the above mentioned, but I am trying to send the letter as HTML page with Images and different formatting. I use .HTMLBody property of MailItem object like .HTMLBody = strHTML where strHTML is HTML text.
When I send it to our internal e-mail addresses I can see it as a web page I created. On the external addresses it looks just a text with formatting and links to all images.
I have a feeling, Outlook rebuilds my HTML text on the run.

Does anybody know how to fix that problem?
Your help will be greatly appreciated.

Vlada.