Links with hash (#) in the url

[arc3636]

I have a database that manages documentation (via links to a shared drive) related to claims. The user creates a claim and can then attach related documents (word, pdf, msg, etc) in the database. After selecting documents from a combo box, a button will attach those to an email with a summary of the claim.

This works well enough until a document comes in with a # symbol in the file name (which is unfortunately happening more often than not). My group doesn't have write access to the folders, so we can't easily remove the special character from the file names.

I was able to convert my VBA script to use the Shell command, which works well enough to open the files from the database.

    For Each Selected_Attachments In l_attchmnt.ItemsSelected
        'OLD --- Does not work
        'Application.FollowHyperlink (l_attchmnt.Column(1, Selected_Attachments)), "", False
        
        'NEW --- Works
        Shell ("C:\Program Files\Internet Explorer\iexplore.exe " & l_attchmnt.Column(1, Selected_Attachments))
        
    Next Selected_Attachments

However, I haven't been able to come up with a workaround for attaching the files onto an email. Code is below, whenever I run it I get "Run-time error '2424' The expression you entered has a field, control, or property name that Microsoft Office Access can't find." Anyone know of any other ways to attach a file to an email via VBA?

    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
        .BodyFormat = olFormatHTML
        .HTMLBody = "Please respond to the attached claim"
        .Subject = "Claim " & clm_nbr
        .To = email_to
        
        For Each Selected_Attachments In l_attchmnt.ItemsSelected
            'Error happens here
            .Attachments.Add l_attchmnt.Column(1, Selected_Attachments)
        Next Selected_Attachments
        
        .Display
    End With

 

[spikepl]

I would test it with a hardwired filename of the offending type in the code, to verify that the attachment method indeed cannot devour a # in the file name. If that is the case, then I'm afraid that this is the end, unless you either get OK to rename the files or induce the producers not to use #

 

[arc3636]

Thanks for your response. It does pull the attachment correctly when the path is fully hard coded. I wonder if my problem has something to do with the path being pulled from a list box...

 

[arc3636]

It does seem to be working when I assign the path to a string variable first!

    Dim path As String
    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
        .BodyFormat = olFormatHTML
        .HTMLBody = "Please respond to the attached claim"
        .Subject = "Claim " & clm_nbr
        .To = email_to
        
        For Each Selected_Attachments In l_attchmnt.ItemsSelected
            path = l_attchmnt.Column(1, Selected_Attachments)
            .Attachments.Add path
        Next Selected_Attachments
        
        .Display
    End With

 

[spikepl]

I recalled having issues when using a Variant to feed the Attachments.Add , which is why I wanted you to test a hardcoded string. Good it worked out.