Problem using access to send email following upgrade from Outlook 03 to 07

[Mattbro]

Hello Everybody,
I have a database that on_click, generates a .rtf file, opens up outlook, and sends it to all senders whose address exists within a table –all borrowed from http://www.jephens.com/2007/05/13/ho...using-outlook/. Code is as follows;

Option Compare Database
Option Explicit
 
Public Function SendEMail()
 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim stDocName As String
Set fso = New FileSystemObject
 
Subjectline$ = "BreachAlert"
               

MyBodyText = "Blahblahblah"
   
If 1 = 2 Then
   
BodyFile$ = "InputBox"
 
 
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
 
    MyBodyText = MyBody.ReadAll
 
    MyBody.Close
 
End If
 
    Set MyOutlook = New Outlook.Application
 
    Set db = CurrentDb()
 
    Set MailList = db.OpenRecordset("MyEmailAddresses")
 
 
Dim MyRecip As Outlook.Recipient
 
    Do Until MailList.EOF
 
       
        Set MyMail = MyOutlook.CreateItem(olMailItem)
           
            'MyMail.To = MailList("email")
            Set MyRecip = MyMail.Recipients.Add(MailList("email"))
            MyRecip.Type = olBCC
           
            MyMail.Subject = Subjectline$
 
 
    MyMail.Attachments.Add "I:\CYTLAB\Breach\Breach.rtf", olByValue, 1, "My Displayname"
            Dim MyQuery As QueryDef
            Set MyQuery = CurrentDb.QueryDefs("ReportSource")
            MyQuery.SQL = "select * from emails "
            MyQuery.Close
         
   
            MyMail.Send
   
 
    MailList.MoveNext
Loop
Set MyMail = Nothing
MyOutlook.Quit
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
End Function

Unfortunately, our IT has upgraded us from Outlook 2003 to Outlook 2007 and I am getting a debug error 287 on the line

  Set MyRecip = MyMail.Recipients.Add(MailList("email"))

I have tried googling for answers but to no avail. Can anyone see what I need to do? Any pointers very gratefully received.
Mattbro

[DepricatedZero]

We use Office 2007 as well. My mailer does things slightly different.

I use this

Dim oApplication As Outlook.Application
Dim oItem As Outlook.MailItem
Dim oAccount As Outlook.Accounts

Public Function BuildTemplate(data As TemplateData)
   Set oApplication = New Outlook.Application
   Set oItem = oApplication.CreateItemFromTemplate(data.GetPath)
   With oItem
      .HTMLBody = Replace(.HTMLBody, "something_to_represent_the_date_in_a_unique_string_pattern", "<font color=black>" & Format(data.GetTime, "mm/dd/yyyy h:mm AMPM") & " EST</font>")
'      a whole bunch of .HTMLBody = Replace(.HTMLBody, "x", y
      .BCC = .BCC & "; " & data.GetBCC
      .CC = .CC & "; " & data.GetCC
      .subject = data.GetSubject
   End With
End Function

Public Function Send()
   oItem.Display
End Function

It may be that for the 2007 MAPI you just need to use something like

MyMail.To = MyMail.To & MailList("email")

perhaps .Recipients.Add is deprecated? Not sure though.

[Mattbro]

Hmmm-no joy there-still throws up the error message. I am wondering if the problem may be that though we are using Outlook 2007, we are still on Access 2003....might be some sort of compatibility issue in the code?

[June7]

What is the error message?

Incompatibility is possible. The VBA reference for the Outlook object library probably has a different version number.

[Mattbro]

Hi June7,
On the original code, the error =Run time error 287 Application-defined or object-defined error.
If I change the code to DepricatedZeros idea, I get 'compile error-invalid use of property' on the word 'MailList'.

I had wondered about library reference-I remember that there was an issue at the beginning, and in order to get it to work, I had to trawl through the references for a different outlook object library. Now all I have is Microsoft Outlook 12.0 object library, and I am sure that wasn't the one I had to select. Is there a workaround or compatibility download for this sort of thing?
Mattbro

What is the error message?

Incompatibility is possible. The VBA reference for the Outlook object library probably has a different version number.

[June7]

I've never tried to get different versions of Office components to work together. Why don't you have Access 2007 with Outlook 2007? Maybe late binding will work http://word.mvps.org/faqs/interdev/e...atebinding.htm