VBA creates PDF attachment & saves but does not generate the email and attach

ralucatarsana 

As far as I can understand, the code is only exporting the active sheet as PDF, not all the sheets.

To export all the sheets starting from sheet 4, you need to loop through them and use the Worksheet.ExportAsFixedFormat method instead of ActiveSheet.ExportAsFixedFormat.

You also need to change the PDF filename for each sheet to avoid overwriting the same file.

Here is a modified version of your code that should work (not tested :):

Sub EmailPDF()

Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Dim ws As Worksheet

' Not sure for what the Title is
Title = Range("M3")

' 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 attachments
With OutlApp.CreateItem(0)

' Prepare e-mail
.Subject = Title
.To = Range("A1").Value
.Body = "Hello! Please see attached the annex for utility invoice." & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf

' Loop through sheets starting from sheet 4 and export them as PDF
For Each ws In Worksheets

If ws.Index >= 4 Then

' Define PDF filename based on sheet name
PdfFile = ws.Name & ".pdf"

' Export worksheet as PDF
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

' Attach PDF file to e-mail
.Attachments.Add PdfFile

End If

Next ws

' 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 files after sending e-mail
For Each ws In Worksheets

If ws.Index >= 4 Then

PdfFile = ws.Name & ".pdf"
Kill PdfFile

End If

Next ws

' 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

Hope I was able to help you :).

NikolinoDE

I know I don't know anything (Socrates)