Runtime error: '91 Object variable or with block not set

[Pookiemeister]

I am trying to attach a converted pdf to an email but I get the error in the title. Here's my code:

Private Sub cmdbtnPDF_Save_Click()
    Dim sPath As String
    Dim sFilename As String
    Dim wbAnswer, EmailAnswer As Integer
    sPath = "C:\Users\username\Desktop\Saved PDF Test\"
    sFilename = Range("VendorName").Text & "_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
    ActiveSheet.ExportAsFixedFormat xlTypePDF, sPath & sFilename
    ActiveWorkbook.Save
   
    EmailAnswer = MsgBox("Do you want to e-mail this pdf?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFilename)
    If EmailAnswer = vbYes Then
        Dim olApp As Outlook.Application
        Dim olMailItem As Outlook.MailItem
        Dim olAttachment As Outlook.Attachment
       
        Set olApp = CreateObject("Outlook.Application")
        Set olMailItem = olApp.CreateItem(olMailItem)
        Set olAttachment = olMailItem.Attachments
       
        With olMailItem
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = ""
            olAttachment.Add sPath & sFilename
            .Display
        End With
        Set olMailItem = Nothing
        Set olApp = Nothing
    Else
        Exit Sub
    End If
    wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
    If wbAnswer = vbYes Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        Exit Sub
    End If
   
End Sub

I was wondering what I might of missed? The error occurs on this line:

Set olMailItem = olApp.CreateItem(olMailItem)

I also tried:

Dim olApp As Object
Dim olMailItem As Object
Dim olAttachment As Object

When I change the current declaration to "As Object" I get a runtime error '13 Type mismatch. Thank you all who contribute to the solution.

 

[Norie]

Try this.

Private Sub cmdbtnPDF_Save_Click()
Dim olApp As Outlook.Application
Dim MI As Outlook.MailItem
Dim sPath As String
Dim sFileName As String
Dim wbAnswer, EmailAnswer As Integer

    sPath = "C:\Users\username\Desktop\Saved PDF Test\"

    sFileName = Range("VendorName").Text & "_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
    ActiveSheet.ExportAsFixedFormat xlTypePDF, sPath & sFileName
    ActiveWorkbook.Save

    EmailAnswer = MsgBox("Do you want to e-mail this pdf?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFileName)

    If EmailAnswer = vbYes Then

        Set olApp = New Outlook.Application
        Set MI = olApp.CreateItem(olMailItem)

        With MI
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Please allow me to purchase this."
            .Attachments.Add sPath & sFileName
            .Display
        End With
        Set MI = Nothing
        Set olApp = Nothing
    Else
        Exit Sub
    End If

    wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
    If wbAnswer = vbYes Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        Exit Sub
    End If

End Sub

 

[Pookiemeister]

Ok... After searching the web, I came across this similar problem in another similar excel help site like this one. One of the user stated that his code worked only when Outlook was open but did not if it was closed. So is there a way to open outlook through VBA? Or is there a better method? I've attached the last update to the program.

Thank You.

Private Sub cmdbtnPDF_Save_Click()
'    Dim olApp As Outlook.Application
'    Dim MI As Outlook.MailItem
    Dim oApp As Object
    Dim oMail As Object
    Dim sPath As String
    Dim sFileName As String
    Dim wbAnswer, EmailAnswer As Integer

    
    
    sPath = "C:\Users\username\Desktop\Saved PDF Test\"
    
    sFileName = Range("VendorName").Text & "_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
    ActiveSheet.ExportAsFixedFormat xlTypePDF, sPath & sFileName
    ActiveWorkbook.Save

    EmailAnswer = MsgBox("Do you want to e-mail this pdf?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFileName)

    If EmailAnswer = vbYes Then
        
        Application.ScreenUpdating = False
        
'        Set olApp = olApp.CreateItem(MI)
'        Set MI = olApp.CreateItem(MI)
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)

        With oMail
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Please allow me to purchase this."
            .Attachments.Add sPath & sFileName
            .Display
        End With
        Set oMail = Nothing
        Set oApp = Nothing
        
        Application.ScreenUpdating = True
    Else
        Exit Sub
    End If

    wbAnswer = MsgBox("Are you sure you want to close this workbook and Excel?", vbYesNo + vbDefaultButton1 + vbQuestion, "WORKBOOK: " & ActiveWorkbook.Name)
    If wbAnswer = vbYes Then
        ActiveWorkbook.Save
        Application.Quit
    Else
        Exit Sub
    End If

End Sub

 

[Norie]

Did you try the last code I posted?

 

[Norie]

Where do you get that error?

 

[Pookiemeister]

Your code works and I think I realized why it didn't work before. I copied the Excel workbook to a flash drive and tried it on another laptop and it worked as expected, NO ERRORS. So I went back to the first laptop that I've been using and tried it once more and I got that same error. So I looked through the programs and noticed that there was a Microsoft Office 365 installed which came with the laptop but the 30 day trial expired and there was a copy of Microsoft Office 2010 installed as well. Which I assume that both these versions of office caused the error. I guess the program didn't know which one to use. Also I'm not sure if I need to create another post for this issue, but how do I include a signature? Thank you so very much for your help.