Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169

    Howto attach the files in the 'attachment' field to an email

    Hello,
    I found the code below on Microsoft Answers which supposedly copies all files in the 'Attachments' field to a temporary location on your hard drive, which you can point to using my 'send email' code below. Im not quite sure how it all fits together....like the order and what all needs to go where. But I would like to be able to incorporate all the code together and have it send an email with attachments with the click of one command button.

    Suggested code:

    i just wrote this little function for you this will save your Attachment on your harddrive.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rstAttachment As DAO.Recordset2


    Dim fld As DAO.Field2
    Dim strPath As String
    Dim intz As Integer


    Set db = CurrentDb
    Set rst = db.OpenRecordset("tabelle1", dbOpenDynaset)
    rst.FindFirst "ID = " & Me!ID

    Set rstAttachment = rst.Fields("Feld1").Value
    Set fld = rstAttachment.Fields("Filedata")
    strPath = CurrentProject.Path & "\" _
    & rstAttachment.Fields("Filename")
    On Error Resume Next
    Kill strPath
    On Error GoTo 0

    fld.SaveToFile strPath

    rstAttachment.Close
    rst.Close
    Set rstAttachment = Nothing
    Set rst = Nothing
    Set db = Nothing



    You can add the mailcode to it and use strPath in the AttachmentAdd-Statement. And at the end you can use kill to delete the attachment. when the mail was send.

    _______________________________

    My 'Send Email' Code:

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    .CC = "email addresss"
    .Subject = "test"
    .HTMLBody = "test"
    .Attachments.Add " "
    'Send email
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
    .Send
    End With
    'MsgBox MailOutLook.Body
    Exit Sub
    email_error:
    MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
    Resume Error_out
    Error_out:
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So one procedure can call the other or you can mesh it all together as one long procedure. Just build it to do things in the order you prefer. User clicks button 'Send Email'. The email code starts. When it gets to the attachment part, call the procedure to save record's attachment files to table and loop through the folder to attach file(s).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Great! thanks. I think I'm almost there. I tried meshing it all together as one long procedure but I guess Im not inserting the code in the correct order. I would like to learn how to CALL a procedure as I think that may work best. I've read that in order to do that the proceedure has to be public. I have pasted the 2 proceedures seperately, which work great by themselves, but I need help on making it all work with just one command button.

    Send Email code (works):

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    .CC = " "
    .Subject = "test"
    .HTMLBody = "test"
    .Attachments.Add "C:\dbtemp\filename"
    'Send email
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
    .Send
    End With
    'MsgBox MailOutLook.Body
    Exit Sub
    email_error:
    MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
    Resume Error_out
    Error_out:
    End Sub

    Proceedure which will save files in the 'attachments' field to a folder on the hard drive (works):

    On Error GoTo Err_SaveFile
    Dim db As DAO.Database
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Set db = CurrentDb
    Set rsParent = Me.Recordset
    rsParent.OpenRecordset
    Set rsChild = rsParent.Fields("Attachments").Value
    While Not rsChild.EOF
    If Dir("C:\dbtemp", vbDirectory) = "" Then
    MkDir ("C:\dbtemp")
    Else
    'do nothing for the "C:\dbtemp" directory already exists
    'MsgBox "C:\dbtemp\ directory already exists"
    End If
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile ("c:\dbtemp\")
    rsChild.MoveNext
    Wend
    Exit_SaveFile:
    Set rsChild = Nothing
    Set rsParent = Nothing
    Exit Sub
    Err_SaveFile:
    If Err = 3839 Then
    MsgBox ("File Already Exists in the Directory!")
    Resume Next
    Else
    MsgBox "Some Other Error occured!", Err.Number, Err.Description
    Resume Exit_SaveFile
    End If
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Procedure only has to be public if you want to be able to call it from other modules. Calling a function or sub is simple: http://www.ehow.com/how_8165996_call-function-vba.html

    You don't show the declaration line of the procedures so I don't know names.

    Again, if you want one procedure just have the code run in the preferred sequence. So just before the 'With MailOutlook' line, can place the code for saving documents.

    What the email part doesn't have is code that will dynamically grab each filename in the folder and build the string of filenames for the attachment property. What you have now requires replacing the generic "C:\dbtemp\filename" with an actual filename, including the extension. How will you know the actual filename(s)? http://www.allenbrowne.com/ser-59.html
    Last edited by June7; 05-23-2012 at 03:34 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Yes Im working on the file attachement part, but need to make sure all my code works together first. I did what you said, but somehow, its just doing the save file portion. I cant get it to start the actual email send portion. Do I need to add a label or something somewhere? Maybe add a resume section?

  6. #6
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Here is the way I have it now. No errors on the form, just does the save file portion only:

    Private Sub Command962_Click()

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim OutlookAttach As Outlook.Attachment
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    On Error GoTo Err_SaveFile
    Dim db As DAO.Database
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Set db = CurrentDb
    Set rsParent = Me.Recordset
    rsParent.OpenRecordset
    Set rsChild = rsParent.Fields("Attachments").Value
    While Not rsChild.EOF
    If Dir("C:\dbtemp", vbDirectory) = "" Then
    MkDir ("C:\dbtemp")
    Else
    'do nothing for the "C:\dbtemp" directory already exists
    'MsgBox "C:\dbtemp\ directory already exists"
    End If
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile ("c:\dbtemp\")
    rsChild.MoveNext
    Wend
    Exit_SaveFile:
    Set rsChild = Nothing
    Set rsParent = Nothing
    Exit Sub
    Err_SaveFile:
    If Err = 3839 Then
    MsgBox ("File Already Exists in the Directory!")
    Resume Next
    Else
    MsgBox "Some Other Error occured!", Err.Number, Err.Description
    Resume Exit_SaveFile
    End If


    With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    .CC = " "
    .Subject = "test"
    .HTMLBody = "test"
    .Attachments.Add "c:\dbtemp\filename.xls"
    'Send email
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
    .Send
    End With
    'MsgBox MailOutLook.Body
    Exit Sub
    email_error:
    MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
    Resume Error_out
    Error_out:

    End Sub
    Last edited by tobydobo; 05-23-2012 at 04:06 PM.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The Exit_SaveFile code executes first so I doubt the email code will ever run. Need to get all the error handling at the end of the procedure, if you want one procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Woohoo! Thats got it. Now Im going to fix the attachments section and add something at the end that will delete the files after sending.

  9. #9
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Ok I thought I could figure out the multiple attachment thing but Im having problems. I thought I would try something like this, but I know Im missing something somewhere:

    Dim strFile As String
    strFile = Dir("C:\dbtemp\" & "\*.xlsx")

    Do While strFile <> ""
    If strFile <> "." And strFile <> ".." Then
    If (GetAttr("C:\dbtemp\" & strFile) And vbDirectory) <> vbDirectory Then
    .Attachments.Add "C:\dbtemp\" & strFile
    End If
    End If
    Loop

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    Dim fso As Object, SourceFolder As Object, SourceFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder("C:\dbtemp\")
    For Each SourceFile in SourceFolder.Files
    .Attachments.Add SourceFolder.Path & "\" & SourceFile.Name
    Next
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Yes! That was the missing piece! Consider this thread solved! Posting final working code which does the following:

    1. creates a temp folder on c:
    2. Saves all files from 'Attachments' field to the temporary folder
    3. creates an email
    4. attaches all files from temp folder to the email
    5. sends email
    6. then deletes entire temp folder from c:

    ...all with the click of one command button!

    _______

    Private Sub Command962_Click()

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim OutlookAttach As Outlook.Attachment
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    Dim db As DAO.Database
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Set db = CurrentDb
    Set rsParent = Me.Recordset
    rsParent.OpenRecordset
    Set rsChild = rsParent.Fields("Attachments").Value
    While Not rsChild.EOF
    If Dir("C:\dbtemp", vbDirectory) = "" Then
    MkDir ("C:\dbtemp")
    Else
    'do nothing for the "C:\dbtemp" directory already exists
    'MsgBox "C:\dbtemp\ directory already exists"
    End If
    rsChild.OpenRecordset
    rsChild.Fields("FileData").SaveToFile ("c:\dbtemp\")
    rsChild.MoveNext
    Wend

    With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    .CC = " "
    .Subject = "test"
    .HTMLBody = "test"
    Dim fso As Object, SourceFolder As Object, SourceFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder("C:\dbtemp\")
    For Each SourceFile In SourceFolder.Files
    .Attachments.Add SourceFolder.Path & "\" & SourceFile.Name
    Next
    'Send email
    '.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
    .Send
    Kill "C:\dbtemp\*.*" ' delete all files in the folder
    RmDir "C:\dbtemp\" ' delete folder
    End With
    'MsgBox MailOutLook.Body
    Exit Sub
    email_error:
    MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
    Resume Error_out
    Error_out:
    End Sub

  12. #12
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    one more thing.....

    How would I go about attaching a file stored on a network share location using UNC instead of a network drive mapping (i.e. \\network folder\file.*)?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Good question.
    The syntax is: \\servername\path\filename
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    thats it! As always...you've been a GREAT help! Thanks.

  15. #15
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    One other tweak to this......

    What syntax would I use to insert a specific email address into the CC field, based on the contents of a specific text field. i.e If <text box> = < " " > Then insert <email address> Hope Im explaining that right. : )

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Attach Report to Email
    By teebumble in forum Reports
    Replies: 5
    Last Post: 11-28-2012, 05:42 PM
  2. Replies: 13
    Last Post: 05-23-2012, 12:30 AM
  3. How do you bulk attach files in Access?
    By newyorkyankee in forum Access
    Replies: 5
    Last Post: 05-11-2012, 01:06 PM
  4. Can't attach to email
    By newtoAccess in forum Access
    Replies: 10
    Last Post: 12-03-2010, 02:10 PM
  5. Attach Email to Form
    By Huddle in forum Access
    Replies: 2
    Last Post: 07-22-2010, 12:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums