Solved Attatching File on an email sent through Excel

June 25, 2018 at 08:05:14
Specs: Windows 10
Hi all,

I have finalized the code for sending my email out from excel thanks to the help of DerbyDad already. My next question is how can I attach a file like a pdf/word or an excel document into my excel vba for emailing.

In an ideal world, I would create a sheet within my workbook with titles of different trades. Be able to drag and drop documents under the titles and then the macro to search for the attachments (for user friendly interface) So for instance, if the email macro was to target carpentry, I would like the macro to find all documents under the carpentry title on another sheet. Then automatically add them to the email when the macro is run. Please tell me if this is possible.

Please note: Not all emails will be the same and will not require the same attachments:

Please see the code already written that I already have.

Sub SendEmail()
' SendEmail Macro
Dim olMail As Object
Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")

'Search "Enquire Column"

  With Range("table29[stage]")
     Set e = .Find("enquired", Lookat:=xlPart)
'Send email when "enquired" found
       If Not e Is Nothing Then
         firstAddress = e.Address
             Set olMail = olApp.CreateItem(olMailItem)
               olMail.To = e.Offset(0, 1)
               olMail.subject = sheet8.Range("F2") + " Enquiry, " + sheet8.Range("F3")
               olMail.HTMLBody = "Hi," 
               olMail.Importance = olImportanceHigh
               olMail.ReadReceiptRequested = True
             Set olMail = Nothing
'Find Next "enquire"
             Set e = .FindNext(e)
               If e Is Nothing Then
                 GoTo DoneFinding
               End If
          Loop While e.Address <> firstAddress
       End If

  End With
'Clean up Object
             Set olApp = Nothing
End Sub

See More: Attatching File on an email sent through Excel

Report •

June 26, 2018 at 04:07:08
✔ Best Answer
There is an Attachments.Add instruction that can be used, but it requires an argument that contains the Path to the file. e.g.

strLocation = "C:\Users\Desktop\Carpentry\Table Saw.docx"
        .Attachments.Add (strLocation)

I don't quite understand what the code would be searching for in order to obtain that argument. Can you explain your "drag and drop" process in a little more detail? What actually ends up in the spreadsheet?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •

June 26, 2018 at 08:01:23
Hi DerbyDad,

Never mind, my idea was to be able to have section of a sheet which can hold pdf files. just like how you can insert an object and use a pdf file. except that process is long and technically pointless. I can kind of gather that this will not work anyway which will lead me to my next question. I will post my next question as a new thread in regards to the email code you have previously helped me with.

Thanks again for your time.

Report •
Related Solutions

Ask Question