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

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I am trying to attach a converted pdf to an email but I get the error in the title. Here's my code:
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:
VBA Code:
Set olMailItem = olApp.CreateItem(olMailItem)
I also tried:
Code:
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.
 
Last edited:
Try this.
VBA Code:
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
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.

VBA Code:
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
 
Upvote 0
Did you try the last code I posted?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top