Why doesn't my Code work??? Creating a PDF, attaching PDF to email and sending

[matthec2]

Hello,

I am having trouble with the below code, wondering if anyone can help me.

I found the original solution on an Excel website; I changed it to what I require.

I am trying to select a few worksheets, save as a PDF, then email it to myself.

Funny thing is that this code has worked before for several weeks.

The code keeps crashing at the line ".Attachments.Add PdfFile"

I can see the PDF being created in the Windows folder, but doesn't then get attached to the email. As if the file name or location is wrong?

My assumption is that there is a problem with the PdfName or Title lines - these are the parts of the code I am unfamiliar with.

Any help much appreciated!

Sub TargetingEmailPDF()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename
  PdfFile = ""
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & Range("TargetingEmail").Value & ".pdf"
 
  ' Export activesheet as PDF
  Sheets(Array("Targeting-1", "Targeting-2")).Select
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
    Sheets("LIST").Select
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Range("TargetingEmail").Value
    .To = Range("EmailAddressTo").Value ' <-- Put email of the recipient here
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "The report is attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .[B]Attachments.Add PdfFile
   [/B]
    ' 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
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
End Sub

 

[than_gold]

you need add the path to add those pdf files.

 

[matthec2]

Thanks for the response, I assumed that was the case, just odd how it has worked previously then has stopped working.

Saving the PDF seems ok as it does appear in the folder. It is the "attachment" line where it doesn't seem to find the location.

Can you amend my code above to show a brief example. The master file is located in our company shared P Drive, about 4 sub folders down.

Many Thanks!

 

[matthec2]

Actually, I've managed to fix this temporarily.

I found that when I copied the file to my desktop, the coding worked. Then I copied the file back to the relevant sub folder.

The issue is definitely in the file location section. Seems that the code works when you initially create the file, but it doesn't like being disturbed.

Hopefully I can work this out when it happens again in the future!