Attach linked files

[DBL]

I have a table that stores file paths as hyperlinks to linked documents, which in turn are attached to records in another table in a one to many relationship. There is a report that is produced based on the records table which is emailed to the customer. Is it possible to attach any linked files as attachments to the email also?

 

[charityg]

The only way of doing this that I know of is to use the Outlook Application object, which basically means that you build the actual Outlook email including recipients, subject, attachments, message, etc. with code and then send.

This is the basic framework for utilizing the Outlook object

Private Sub cmdSendEmail_Click()

    'Don't forget to set a reference to the Outlook object library 8.0
    'Use the Object browser to see the exposed objects.
    Dim MailApp As Outlook.Application
    Dim NewEmail As MailItem
    Set MailApp = CreateObject("Outlook.Application")
    Set NewEmail = MailApp.CreateItem(olMailItem)
    
    Dim atts As Outlook.Attachments
    Dim newAttachment As Outlook.Attachment



    With NewEmail
        
        .To = "your recipient"
        .Subject = "your subject"
        .Body = "This message was generated by an " & Chr(13) _
                & "access event." & Chr(13) _
                & "What do you think?" & Chr(13) _
                & "It uses the exposed objects of outlook" & Chr(13) _
                & "to create and send the message."
        Set atts = .Attachments
        'here you would want to open a recordset and loop though all attachments that should be added
        'the first parameter is the file path and name, I always have used olByValue as the
        'second parameter, the third parameter is optional, and defines the position of the attachment,
        '(first, second, third, etc.) the last parameter is also optional if you want to display
        ' a name for the attachment other than the file name
        'I'm only pseudo-coding the loop process, because I am assuming you know how to do this.
        'let me know if you don't
        'Do until recordset.eof
            Set newAttachment = atts.Add(recordset.fields("HyperlinkPath"), olByValue, 1, "My Attachment Name")
        'loop
        .Send
        End With
    Set NewEmail = Nothing
    Set MailApp = Nothing
    
End Sub

 

[DBL]

Thanks for this. I'll give it a go and see how I get on. If I get stuck (probably) I'll come back.

Dawn