Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The field is in rsParent recordset? Is the textbox null or empty string?

    .CC = Nz(Me.textboxname, rsParent!fieldname)



    or

    .CC = IIf(Me.textboxname = "", rsParent!fieldname, Me.textboxname)
    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.

  2. #17
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    sorry just now seeing this response. I decided to do something else on that part. However, I would like to see about another tweak. My current code below pulls all attachments from the attachments field and attaches them to an email. But how would I modify this to pull out one single file? The tricky part is that the filename will partially contain a persons name. Is there anyway to use a wildcard if part of the filename will always be constant?



    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

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't think wildcard will work. Far as I know, each file must be independently referenced.

    If there is same text string in all desired file names, that can be used as criteria in the code to attach only those files that contain that substring in their name. Within the For Each loop, use conditional statement:

    If InStr(SourceFile.Name, "your string") Then .Attachments.Add SourceFolder.Path & "\" & SourceFile.Name

    The 'your string' can be hard-coded in the procedure or provided by user input.
    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.

  4. #19
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    gotcha. So would I also need to remove the 'next' command if I do that?

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, the conditional statement is within the loop.

    Note, I edited by last post.
    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.

  6. #21
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Perfect! And is there also a way to populate Outlook's FROM field when sending email. I tried: .FROM = "email" but its not a recognized command like the .To = is.

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, as far as I know, it will presume the FROM address by association with Outlook on the user's computer.
    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. #23
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    Hello again,

    for some readon my code that I said was working all this time has somehow stopped working. Im now getting a runtime error and the debugger fails at the line in bold. I thought it had something to do with the references but all of the required ones are still checked. Any ideas?

    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

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is the exact error message?

    I don't know why that line would error. What change could have recently taken place - new server, new computer, new software?

    Same problem from another computer? Want to provide db for analysis? Follow instructions at bottom of my post.

    Been a while since I last saw this code. Something I should have seen before. The line just above the SaveToFile looks unnecessary and in fact I wonder that it does not error.

    BTW, your code would be easier to read with indentation and posting between code tags.
    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.

  10. #25
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    Quote Originally Posted by tobydobo View Post
    Hello again,

    for some readon my code that I said was working all this time has somehow stopped working. Im now getting a runtime error and the debugger fails at the line in bold. I thought it had something to do with the references but all of the required ones are still checked. Any ideas?

    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
    My version of this code that works for me is on my work computer. I'll copy and paste it when I get to the office tomorrow.

  11. #26
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    This works for me. Ignore the textboxes.

    Code:
    Private Sub Command90_Click()
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim OutlookAttach As Outlook.Attachment
    Set objOL = New Outlook.Application
    Set objMail = objOL.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("Subpoena").Value
    While Not rsChild.EOF
    If Dir("C:\Users\[user]\Documents\Subpoena\", vbDirectory) = "" Then
    MkDir ("C:\Users\[user]\Documents\Subpoena\")
    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:\Users\[user]\Documents\Subpoena\")
    rsChild.MoveNext
    Wend
     
    With objMail
            .To = Me.Text127.Value
            .CC = Me.Text136.Value
            If IsNull(Me.bcc.Value) Then
            Else
            .bcc = Me.bcc.Value
            End If
            .Subject = Me.Text103.Value
            .Body = Me.Text40.Value & vbCrLf & vbCrLf & Me.Text16.Value & vbCrLf & Me.Text42.Value & vbCrLf & Me.Text43.Value & vbCrLf & Me.Text117.Value & vbCrLf & Me.Text45.Value & vbCrLf & vbCrLf & Me.Text118.Value
            .Display
    Dim fso As Object, SourceFolder As Object, SourceFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder("C:\Users\[user]\Documents\Subpoena\")
    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
     
    Kill "C:\Users\[user]\Documents\Subpoena\*.*" ' delete all files in the folder
    RmDir "C:\Users\[user]\Documents\Subpoena\" ' 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. #27
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    June, I have recently migrated to Access 2010 which I verified this code worked afterwards. I have also changed the location of the backend tables which I wouldnt believe should have any affect on the code. Its also partially a web database and has been published on a Sharepoint through Access Services. This code however is being used on my client form which cannot be published....again something i wouldnt think would have an affect on the code.

    Ganymede, thanks for posting. It appears that it almost identical to mine and the syntax is the same where mine is erroring out at. So im still a bit at a loss.

  13. #28
    tobydobo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    169
    I have posted a screencap of the error. I also thought it may be important to note that I have another button that uses the same code, except it attaches a file from an offsite network share instead of from the attachments field....this works fine.
    Attached Thumbnails Attached Thumbnails 8-15-2012 1-38-58 PM.png  

  14. #29
    Ganymede is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Dec 2011
    Posts
    64
    Since you've recently switched to a newer version, it's possible that your new 2010 version has the wrong object libraries enabled.

    https://www.accessforums.net/program...tml#post123021
    Quote Originally Posted by Ganymede View Post
    Thank you all for the help. I believe I have figured it out. The problem was that I had "Microsoft DAO 3.6 Object Library" enabled when I needed "Microsoft Office 12.0 Access database engine Object library" enabled in order to use Recordset2, field2 and Savetofile. Essentially, I had to disable DA0 3.6 and browse for ACEDAO.DLL in C:\Program Files\Common Files\microsoft shared\Office12\. Everything worked after I did that.

    Should I rename the thread to "Error using Recordset2, SavetoFile or Field2" so future posters can find the solution?

  15. #30
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you changed location of backend would have to fix table links. I presume you did that if all was working after the upgrade and move. So something else in your environment has changed.

    Did you try this code in db on your computer, not on the network data, just data locally on your computer. Maybe in a new project built with Access 2010. If still fails then the network would not appear to be source of issue. Maybe reinstall Access 2010. If that goes okay then try a new split db with a data file on network. If that works then maybe need to build a new db with Access 2010 and import everything from the old file.

    Don't think I can offer anything else.
    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.

Page 2 of 3 FirstFirst 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