Hi everyone,
I wonder if someone could please guide me. I'm looking for a VBA syntax to do the following :
1) Save the PDF document on network location
2) Save the same file in C:Temp folder
3) Email from the Temp location
4) Delete the file from Temp location
This is what I'm using but it's not seem to be working.
I wonder if someone could please guide me. I'm looking for a VBA syntax to do the following :
1) Save the PDF document on network location
2) Save the same file in C:Temp folder
3) Email from the Temp location
4) Delete the file from Temp location
This is what I'm using but it's not seem to be working.
VBA Code:
Sub Create_PDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Dim oItem As Object
Const olMailItem As Long = 0
Filesavename = ActiveSheet.Range("D7")
ChDir "Z:\Test"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filesavename, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "The checklist has been saved as PDF with the filename" & " " & Filesavename
' Define PDF filename
ChDir "C:\Temp"
PdfFile = "XYZ"
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & ".pdf"
' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
' Use already open Outlook if possible
Set OutlApp = CreateObject("Outlook.Application")
' Prepare e-mail with PDF attachment
Set oItem = OutlApp.CreateItem(olMailItem)
With oItem
' Prepare e-mail
.Subject = "XYZ"
.To = Range("O13")
.CC = Range("O14")
.CC = Range("O15")
.Body = "Hi," & vbLf & vbLf _
& "Thank you for your time today. Please find attached XYZ." & vbLf & vbLf _
& "Many thanks," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add "C:\Temp\XYZ.pdf"
' Try to send
On Error Resume Next
.send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Release the memory of object variable
Set OutlApp = Nothing
End Sub