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]

Change this line,

     olAttachment.Add sPath & sFilename

to this.

    .Attachments.Add sPath & sFilename

 

[Pookiemeister]

I followed your directions but I am still getting that runtime error. I even commented out these two lines of code:

'Dim olAttachment As Outlook.Attachment
'Set olAttachment = olMailItem.Attachments

Thank you for the response.

 

[Norie]

Can you post the code as it now is?

 

[Pookiemeister]

With this current version I now receive a "runtime error 438: Object doesn't support this property or method". Nothing is highlighted. Thank You

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 = Application.CreateItem(olMailItem)
        Set olMailItem = Application.CreateItem(olMailItem)
        'Set olAttachment = olMailItem.Attachments
              
        With olMailItem
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Please allow me to purchase this."
            .Attachments.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

 

[Norie]

Try this.

Option Explicit

Private Sub cmdbtnPDF_Save_Click()
Dim olApp As Outlook.Application
Dim olMailItem 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 = olApp.CreateItem(olMailItem)
        Set olMailItem = olApp.CreateItem(olMailItem)

        With olMailItem
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Please allow me to purchase this."
            .Attachments.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

 

[Pookiemeister]

I copied your code and reran it and now I am getting the error in the title Runtime error 91

 

[Norie]

Change olMailItem to something else, olMailItem is an Outlook VBA constant and you should use its name for a variable name,

 

[Pookiemeister]

Do you mean like this? If so, I am still getting the same error message. Thank you for your help.

Private Sub cmdbtnPDF_Save_Click()
    Dim olApp As Outlook.Application
    Dim MailItem 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 email this pdf?", vbYesNo + vbDefaultButton1 + vbQuestion, "Email: " & sFileName)

    If EmailAnswer = vbYes Then

        Set olApp = olApp.CreateItem(MailItem)
        Set MailItem = olApp.CreateItem(MailItem)

        With MailItem
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Please allow me to purchase this."
            .Attachments.Add sPath & sFileName
            .Display
        End With
        Set MailItem = 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]

Here's the most recent change:

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 = olApp.CreateItem(MI)
        Set MI= olApp.CreateItem(MI)

        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

Stepping through the program the error '91 occurs on this line of code.

        Set olApp = olApp.CreateItem(MI)