Can anybody tell me why this macro only works if outlook is open? It works fine as long as i have the outlook app running, and my attempts to open the app before running this macro have failed. Specifically, it fails at 'add.attachemnt.pdf'.
Sub sendEmail()
' this sends an email
Application.DisplayAlerts = False
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Worksheets("REPORT").Visible = True
Worksheets("REPORT").Select
Title = Range("AH5")
' Define PDF filename
PdfFile = Title
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
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 = Title
.To = " emails "
.HTMLBody = "Key Indicators are as follows: <br><br>" _
.Attachments.Add PdfFile
' Try to send
On Error Resume Next
.Display
Application.Visible = True
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
Worksheets("REPORT").Visible = xlSheetHidden
End Sub