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]

And is this magical pdf file onto a excel worksheet by any chance?

 

[CBurgess]

Yeap it's hidden on Worksheet1. So printing Worksheet1("MENU") will return you loads of rubbish we don't wanna send.

We just want to send the adobe document.

Possibly by declaring the variable(Drawing) equal to the object(the PDF), and then attatching the variable to the email?

 

[EPURON]

You can try this because i looked deeply through your code and it shows that your "drawing" is a worksheet when its visible.

Try adding this before it sends the attachment to the email

Set Worksheet("Drawing") = True

Then

Attachments.Add Worksheet("Drawing")

 

[CBurgess]

"Drawing" is an object?

 

[EPURON]

Then you would put

Attachments.Add Drawing.Object

If that embedded pdf file is shown as a object on Excel

 

[CBurgess]

That's cool, how we would organise "set Drawing = Sheet1.Shapes.range(Array("Drawing"))" this part? because maybe the problem is attatching the object to the variable?

 

[EPURON]

Probably but you can save the object as a pdf and send it as of here

Sub epuron()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
'makes hidden pdf visible
    Sheets("Menu").Visible = True
    Sheets("Menu").Select
    ActiveSheet.Shapes.Range(Array("Drawing")).Select
    Selection.Verb Verb:=xlPrimary
    Sheets("EXM").Select
'hides everything from worksheet and continues to display pdf
    Sheets("Menu").Visible = False
'makes the pdf object onto desktop
    If Worksheet("Drawing") = ObjectVariable Then
    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.
   ' sends message with attachment for outlook
    With OutMail
        .To = " 'the main email that the pdf will send to' "
        .CC = ""
        .BCC = ""
        .Subject = " 'input subject title between quotation marks "
        .Body = " 'input body message between quotation marks "
        .Attachments.Add ("C:\location-of-pdf-file-here.pdf")
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Test this

 

[EPURON]

Anyways im gonna go grab some lunch. I'll be back later.

 

[CBurgess]

It's still not right, adding the location adds a morale thing. I can't really adjust anyone's desktop/personal files. I like the Attatchment.Add Drawing.Object thereom but just not sure how to implement.

 

[CBurgess]

Thank you, I'll keep on playing around. It's nearly home time anyway so I'll carry on tomorrow for when the bells toll.