Attatching an already embedded PDF to an Email.

C

CBurgess

Board Regular
Joined
Oct 29, 2013
Messages
65
Okay, my problem is as follows:

My "Drawing" is embedded on Sheet1, later on in Sheet12 I click a button that'll send an automatic email to a user. I want to attatch the embedded PDF onto this email if it is at all possible??

Code: 
  Dim Drawing As Object

After I've declared my variables I've tried setting the variable "Drawing" to the object, this is where I'm encountering my errors mainly "Type Mismatch" or Object Variable not set...


Code: 
    Sheets("Menu").Visible = True
    Drawing = Sheet1.Shapes.range(Array("Drawing"))
    Selection.Verb Verb:=xlPrimary
    Sheets("Menu").Visible = False

At the bottom of the code, this is where i pull in the object

Code: 
        .To = Sheet12.range("L8")
        .CC = ""
        .BCC = ""
        .Subject = "Arrange P&D Request"
        .htmlbody = strbody & vbNewLine & Signature
        .Attachments.Add ("Drawing")

Cheers guys, you're help is appreciated.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Try this! :)

Code: 
Sub epuron()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
'automatically saves file as a PDF
        fName = Application.GetSaveAsFilename("", "PDF Files (*.pdf), *.pdf")
        ActiveSheet.ExportAsFixedFormat xlTypePDF, fName, xlQualityStandard, , , , , True
    On Error Resume Next
   ' Change the mail address and subject in the code before you run it.
    With OutMail
        .To = " 'input email between quotation marks to the person receiving "
        .CC = ""
        .BCC = ""
        .Subject = " 'input subject title between quotation marks "
        .Body = " 'input body message between quotation marks "
        .Attachments.Add ActiveWorkbook.FullName
        .Attachments.Add ("C:\locationhere.txt") 'change location to were the pdf is saved at
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Last edited:
Upvote 0
Sorry, I should of stated better, I don't want Outlook to pull the email from an external location. i want it to work from the already embedded PDF. Users won't have server access to where the original document is stored.
 
Upvote 0
No sorry haha, The PDF is embedded on a sheet. So the code

Code: 
.Attachments.Add ("C:\locationhere.txt")

Surely wouldn't work? If a user who has no access to the server where the file is stored, uses my workbook they won't be able to pull the file.

So i've embedded the PDF onto EXCEL, and I want to upload that file that's already been embedded via outlook to accompany an email.
</pre>
 
Upvote 0
OOOOOOH alright so... You want to share a excel file to a company and the pdf file is already saved into a excel file?
 
Upvote 0
Alright if your using outlook then here

Rich (BB code): 
Sub epuron()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
'automatically saves file as a xlsx
        fName = Application.GetSaveAsFilename("", "PDF Files (*.xlsx), *.xlsx")
        ActiveSheet.ExportAsFixedFormat xlTypePDF, fName, xlQualityStandard, , , , , True
    On Error Resume Next
   ' Change the mail address and subject in the code before you run it.
   ' sends message with attachment for outlook
    With OutMail
        .To = " 'input email between quotation marks to the person receiving "
        .CC = ""
        .BCC = ""
        .Subject = " 'input subject title between quotation marks "
        .Body = " 'input body message between quotation marks "
        .Attachments.Add ActiveWorkbook.FullName
        .Attachments.Add ("C:\locationhere.xlsx") 'change location to were the xlsx is saved at
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Now for the location just locate were you saved your excel file then were you see the address bar on the window click it and copy that and paste it on the red part

Rich (BB code): 
.Attachments.Add ("C:\locationhere.xlsx")

Also don't forget to add the extension of the file! I am not sure if your file is macro enabled or not.
 
Upvote 0
Yeah it's company email, so ours is outlook.

The email function works completly fine, I just want to send an accompanying datasheet along with the email to make things easier for the user. I know how to link an embedded PDF onto a command button, but I'm not sure how to attatch it to an email?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
204
GarnesGambit
G
C
  • Question
Incorporating a UserForm in place of multiple InputBoxes
Replies
2
Views
149
Fluff
Fluff
S
  • Question
VBA Code hangs on Outlook Send?
Replies
2
Views
353
shophoney
S
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
419
josros60
J
J
  • Solved
Email body not showing using html.
Replies
3
Views
591
Logit
Logit

Forum statistics

Threads
1,214,665
Messages
6,120,803
Members
448,990
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top