Consulting

Results 1 to 12 of 12

Thread: Solved: Attach Word File to Email

  1. #1
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location

    Solved: Attach Word File to Email

    I am looking for code to attach word files to emails and send them to different people based on a table in access. Any one have any ideas?

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, Tricia.

    So....will it always be the same Word file? Or is the name/path of the file in a field somewhere? Where is the email address? I assume a field, and the coders will need to know that field's name too.

    If possible, can you make a copy of your database, remove anything not related, change the names to protect the innocent, compact, zip, and ship up here?
    ~Anne Troy

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    I have attached the database. the files will be the check number .rtf and the email adress associated with that file is listed as well in the table numberandemail.

  4. #4
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    Quote Originally Posted by Tricia
    I have attached the database. the files will be the check number .rtf and the email adress associated with that file is listed as well in the table numberandemail.
    Will all the .rtf files be in the same folder? Are the files really named 1.rtf, 2.rtf etc,etc? Anyway, I've created a new form in your db called frmMail. The code to send the email is as below...
    [vba]
    Private Sub CmdSendMail_Click()
    On Error GoTo Err_CmdSendMail_Click
    Dim EmailApp, NameSpace, EmailSend As Object

    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.GetNamespace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)

    EmailSend.To = Me.Mail_Address_S
    EmailSend.Subject = "Latest Document"
    EmailSend.Body = "Please find attached the latest Word document" & vbCrLf & vbCrLf & "Best Regards" & vbCrLf & "Tricia"
    EmailSend.Attachments.Add "C:\" & Me.CheckNumber & ".rtf"
    EmailSend.Display

    Set EmailApp = Nothing
    Set NameSpace = Nothing
    Set EmailSend = Nothing

    Exit_CmdSendMail_Click:
    Exit Sub

    Err_CmdSendMail_Click:
    If Err Then
    MsgBox "The file was not found"
    Resume Exit_CmdSendMail_Click
    Else
    End If

    End Sub
    [/vba]
    you will need to change this line...
    [vba]
    EmailSend.Attachments.Add "C:\" & Me.CheckNumber & ".rtf"
    [/vba]
    to the folder where you store the files, ie.
    [vba]
    EmailSend.Attachments.Add "C:\MyDocuments\LatestDocs\" & Me.CheckNumber & ".rtf"
    [/vba]
    If you need anymore help let me know.
    Last edited by ___; 06-17-2004 at 01:19 AM.
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  5. #5
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    That works thanks but, I was looking for a little more automated version. I want it to loop through the whole table with one push of the button and send the e-mails out with out pushing send each time. Any suggestions?

  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    To send it straight away rather than display it just change:

    EmailSend.Display

    to

    EmailSend.Send

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    See if this works. I've not had time to test it because I've got to run to see the football!

    Let me know.
    If it doesn't I'll look closer at it tomorrow.
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay, guys. That's 2 down to go watch football (which is really soccer), but let us know how the game goes...
    ~Anne Troy

  9. #9
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    Quote Originally Posted by Dreamboat
    let us know how the game goes...
    Eng 3 - Sui 0 *hic*
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  10. #10
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    Ok, I've tested it now and this version works. I created a listbox called LstEMail with the Row Source query as below...

    SELECT numberandemail.CheckNumber, numberandemail.[Mail Address S]
    FROM numberandemail;

    then looped through each record in the list...

    [vba]
    Private Sub CmdSendToList_Click()
    On Error GoTo Err_CmdSendToList_Click

    Dim intCurrRow As Integer
    Dim EmailApp, NameSpace, EmailSend As Object

    For intCurrRow = 0 To LstEMail.ListCount - 1

    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.GetNamespace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)

    EmailSend.To = Me.LstEMail.Column(1, intCurrRow)
    EmailSend.Subject = "Latest Document"
    EmailSend.Body = "Please find attached the latest Word document" & vbCrLf & vbCrLf & "Best Regards" & vbCrLf & "Tricia"
    EmailSend.Attachments.Add "C:\" & Me.LstEMail.Column(0, intCurrRow) & ".rtf"
    EmailSend.Display

    Next intCurrRow

    Set EmailApp = Nothing
    Set NameSpace = Nothing
    Set EmailSend = Nothing

    Exit_CmdSendToList_Click:
    Exit Sub

    Err_CmdSendToList_Click:
    Resume Next

    End Sub
    [/vba]

    and as Mark007 said in his earlier post, to send it straight away rather than display it just change:
    [vba]
    EmailSend.Display
    [/vba]
    to
    [vba]
    EmailSend.Send
    [/vba]
    Hope it's what you wanted.
    Last edited by ___; 06-18-2004 at 12:56 AM.
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  11. #11
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    Did it work as you wanted?
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  12. #12
    VBAX Regular
    Joined
    Jun 2004
    Posts
    14
    Location
    It Works!!! Case Closed. Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •