PDF & Email

ashani

Active Member
Joined
Mar 14, 2020
Messages
347
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Here is a thread worth checking out.
 
Upvote 0
Thank you
However I still want to know how to save on both locations - temp folder and also Z drive network folder.
 
Upvote 0
Ok. There are other ways of doing this, but it might be easier to talk you through how to identify the relevant code so that you can customize it exactly how you want it. In that helpful code located by Davesexcel, there are two lines of code in particular that you'll need - one which sets the filepath/filename, and one which exports to PDF. Can you see which two lines I'm referring to?
 
Upvote 0
Ok. There are other ways of doing this, but it might be easier to talk you through how to identify the relevant code so that you can customize it exactly how you want it. In that helpful code located by Davesexcel, there are two lines of code in particular that you'll need - one which sets the filepath/filename, and one which exports to PDF. Can you see which two lines I'm referring to?
I'm not the OP
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top