Question adding attachment

[piomoor]

Hi guys,
I don't know much about Access and building databases but overwhelmed with paperwork in my office decided to create one. Actually it's suppose to be more like a spreadsheet just to keep the record of orders. I watched hundreds of video tutorials on youtube and so far for what I need it seems to be pretty easy. Its just one thing which makes my headache and after weeks of searching internet haven't found any information. What I'm after is some kind of trick which would automatically add an attachment (or hyperlink) to the current record of my table. Another words each row of my table is a record of 1 order with unique ID (order number such as A001; A002; A003 etc.) and in specific folder I have pdf scans of the orders named the same as my unique ID of orders in my table (A001.pdf A002.pdf A003.pdf ...). I was wondering if there is a way to specify default folder for my attachments and make access to insert into table the file with the same name as record ID, for example for record with ID: A001 it would be C:\scans\A001.pdf ; for record ID: A002 C:\scans\A002.pdf etc.
Any help would be much appreciated.
Thanks, Peter

 

[cjman]

This is code I use with a form with two list boxes. I use a combo box to populate the first list box with email addresses. The combo box then populates the second list box with the reports to send matching the users with their reports. I have other fields on the form for other email fields. I have been using code like this since version 2003. Notice where the attachments are added.

Option Compare Database
Option Explicit
Dim appOutlook As New Outlook.Application
Dim lngCount As Long
Dim lngListCount As Long
Dim lst As Access.ListBox
Dim lst2 As Access.ListBox
Dim msg As Outlook.MailItem
Dim strBody As String
Dim strEMailRecipient
Dim strAttachment As String
Dim strSubject As String
Dim strTo As String
Dim msgReply As Integer
Dim varItem1 As Variant
Dim varItem As Variant

Private Sub Command64_Click()
'Created by Helen Feddema 6-30-2002
'Last modified 7-7-2002
On Error GoTo ErrorHandler
'Section by cj
msgReply = MsgBox("Are you ready to send email?", vbYesNo)
If msgReply = vbNo Then
Exit Sub
Else
End If
Set lst = Me![lstSelectContacts]
Set lst2 = Me.ListAttach
'Check that at least one contact has been selected
If lst.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one contact"
lst.SetFocus
GoTo ErrorHandlerExit
End If

'Test for required fields
strSubject = Me![txtSubject].Value
If strSubject = "" Then
MsgBox "Please enter a subject"
Me![txtSubject].SetFocus
GoTo ErrorHandlerExit
End If

strBody = Me![txtBody].Value
If strBody = "" Then
MsgBox "Please enter a message body"
Me![txtBody].SetFocus
GoTo ErrorHandlerExit
End If

'Section by cj
If lst2.ItemsSelected.Count = 0 Then
MsgBox "Please enter an attachment"
lst2.SetFocus
Exit Sub
End If

For Each varItem In lst.ItemsSelected
'Check for email address
strEMailRecipient = Nz(lst.Column(1, varItem))
Debug.Print "EMail address: " & strEMailRecipient
If strEMailRecipient = "" Then
GoTo NextContact:
End If

'Create new mail message and send to contact
Set msg = appOutlook.CreateItem(olMailItem)
With msg
.To = strEMailRecipient
.Subject = strSubject
.Body = strBody
'Add attachments Section by cj
For Each varItem1 In lst2.ItemsSelected
strAttachment = Nz(lst2.Column(0, varItem1))
msg.Attachments.Add strAttachment
Next varItem1
'.Display
.Send
End With

NextContact:
Next varItem
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub