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?

Posted on 2006-11-19
Last Modified: 2010-04-08
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....
Question by:jazjef
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 76

Expert Comment

by:David Lee
ID: 17976433
Hi jazjef,

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


Author Comment

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

LVL 65

Expert Comment

ID: 17977428
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 17979750
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....


Author Comment

ID: 17982662
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

LVL 65

Expert Comment

ID: 17982871
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

Author Comment

ID: 17984360
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.....

Accepted Solution

DarthMod earned 0 total points
ID: 18077232
PAQd, 500 points refunded.

CS Moderator

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question