Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using a recordset to send multiple emails

Status
Not open for further replies.
domino3

domino3

MIS
May 20, 2003
307
GB
I am trying to use the info in the microsoft knowledge base to send multiple emails using an access recordset. As far as I can see, I have created the table with the email addresses and the form with the subject and message correctly, and left them both open when I try to run the code by opening the debug window and typing in "sendmessages" and then pressing "enter" ( I don't need to send an attachment with the emails). However, I get a compile error telling me that the user-defined type is not defined. The code I am using is below.

Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I would be grateful if someone could tell me what I am doing wrong, as I have been trying to do this for a few days, and am very keen not to have to type in a couple of hundred email addresses by hand in order to send them directly through outlook. Thank you.
 
Sort by date Sort by votes
At what point are you getting the error and:

Did you reference the Outlook object library?
 
Upvote 0 Downvote
I get the error on Dim MyDB As Database.

I did reference the object library. However, the instructions said to reference object library 8 and I have 9. I don't know if that would make a difference, but it seemed the only option available on my system.
 
Upvote 0 Downvote
You need to reference the Microsoft DAO 3.x Object Library too.
Furthermore I suggest to replace this:
Dim MyRS As Recordset
with this:
Dim MyRS As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I've made the changes you've suggested and the module now seems to do something, however the test email I have sent myself hasn't arrived.

I've tried to send to two different email addresses and neither one has arrived. I don't know if I've put the correct info in the right boxes on the form. The email address I'm sending to is in the tblMailingList table, and the address I want to have appear as the address I am sending from is in the ccaddress box in the open form.

I've tried sending with the mailing list closed, and then also tried with it open and neither seems to make a difference. I'm don't know why it still isn't working.
 
Upvote 0 Downvote
I've now tried leaving the cc address field blank, and that has made no difference either. The three items I would like to have appear on the recipient emails are the following:

the sending email address (not the one I am actually sending from as I am trying to get this working from home, but I need the email to look as if it has gone from the central address at work as that is where I would like the replies to go to)

the subject field

the message field

I don't know if the fields I have set up already would do that, as I can't work out where to have the email address to reply to. I tried having that in the ccaddress field and didn't know whther that was what was causing the problem.

Any help on how to solve this would be much appreciated. Thank you.

 
Upvote 0 Downvote
I've just realised that at home I use outlook express, not outlook, to send/receive emails. Am I right in assuming that I have to change the code to open outlook express instead, and, if so, how?
 
Upvote 0 Downvote
I've solved the outlook problem, and the message now arrives. However, is there any way of having a different email showing to the one I am sending from. As I said earlier, I need to have replies going to my work email, even though I am sending the original email from my personal email address at home. When I have sent myself a test, my home email appears when I tried replying to the email.

I assume it would go just above:

.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText

But I can't find any option that seems to cover what I need. Can anyone help me on this, as I really need to send these emails before the weekend, and I won't be back at work until after that.
 
Upvote 0 Downvote
I gave up, and came into work to make sure the emails went before the weekend. However, what I did find was that my system asked me to confirm each email going out as it was being sent by a programme other than outlook. For future reference, is there any way of over-riding this as I had to click yes a couple of hundred times to make sure that all the emails went? Also, I got a debug message from the module on .send, but the emails were actually sent. Does anyone know whether this is to do with my previous question? Thank you.
 
Upvote 0 Downvote
Hello and welcome to the club of Outlook Object Model Guard. Starting from Outlook 2000 Service Pack 2

Three Options
[ul]
[li]ClickYes program[/li]
[li]SendKeys[/li]
[li]Outlook Redemption [/li]
[/ul]
 
Upvote 0 Downvote
Thanks for the reply. If I needed to use sendkeys, would I replace the .send in the code below with

object.sendkeys "Yes"

to make the module click on the yes button each time?

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Or am I totally misunderstanding what is needed? I'd like to work it out now, while the problem is still fresh in my mind, rather than try to work it out in a panic next time I have to do a multiple email. Thanks
 
Upvote 0 Downvote
domino3

Instead of

.Send

use

SendKeys "%{s}", True 'This sends the e-mail (won't work if you are stepping through)

BUT, it is not very reliable.... you might find yourself clicking "Yes", many many many times [sad]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MacroAlan
  • Locked
  • Question
Empty recordset stops 1
Replies
3
Views
381
Andrzejek
Andrzejek
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
685
Zyborg
Zyborg
Moss100
  • Locked
  • Question
Access send email attachments - problem when null or empty
Replies
7
Views
3K
Andrzejek
Andrzejek
breezett93
  • Question
Converting from 32bit to 64 bit. After converting, database takes significantly longer to open 1
Replies
14
Views
1K
dhookom
dhookom
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
1K
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top