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)
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.
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
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.
gotcha. So would I also need to remove the 'next' command if I do that?
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.
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.
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.
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
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.
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.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
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
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.
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.
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
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?
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.