How do send an attachment via Outlook by referencing the attachment as a filename from a textbox rather than hardcoding the path to the file?

Here's what I have that works.... its in VBA from an Excel application activated from a button click:

        With .Attachments.Add _
            .DisplayName = "Job Request"
       End With

Here's what I want:

        With .Attachments.Add _
            .DisplayName = "Job Request"
       End With

I have a name for a file that exists in cellD2 on Sheet1.... Since the name in the cell varies, I need to grab the name from
cell D2 where the filename to be attached is located.

I am getting 'Runtime error 438'  ...  'Object doesn't support this property or method'

I know what this error means for the most part.... what I need is some 'brainstorming' for a workaround method ......

Thanks for any help you can give.... once again, I am giving big points for any assistance....
Who is Participating?
DarthModConnect With a Mentor Commented:
PAQd, 500 points refunded.

CS Moderator
David LeeCommented:
Hi jazjef,

Is "Excel" an instantiated object?  If it is, the have you tried using the Cells method of a Sheet object?

jazjefAuthor Commented:
Yes, I have tried Cells as well but with no luck.... I thought this would be easier but no luck so far.

Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

Is this to be sent from outlook or excel? not clear, and u say textbox, do u mean cell or a textbox on a user form?

if from outlook and referencing a cell then try this,

add this function somewhere and just call it to get the cell contents, passing in the full path of the filename

public function GetD2(byval sFile as String)

    Dim objXL As Object
    Dim objWB As Object

    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Open(sFile)

    GetD2 = objWB.Sheets(1).Range("D2").Value

    objWB.Close False
    Set objWB = Nothing
    Set objXL = Nothing
end function


With .Attachments.Add _
            .DisplayName = "Job Request"
       End With
jazjefAuthor Commented:
Hi rockiroads:

I see what you're getting at and I think it will work.... just to clarify:

I have an open Excel workbook, and I am trying to send the file and its path as referenced in cell D2, as an attachment to the Outlook email that launches automatically.

Your code gives me the following error with respect to GetD2:  "Argument not optional" ...
the code you posted indicates CreateObject when I already have the object opened and created. I'm probably just doing something wrong in implementing your suggestion.

In looking at your code I can see that this is better than anything I have come across yet... any further ideas? Do you need more info from me? ...thanks....

jazjefAuthor Commented:
OK... I have solved my own question. This code will take a path from Sheet1.TextBox1.Text called "C:\*.pdf" and it will search the C:\ directory for the existence of a pdf file. If there is no pdf, it will exit the sub and do nothing. When there is a pdf, it will attach it to an Outlook email and send it to the email address specified in cell D1 of Sheet1. The Kill statement will then delete the pdf from the directory...just do 'Call SendMailMessage' on a button click etc to run the code... edit to your taste.

I am posting this because I hope that it will help someone else.

Sub SendMailMessage()
    Dim ol As New Outlook.Application
    Dim ns As Outlook.Namespace
    Dim newMail As Outlook.MailItem
    Dim recRecipient1 As String
    Dim tempFilename As String
    recRecipient1 = Range("D1")
    'Return a reference to the MAPI layer
    Set ns = ol.GetNamespace("MAPI")

    'Create a new mail message item
    Set newMail = ol.CreateItem(olMailItem)
    With newMail
        'Add the subject of the mail message
        .Subject = "New job request document"
        'Create some body text
        .Body = "Here is a new job request blah, blah, blah:" & vbCrLf

        'Add a recipient and test to make sure that the
        'address is valid using the Resolve method
        With .Recipients.Add(recRecipient1)
            .Type = olTo
            If Not .Resolve Then
                MsgBox "Unable to resolve address.", vbInformation
                GoTo Release_on_error
            End If
        End With
    strSearch = Sheet1.TextBox1.Text  'Search string
    Dim strFile As String
    Dim wb As Workbook
    Dim strPath As String

    strPath = Left(strSearch, InStrRev(strSearch, "*") - 1)
    strFile = Dir(strSearch)

      If Not Dir$(Sheet1.TextBox1.Text) = "" Then
                    With .Attachments.Add _
                        (strPath & strFile)
                        .DisplayName = "Job Request"
                    End With
                    Exit Sub

      End If

        'Send the mail message
    End With

   'Release memory
    Set ol = Nothing
    Set ns = Nothing
    Set newMail = Nothing
     Kill (strPath & strFile)
    Exit Sub
    'Release memory
    Set ol = Nothing
    Set ns = Nothing
    Set newMail = Nothing

End Sub

Hi, now just looking at this

Regarding my post, I thought u was doing it from Outlook, so I did it by creating excel objects. U can get the active workbook using GetObject.

But it seems u are doing this via Excel, my apologies for not understanding your response when I asked you.

Since u have sorted it out yourself, u can post a question in CS asking for PAQ/refund
jazjefAuthor Commented:
OK... thanks for answering my inquiry anyway rockiroads... I really appreciate it. I didn't exactly make my situaiton perfectly clear with the post though anyway.....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.