Solved

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
9
186 Views
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 _
            ("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
Comment
Question by:jazjef
9 Comments
 
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?

Cheers!
0
 
LVL 4

Author Comment

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


0
 
LVL 65

Expert Comment

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



eg

With .Attachments.Add _
            GetD2("C:\myfile.xls")
            .DisplayName = "Job Request"
       End With
0
 
LVL 4

Author Comment

by:jazjef
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....

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Author Comment

by:jazjef
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
                   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
 
LVL 65

Expert Comment

by:rockiroads
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
0
 
LVL 4

Author Comment

by:jazjef
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.....
0
 
LVL 1

Accepted Solution

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

DarthMod
CS Moderator
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now