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.
Thanks in advance for all your help and suggestions. Regards, Gerben
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
This question generated 24 answers. To proceed to the answers, click here.
This thread is current as of March 17, 2014.