Exceluser2014
New Member
- Joined
- Mar 17, 2014
- Messages
- 5
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
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.
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