• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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 _
            ("C:\TEST5_.pdf")
            .DisplayName = "Job Request"
       End With


Here's what I want:

        With .Attachments.Add _
            (Excel.Range("D2"))
            .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....
0
jazjef
Asked:
jazjef
1 Solution
 
David LeeCommented:
Hi jazjef,

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

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


0
 
rockiroadsCommented:
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
    objXL.Quit
    Set objWB = Nothing
    Set objXL = Nothing
end function



eg

With .Attachments.Add _
            GetD2("C:\myfile.xls")
            .DisplayName = "Job Request"
       End With
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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....

0
 
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
                   Else
                    Exit Sub

      End If

        'Send the mail message
        .Send
    End With

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

End Sub



0
 
rockiroadsCommented:
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
0
 
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.....
0
 
DarthModCommented:
PAQd, 500 points refunded.

DarthMod
CS Moderator
0
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now