how to add attachments to email using subform field to for attachtment path

If there's need to see your db I'll let you know, at the moment it's not necessary.
sspreyer said:
I can't add Option Explicit at the top as it then causes error with other code within the form
Click to expand...
Add it and fix the errors, then come back to this code and look carefully at what you're writing.
 
vbaInet said:
If there's need to see your db I'll let you know, at the moment it's not necessary.
Add it and fix the errors, then come back to this code and look carefully at what you're writing.
Click to expand...

have done what you said quoted above WOW!!! didn't realise how many errors

but still not working run time error 3464 data type mismatch

here is the code

Code: 
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
Dim rstFiltered As DAO.Recordset
Dim strteamid As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
  Dim db As DAO.Database
 Dim rstAttachments As DAO.Recordset
With olMail
    .BodyFormat = olFormatHTML
    .To = "" ' Left
    .CC = ""
    .Subject = "test for VbaInet"
    .Body = "VbaInet Help Me "
     'Add Attachments
  
    Set db = CurrentDb()
    
      Set rstAttachments = db.OpenRecordset("SELECT * FROM tblteaminformer INNER JOIN attachments ON tblteaminformer.TeamID = attachments.linkattatchment")
    
   strteamid = rstAttachments!TeamID
  rstAttachments.Filter = "teamid = '" & strteamid & "'"
   [COLOR=red][B] Set rstFiltered = rstAttachments.OpenRecordset
[/B][/COLOR]    
    
    If rstAttachments.RecordCount > 0 Then
        With rstAttachments
            Do Until .EOF
         olMail.Attachments.Add (rstAttachments!txtaddress)
                
                .MoveNext
            Loop
        End With
    End If
     .Save
    .Display
End With
Set olMail = Nothing
Set objOutlookAttach = Nothing
Set olApp = Nothing
Set rstAttachments = Nothing
Set db = Nothing
End Sub
 
Make a habit to declare all your variables and always declare Option Explicit. These are some of the things that cause big problems with beginners.

Instead of all that code, remember that your subform is bound to a recordset. Use the recordsetclone of your subform. Research RecordsetClone.
 
vbaInet said:
Use the recordsetclone of your subform. Research RecordsetClone.
Click to expand...

sorry vbaInet I m lost completely and why over my head

here my code now still no good and the same error as before

Code: 
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
Dim rstFiltered As DAO.Recordset
 Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
Dim rst As DAO.Recordset
  Dim db As DAO.Database
 Dim rstAttachments As DAO.Recordset
  Dim strSearchName As String
With olMail
    .BodyFormat = olFormatHTML
    .To = "" ' Left
    .CC = ""
    .Subject = "test for VbaInet"
    .Body = "VbaInet Help Me "
     'Add Attachments
  
    Set db = CurrentDb()
    
      Set rstAttachments = db.OpenRecordset("SELECT * FROM tblteaminformer INNER JOIN attachments ON tblteaminformer.TeamID = attachments.linkattatchment")
    Set rst = Me.RecordsetClone
    strSearchName = Str(Me!TeamID)
  
  rstAttachments.Filter = "teamid = '" & TeamID & "'"
   [COLOR=red][B] Set rstFiltered = rstAttachments.OpenRecordset[/B][/COLOR]
    
    
    If rstAttachments.RecordCount > 0 Then
        With rstAttachments
            Do Until .EOF
         olMail.Attachments.Add (rstAttachments!txtaddress)
                
                .MoveNext
            Loop
        End With
    End If
     .Save
    .Display
End With
Set olMail = Nothing
Set objOutlookAttach = Nothing
Set olApp = Nothing
Set rstAttachments = Nothing
Set db = Nothing
End Sub
 
Work from this:
Code: 
    Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.SubformName.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "sspreyer@stanford-le-hope.co.uk"
        .CC = ""
        .Subject = "sspreyer take your time!"
        .Body = "sspreyer, you're getting there"
        
        ' Add Attachments
        With rstAttach
            Do While Not .EOF
                olMail.Attachments.Add !PathField
                DoEvents
                .MoveNext
            Loop
        End With
        
        .Display
'        .Save
'        .Send
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
    Set rstAttach = Nothing
 
right I think I'm closer runtime error 3252 operation not support for this type of object

Code: 
   Dim olApp       As Outlook.Application
    Dim olMail      As Outlook.MailItem
    Dim olAttach    As Outlook.Attachment
    Dim rstAttach   As DAO.Recordset
     
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With Me.attachments_subform.Form
        ' Commit changes (if necessary)
        If .Dirty Then .Dirty = False
        
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
    End With
    
    ' Compose email, attach docs and send
    With olMail
        .BodyFormat = olFormatHTML
        .To = "[EMAIL="sspreyer@stanford-le-hope.co.uk"]sspreyer@stanford-le-hope.co.uk[/EMAIL]"
        .CC = ""
        .Subject = "sspreyer take your time!"
        .Body = "sspreyer, you're getting there"
        
        ' Add Attachments
        With rstAttach
            Do While Not .EOF
                [B][COLOR=red]olMail.Attachments.Add !txtaddress[/COLOR][/B]
                DoEvents
                .MoveNext
            Loop
        End With
 
You should be able to debug this. I specifically put PathField but you changed it to the name of the textbox. PathField meaning, the name of the field that contains the path to the file.
 
vbaInet said:
You should be able to debug this. I specifically put PathField but you changed it to the name of the textbox. PathField meaning, the name of the field that contains the path to the file.
Click to expand...

right... but txtaddress is the field name that holds the file path sorry i must be getting on you nerves now .
 
A field name with a prefix of "txt", are you sure?
Nope, not yet ;)
 
Ok, do this:
Code: 
                strPath = !txtAddress
                olMail.Attachments.Add strPath, olByValue
where strPath is declared as a String variable.
 
vbaInet said:
Ok, do this:
Code: 
                strPath = !txtAddress
                olMail.Attachments.Add strPath, olByValue
where strPath is declared as a String variable.
Click to expand...


WOW !!!!!!!!thanks it works only one issue for some reason if I go to the next record with no path in txtaddress field and run the code the email open's with no attachment which is correct but then if go back to a record with attachments and run the code again it attaches nothing even though there is file path's in the field. if I close the form and reopen it works perfect very strange :confused:

plus just found out if I run the code a second time it attaches nothing :confused:

View attachment test file 12.zip
 
Last edited:
vbaInet said:
Close the mail item window before you re-run the code.
Click to expand...

I have done that doesn't make a different what a weird fault :confused:
can you see it does the same for you I attached last copy of my db in the last post would it be anything to do with office 365?
 
The recordset is at the eof so move it back up.
Code: 
        With rstAttach
            If .RecordCount > 0 Then
                .MoveFirst
                Do While Not .EOF
                    strPath = !txtAddress
                    olMail.Attachments.Add strPath, olByValue
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
 
Thanks VbaInet for your patience (man of steel) got there in end

Legend!!!!!!!!!!!


thanks again:D

Time for Bed I think!

Shane
 
You're welcome!

In fact, you should move that check here:
Code: 
        ' Fetch your subform's recordset
        Set rstAttach = .RecordsetClone
        If rstAttach.RecordCount < 1 Then Exit Sub
And keep the other looking like:
Code: 
        With rstAttach
            .MoveFirst
            Do While Not .EOF
                strPath = !txtAddress
                olMail.Attachments.Add strPath, olByValue
                DoEvents
                .MoveNext
            Loop
        End With
 
You must log in or register to reply here.

Similar threads

N
Solved How to add an attachment in Gmail managed in MS Access VBA
Replies
7
Views
436
nector
N
T
Attache to email from attachment Field
Replies
2
Views
371
theinviter
T
C
  • Question Question
Adding outlook.recipient values to an Access table
Replies
8
Views
339
Pat Hartman
P
N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
559
sonic8
S
T
Solved Attache files from Subform in main form to Outlook
Replies
11
Views
215
theinviter
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom