PDF & Email

[ashani]

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.

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

 

[davesexcel]

Here is a thread worth checking out.

Export & Email

Hi everyone, Before I start, I just want to make it known that I'm not necessarily new to programming but I'm relatively new to VisualBasic, so bear with me! I'm trying to create a macro that will export the current sheet as a PDF file, and send it as an attachment on an email to predetermined...

www.mrexcel.com

 

[ashani]

Thank you
However I still want to know how to save on both locations - temp folder and also Z drive network folder.

 

[Dan_W]

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?

 

[davesexcel]

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

 

[Dan_W]

I'm not the OP

I didn't say that you were. I wrote:

In that helpful code located by Davesexcel,