Automating emails from Access using Outlook 97 and Access 97

Hello, I have used code in Access and Excel 2002 that enabled me to send an 
email with file attachments from Access or Excel. I am now with a firm which 
uses Office 97 and my code doesn't work. Is it possible to send an email from 
within Access 97? If so, could you point me in the direction of a source of 
information it? I have no idea where I'm going wrong..

Many thanks in advance

Tim Long

Yes, it should be possible since all versions of Outlook support external automation using methods like Application.CreateItem to create a new mail message and MailItem.Attachments.Add to add an attachment. It might be helpful if you posted a code snippet to start the discussion. 

-- 
Sue Mosher, Outlook MVP
Author of
     Microsoft Outlook Programming - Jumpstart for 
     Administrators, Power Users, and Developers
      http://www.**--****.com/ 

Hello, I've taken out the content of SubjectCell, MsgTxt and the recipient 
email address in the following, but the rest is as I had it when I tried to 
run it, including the comments. The code halted at the line: Set OL = 
CreateObject("Outlook.Application"). As you can tell, I'm an amateur at VBA 
(no error handling or checking to make sure applications are open etc.. I 
alone used to run this procedure and knew what I needed to have available)!  

I had previously used it in Excel 2000 to send emails with xls attachments. 
I am hoping to be able to do the same, more or less, from Access 97 (run 
queries, save the results to xls files and email the files to the recipients).

Sub ResearchContactsEmail()

    Dim OL As Object, MailSendItem As Object
    Dim MsgText As String, SubjectCell As String, SendFile As String
        
    currDate = Date
    currStr = Format(currDate, "d_mmm_yy")

SubjectCell = " "
MsgTxt = " "
       
'Start the Outlook session
    Set OL = CreateObject("Outlook.Application")
    Set MailSendItem = OL.CreateItem(olMailItem)
    
'Create the message
    With MailSendItem
        .Subject = SubjectCell
        .Body = MsgTxt 
        .To = " "
        .Attachments.Add "C:\New_Research_Contacts_" & currStr & ".xls"
        .Send
    End With

    Set OL = Nothing

End Sub

Are you running an anti-virus program that includes script blocking? If so, chances are that it is blocking the call to CreateObject("Outlook.Application"). 

-- 
Sue Mosher, Outlook MVP
Author of
     Microsoft Outlook Programming - Jumpstart for 
     Administrators, Power Users, and Developers
      http://www.**--****.com/ 

I think that may well be the case, because security is a big issue with the 
firm.. I guess that means I won't be able to use this functionality?

Thanks again

Tim Long