Sending an email using MSAccess 97 and 03

Hi all,

Currently I have an Access programme which I can print reports from.

I was wondering if there is a way of rather than printing the report I could automatically attach it to a blank email of MS Outlook Express or MS Outlook 03.

I know it can be done in A07 but was wondering if anyone would have the code to be able to do it in these two other versions.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Despite the brevity of your request, this is can actually end up being fairly complicated depending on *exactly* what you need to do.

If your emailing needs are fairly simple, then you can attach a report in Access 2003 or 97 to an email, if you use the .rtf (Word), html, Text, or Excel formats
...Basic code looks like this:
DoCmd.SendObject acSendReport, "YourReportName", acFormatHTML, "", , , "Report Attached", "Here is your Report"

See the help files on "SendObject" for more info.
See here for the different formats:

If you would like to send a PDF you can use this (but you need to be good at VBA)

Need more complex emails, you will have to use even more complex code like this:


PipMicAuthor Commented:

Thanks for your reply.

Basically I have two buttons on a form
1. Print Previews an A97/03 report
2. Prints an A97/03 report

My idea was to have an additional button which would email the report. This being as automated as possible.

The idea was to first create a pdf of the report and then attach it to a blank outlook express or outlook 03 email. All I would then need to do is enter the email I would want to send it to, add the subject, enter some comment and then press send.

That is it. I can understand that there would be a lot of code involved but that in a nutshell is it.

I will look through the links provided to see if these meets my requirements.

Jeffrey CoachmanMIS LiasonCommented:
To create a PDF in Access 2003 and older, you need an external program.
The link I posted is popular with many Access developers

Then once the Report-To-Pdf code is working in your database, you need to use code like this to create a blank email with the PDT attatched.

Dim strReportName       As String
Dim blRet               As Boolean
Dim objOutlook          As Outlook.Application
Dim objOutlookMsg       As Outlook.MailItem
Dim objOutlookRecip     As Outlook.Recipient
Dim objOutlookAttach    As Outlook.Attachment

    'Set the Report name variable
    strReportName = "rptDetailVideos"

    'Open the report Hidden
    DoCmd.OpenReport strReportName, acViewPreview, , , acHidden
    'This is the call to the Stephen Lebans code
    ' that creates the PDF.
    blRet = ConvertReportToPDF(strReportName, vbNullString, "C:\YourFolder\PDF\" & strReportName & ".pdf", False, False, 150, "", "", 0, 0, 0)
    'Close the report once the PDF is created
    DoCmd.Close acReport, strReportName

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        Set objOutlookAttach = .Attachments.Add("C:\YourFolder\PDF\" & strReportName & ".pdf")
    End With

'Cleanup Code
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing

Now the caveats:
This may trigger a security warming, then you need this to bypass it:
You need to set a reference to the Outlook library in your VBA editor

Again, all of these external utilities and this code can be replaced with that single line of code I posted.:
DoCmd.SendObject acSendReport, "rptDetailVideos", acFormatRTF
(Outputs to a Word doc)
,,,The kicker there is that the attachment may not look as nice, and can be edited.

Very basic sample is attached


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.