Excel Macro (VBA) sending active worksheet in PDF by Outlook

[Exceluser2014]

Dear all,

Can anyone help me with this problem. I find out a code to create a PDF (with opening the Save As dialog box) from an active worksheet, but I can't find out how to send this PDF by e-mail (Outlook). The code is working till the words 'Set OutApp'.

Please can anyone help me? Just what I want is to send the active worksheet as PDF (as attachment) by email (Outlook). Here the present code.

Sub SendPDF()
'
' SendPDF Macro
'
    Dim OutApp As Object
    Dim OutMail As Object
    Dim v As Variant
    v = Application.GetSaveAsFilename(Range("E2").Value, "PDF Files (*.pdf), *.pdf")
         
    If VarType(v) <> vbString Then Exit Sub
     
    If Dir(v) <> "" Then
        If MsgBox("File already exists - do you wish to overwrite it?", vbYesNo, "File Exists") = vbNo Then Exit Sub
    End If
     
    With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, FileName:=v, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, From:=1, To:=3, OpenAfterPublish:=False
    End With
         
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add v
        .Send
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Thanks in advance for all your help and suggestions.

Regards,

Gerben

 

[wigi]

Here is code, please have a look in detail.

Sub PDF_and_email_Wigi()    

    c00 = Environ("Temp") & "\Schedule.pdf"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=c00
    
    On Error Resume Next
    With CreateObject("Outlook.Application").createitem(0)
        .To = ""
        .Subject = "Report"
        .Body = "Body"
        .Attachments.Add c00
        .Send
    End With
    On Error GoTo 0
    
    Kill c00

End Sub

 

[micfly]

How do I do that? Sorry, I don't know VBA, just trying to piece this together.

 

[wigi]

Are you now saying that with 500 posts here, you cannot run this macro?
What happens if you fill in the .To address in the code and execute it?

 

[micfly]

We must have been posting at the same time. I did not see your previous post on my last reply.
500 posts? geez... Anyway, this is the first code I have seen that actually does what I was asking for!!! Thanks!!!