Further help with Email code please - Doug Steele, David Cox?

G

Guest

Hi.

I’m trying to get the code quoted in the following Microsoft help page to
work.

http://support.microsoft.com/?id=318881#appliesto

I’ve already had help from Doug Steele and David Cox on this thread “Help
with Microsoft Help on linking Access to ….â€:

http://www.microsoft.com/office/com...&dg=microsoft.public.access.formscoding&fltr=

I then found a separate thread in which Doug helped someone with exactly the
same problem that I had last and I built this suggestion into my code.

At this point I hit further problems and decided to build the sample table
as suggested in the Microsoft help page and when I ran that, I got exactly
the same errors. So I figure I need to get the basic sample working first,
then my own code.

Here is what happens. I’ve built tblMailingList with EMailID, Name and
EmailAddress fields. I’ve added just one record, containing my Name and Email
address. I’ve built-in Dougs modifications to the Microsoft code which I copy
below. Also, my References ticked are Visual Basic for Application, Microsoft
Access 10.0 Object Library, Microsoft DAO3.6 Object Library and Microsoft
Outlook object library. I’m using Access 2000 on XP, networked.

When I call up the module code from a command button, I see an Outlook tile
appear in the taskbar and a message box appears warning me that a program is
trying to send and Email and will I allow this (I know this message is
normal). So I click OK and the screen flickers but stays the same. I have to
click 6 times before the window changes and at this point, a line of blue
boxes starts to fill a section of the window as if something is being done.
Once the rectangle is filled completely with blue, it just stays there. When
I close the message box, it and the Outlook tile disappears and I’m left with
the message â€Error 287: Application-defined or Object-defined errorâ€.

Any ideas what’s wrong? As I say, if I can get this sample working,
hopefully I can get my own version working (where the source is a parameter
query, rather than tblMailingList). Thanks for any help. Here is the code (I
have commented out the code referring to “cc†, “Subject†and “Bodyâ€):

Option Compare Database
Option Explicit

Sub SendMessages2(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.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
'.Importance = olImportanceHigh 'High importance

'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
 
G

Guest

Hi.

I've been playing about with this again. It seems that if I click on "Yes"
in the final message box window, an Email does actually get sent but I'm
given no opportunity to fill in the Subject or Message Body sections. I was
expecting Outlook to open and for me to actually see the Email and be able to
create the message text. Am I wrong to expect this? Perhaps the only way of
adding Subject and Body text is to get the user to do it via the form
mentioned in the Microsoft article.

Also, why do I have to click "OK" six times before the Email gets built?
Surely it should be just once?

Thanks

JohnB said:
Hi.

I’m trying to get the code quoted in the following Microsoft help page to
work.

http://support.microsoft.com/?id=318881#appliesto

I’ve already had help from Doug Steele and David Cox on this thread “Help
with Microsoft Help on linking Access to ….â€:

http://www.microsoft.com/office/com...&dg=microsoft.public.access.formscoding&fltr=

I then found a separate thread in which Doug helped someone with exactly the
same problem that I had last and I built this suggestion into my code.

At this point I hit further problems and decided to build the sample table
as suggested in the Microsoft help page and when I ran that, I got exactly
the same errors. So I figure I need to get the basic sample working first,
then my own code.

Here is what happens. I’ve built tblMailingList with EMailID, Name and
EmailAddress fields. I’ve added just one record, containing my Name and Email
address. I’ve built-in Dougs modifications to the Microsoft code which I copy
below. Also, my References ticked are Visual Basic for Application, Microsoft
Access 10.0 Object Library, Microsoft DAO3.6 Object Library and Microsoft
Outlook object library. I’m using Access 2000 on XP, networked.

When I call up the module code from a command button, I see an Outlook tile
appear in the taskbar and a message box appears warning me that a program is
trying to send and Email and will I allow this (I know this message is
normal). So I click OK and the screen flickers but stays the same. I have to
click 6 times before the window changes and at this point, a line of blue
boxes starts to fill a section of the window as if something is being done.
Once the rectangle is filled completely with blue, it just stays there. When
I close the message box, it and the Outlook tile disappears and I’m left with
the message â€Error 287: Application-defined or Object-defined errorâ€.

Any ideas what’s wrong? As I say, if I can get this sample working,
hopefully I can get my own version working (where the source is a parameter
query, rather than tblMailingList). Thanks for any help. Here is the code (I
have commented out the code referring to “cc†, “Subject†and “Bodyâ€):

Option Compare Database
Option Explicit

Sub SendMessages2(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.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
'.Importance = olImportanceHigh 'High importance

'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
 
G

Guest

Hi.

I've got further now and I understand what the message windows are telling
me and why (Microsoft security). I'm going to post a related question
seperately.

Thanks

JohnB said:
Hi.

I’m trying to get the code quoted in the following Microsoft help page to
work.

http://support.microsoft.com/?id=318881#appliesto

I’ve already had help from Doug Steele and David Cox on this thread “Help
with Microsoft Help on linking Access to ….â€:

http://www.microsoft.com/office/com...&dg=microsoft.public.access.formscoding&fltr=

I then found a separate thread in which Doug helped someone with exactly the
same problem that I had last and I built this suggestion into my code.

At this point I hit further problems and decided to build the sample table
as suggested in the Microsoft help page and when I ran that, I got exactly
the same errors. So I figure I need to get the basic sample working first,
then my own code.

Here is what happens. I’ve built tblMailingList with EMailID, Name and
EmailAddress fields. I’ve added just one record, containing my Name and Email
address. I’ve built-in Dougs modifications to the Microsoft code which I copy
below. Also, my References ticked are Visual Basic for Application, Microsoft
Access 10.0 Object Library, Microsoft DAO3.6 Object Library and Microsoft
Outlook object library. I’m using Access 2000 on XP, networked.

When I call up the module code from a command button, I see an Outlook tile
appear in the taskbar and a message box appears warning me that a program is
trying to send and Email and will I allow this (I know this message is
normal). So I click OK and the screen flickers but stays the same. I have to
click 6 times before the window changes and at this point, a line of blue
boxes starts to fill a section of the window as if something is being done.
Once the rectangle is filled completely with blue, it just stays there. When
I close the message box, it and the Outlook tile disappears and I’m left with
the message â€Error 287: Application-defined or Object-defined errorâ€.

Any ideas what’s wrong? As I say, if I can get this sample working,
hopefully I can get my own version working (where the source is a parameter
query, rather than tblMailingList). Thanks for any help. Here is the code (I
have commented out the code referring to “cc†, “Subject†and “Bodyâ€):

Option Compare Database
Option Explicit

Sub SendMessages2(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.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
'.Importance = olImportanceHigh 'High importance

'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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top