Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 30th, 2006, 10:08 AM
Registered User
Join Date: Aug 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Email Attachments in Access

Hi there,

Can anyone help. I am trying to set up to automatically email attachments from Access and have used the code below, that I found on this forum and I believe originates from http://www.jephens.com/howtoemail.asp

It works great to generate emails but when I uncomment the line to add an attachment as described it does not work. I add the subject, grab the body text and then nothing...
Access just seems to freeze, no error message or anything. I then have to use the task manager to close it down.
Does anyone have any ideas about what is going wrong??
Is there some more code I have to add somewhere other than the one line that I have uncommented??

Any help very much appreciated.

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

Set fso = New FileSystemObject

 ' First, we need to know the subject.
 ' We can't very well be sending around blank messages...

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
                 "We Need A Subject Line!")

 ' If there's no subject, call it a day.

If Subjectline$ = "" Then
    MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
    Exit Function
End If

 ' Now we need to put something in our letter...

BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
             "We Need A Body!")

 ' If there's nothing to say, call it a day.

If BodyFile$ = "" Then
    MsgBox "No body, no message." & vbNewLine & vbNewLine & _
         "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If

 ' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
    MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
           "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If

   ' Since we got a file, we can open it up.
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

   ' and read it into a variable.
    MyBodyText = MyBody.ReadAll

   ' and close the file.

   ' Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application

 ' Set up the database and query connections

    Set db = CurrentDb()

    Set MailList = db.OpenRecordset("MyEmailAddresses")

 ' now, this is the meat and potatoes.
 ' this is where we loop through our list of addresses,
 ' adding them to e-mails and sending them.

    Do Until MailList.EOF

        ' This creates the e-mail

        Set MyMail = MyOutlook.CreateItem(olMailItem)

            ' This addresses it
            MyMail.To = MailList("email")

            'This gives it a subject
            MyMail.Subject = Subjectline$

            'This gives it the body
            MyMail.Body = MyBodyText

            'If you want to send an attachment
            'uncomment the following line

             'MyMail.Attachments.Add "c:\myfile.txt", olByValue, 1, "My Displayname"

            ' To briefly describe:
            ' "c:\myfile.txt" = the file you want to attach
            ' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
            ' the shortcut only works if the file is available locally (via mapped or local drive)
            ' 1 = the position in the outlook message where to attachment goes. This is ignored by most
            ' other mailers, so you might want to ignore it too. Using 1 puts the attachment
            ' first in line.
            ' "My Displayname" = If you don't want the attachment's icon string to be "c:\myfile.txt" you
            ' can use this property to change it to something useful, i.e. "4th Qtr Report"

            'This sends it!

            'Some people have asked how to see the e-mail
            'instead of automaticially sending it.
            'Uncomment the next line
            'And comment the "MyMail.Send" line above this.


    'And on to the next one...


 'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

Set MyOutlook = Nothing

Set MailList = Nothing
Set db = Nothing

End Function

Similar Threads
Thread Thread Starter Forum Replies Last Post
Email with Attachments echovue Access 2 March 22nd, 2005 10:36 AM
sending email with attachments harpua Classic ASP Basics 2 February 16th, 2005 07:26 PM
Email attachments tucker Excel VBA 1 June 10th, 2004 03:25 AM
Email Attachments Doug Riede Classic ASP Basics 0 October 24th, 2003 11:11 AM
Email w/Attachments aingalsbe PHP How-To 1 June 25th, 2003 04:48 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.