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


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


This question generated 24 answers. To proceed to the answers, click here.

This thread is current as of March 17, 2014.


For more resources for Microsoft Excel