Solved Attache files from Subform in main form to Outlook

[theinviter]

Dears;

I need your kind support to modify the below to get the attachment from subforum in maniform.
Main form = Holiday_Dialysis
Subform = Documents
attachment filed = Sigend_Form

as i get error in " If Me.Documents.Form.Recordset.Fields("Sigend_Form").AttachmentCount > 0 Then"
the complete code below.

Private Sub AttachToCollection()

    Dim rsAttach As DAO.Recordset2

    Dim sPath As String

    Dim sFile As String

    Dim i As Integer





    sPath = Environ$("Temp") & "\"

 

 

    Set colAttach = New Collection



 

    If Me.Documents.Form.Recordset.Fields("Sigend_Form").AttachmentCount > 0 Then

      

         Set rsAttach = Me.Documents.Form.Recordset.Fields("Sigend_Form").Value

    

        With rsAttach

        

            If Not (.BOF And .EOF) Then

                .MoveFirst ' Move to the first record

                Do Until .EOF ' Loop through all attachments

                    i = i + 1 ' Increment counter for collection key

                    sFile = sPath & .Fields("FileName") ' Construct full file path

                

                  

                    If Len(Dir$(sFile)) > 0 Then

                        Kill sFile

                    End If

                

                

                    colAttach.Add sFile, i & ""

                

                

                    .Fields("FileData").SaveToFile sFile

                

                    .MoveNext ' Move to the next attachment

                Loop

            End If

            .Close ' Close the attachment recordset

        End With

    End If

End Sub

 

[CJ_London]

Please use the code button - <> - to post your code, this will preserve indentations and make it readable

 

[theDBguy]

as i get error in

What was the error message?

 

[theinviter]

What was the error message?

" If Me.Documents.Form.Recordset.Fields("Sigend_Form").AttachmentCount > 0 Then"

 

[theDBguy]

View attachment 120454
" If Me.Documents.Form.Recordset.Fields("Sigend_Form").AttachmentCount > 0 Then"

Can you post a sample db with test data?

 

[sonic8]

as i get error in " If Me.Documents.Form.Recordset.Fields("Sigend_Form").AttachmentCount > 0 Then"

None of the DAO.FieldX classes has an AttachmentCount property. - That's what the error message is trying to tell you.

Isn't it enough to just check for:

Not .....Fields("Sigend_Form").Value Is Nothing

?

 

[arnelgp]

you can test this:

Private Sub AttachToCollection()

    Dim rsAttach As DAO.Recordset2
    Dim sPath As String
    Dim sFile As String
    Dim i As Integer
    Dim bm As Variant
    
    bm = Null
    sPath = Environ$("Temp") & "\"
    Set colAttach = New Collection

    With Me!Documents.Form.Recordset
        If Not (.BOF And .EOF) Then
            bm = Me!Documents.Form.Bookmark
            .MoveFirst
        End If
        Do Until .EOF
            Set rsAttach = .Fields("Sigend_Form").Value
        
            With rsAttach
                If Not (.EOF) Then
                    .MoveFirst
                    Do Until .EOF
                        i = i + 1 ' Increment counter for collection key
                        sFile = sPath & .Fields("FileName") ' Construct full file path
                        If Len(Dir$(sFile)) > 0 Then
                            Kill sFile
                        End If
    
                        colAttach.Add sFile, i & ""
    
                        .Fields("FileData").SaveToFile sFile
                        .MoveNext
                    Loop
                End If
            End With
            rsAttach.Close: Set rsAttach = Nothing
            .MoveNext
        Loop
    End With
    If Not IsNull(bm) Then
        Me!Documents.Form.Bookmark = bm
    End If
End Sub

 

[theinviter]

you can test this:

Private Sub AttachToCollection()

    Dim rsAttach As DAO.Recordset2
    Dim sPath As String
    Dim sFile As String
    Dim i As Integer
    Dim bm As Variant
   
    bm = Null
    sPath = Environ$("Temp") & "\"
    Set colAttach = New Collection

    With Me!Documents.Form.Recordset
        If Not (.BOF And .EOF) Then
            bm = Me!Documents.Form.Bookmark
            .MoveFirst
        End If
        Do Until .EOF
            Set rsAttach = .Fields("Sigend_Form").Value
       
            With rsAttach
                If Not (.EOF) Then
                    .MoveFirst
                    Do Until .EOF
                        i = i + 1 ' Increment counter for collection key
                        sFile = sPath & .Fields("FileName") ' Construct full file path
                        If Len(Dir$(sFile)) > 0 Then
                            Kill sFile
                        End If
   
                        colAttach.Add sFile, i & ""
   
                        .Fields("FileData").SaveToFile sFile
                        .MoveNext
                    Loop
                End If
            End With
            rsAttach.Close: Set rsAttach = Nothing
            .MoveNext
        Loop
    End With
    If Not IsNull(bm) Then
        Me!Documents.Form.Bookmark = bm
    End If
End Sub

HI;
this work fine but it does not attach the file. as the form ID is linked with subforum.
can you please advise how to solve it.

thanks

 

[arnelgp]

If the subform has Link Master/Child Fields, it will work.
you have a Collection Object there (that contains the Path+filename), use
it on your code to as Attachment:

..
    For i = 1 To colAttach.Count
        objMail.Attachments.Add colAttach(i & "")
    Next

 

[theinviter]

If the subform has Link Master/Child Fields, it will work.
you have a Collection Object there (that contains the Path+filename), use
it on your code to as Attachment:

..
    For i = 1 To colAttach.Count
        objMail.Attachments.Add colAttach(i & "")
    Next

thanks Sir, it was working fine. but what if there is no attachment get error message in "Kill sFile", can you please advise how to avoid as i want if no attachment then proceed.

 

[arnelgp]

change this portion to:

..
                    Do Until .EOF
                        sFile = sPath & .Fields("FileName") ' Construct full file path
                        If sPath <> sFile Then
                            i = i + 1 ' Increment counter for collection key
                            If Len(Dir$(sFile)) > 0 Then
                                Kill sFile
                            End If
                       
                            colAttach.Add sFile, i & ""
   
                            .Fields("FileData").SaveToFile sFile
                        End If
   
                        .MoveNext
                    Loop

...

 

[theinviter]

change this portion to:

..
                    Do Until .EOF
                        sFile = sPath & .Fields("FileName") ' Construct full file path
                        If sPath <> sFile Then
                            i = i + 1 ' Increment counter for collection key
                            If Len(Dir$(sFile)) > 0 Then
                                Kill sFile
                            End If
                      
                            colAttach.Add sFile, i & ""
  
                            .Fields("FileData").SaveToFile sFile
                        End If
  
                        .MoveNext
                    Loop

...

thanks alot work fine.