Automating emails from Access using Outlook 97 and Access 97

[Guest]

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

 

[Sue Mosher [MVP-Outlook]]

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.

 

[Guest]

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

 

[Sue Mosher [MVP-Outlook]]

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

 

[Guest]

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

 

[Sue Mosher [MVP-Outlook]]

Not without upgrading Office (so you can use Outlook VBA) or getting rid of the script blocker.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers