Attatching an already embedded PDF to an Email.

[CBurgess]

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

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

    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

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

 

[EPURON]

Try this!

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

 

[CBurgess]

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.

 

[EPURON]

So... Your saying that to send the pdf to a server?

 

[CBurgess]

No sorry haha, The PDF is embedded on a sheet. So the 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>

 

[EPURON]

OOOOOOH alright so... You want to share a excel file to a company and the pdf file is already saved into a excel file?

 

[CBurgess]

I think that makes sense? Sorry for any confusion! and thank you anyway.

 

[EPURON]

And your using outlook as the email to send right? some use lotus

 

[EPURON]

Alright if your using outlook then here

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

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

 

[CBurgess]

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?