Object variable or With block variable not set-MAC issue

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
149
I get the following error "Object variable or With block variable not set" when I run the below code on a MAC however on a windows PC it works fine

VBA Code:
Option Explicit


Sub create_and_email_pdf()


Dim EmailSubject As String, EmailSignature As String
Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OverwritePDF As VbMsgBoxResult
Dim OutlookApp As Object, OutlookMail As Object
Dim strbody As String
CurrentMonth = ""

' *****************************************************
' *****     You Can Change These Variables    *********

    EmailSubject = "The file you were looking for"
    OpenPDFAfterCreating = False    'Change this if you want to open the PDF after creating it : TRUE or FALSE
    AlwaysOverwritePDF = True      'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
    DisplayEmail = False 'Change this if you don't want to display the email before sending.  Note, you must have a TO email address specified for this to work
    Email_To = ActiveSheet.Range("J1")
    Email_CC = ""
    Email_BCC = ""
   
           
' ******************************************************
    
    'Prompt for file destination
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        If .Show = True Then
        
            DestFolder = .SelectedItems(1)
            
        Else
        
            MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
                
            Exit Sub
            
        End If
        
    End With

    'Current month/year stored in H6 (this is a merged cell)
    CurrentMonth = Mid(ActiveSheet.Range("H6").Value, InStr(1, ActiveSheet.Range("H6").Value, " ") + 1)
    
    'Create new PDF file name including path and file extension
    PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Name _
                & "_" & CurrentMonth & ".pdf"

    'If the PDF already exists
    If Len(Dir(PDFFile)) > 0 Then
    
        If AlwaysOverwritePDF = False Then
        
            OverwritePDF = MsgBox(PDFFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", vbYesNo + vbQuestion, "File Exists")
        
            On Error Resume Next
            'If you want to overwrite the file then delete the current one
            If OverwritePDF = vbYes Then
    
                Kill PDFFile
        
            Else
    
                MsgBox "OK then, if you don't overwrite the existing PDF, I can't continue." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
                
                Exit Sub
        
            End If

        Else
        
            On Error Resume Next
            Kill PDFFile
            
        End If
        
        If Err.Number <> 0 Then
        
            MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
                    & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
                
            Exit Sub
        
        End If
            
    End If
   

    'Create the PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=OpenPDFAfterCreating

    'Create an Outlook object and new mail message
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
        
    'Display email and specify To, Subject, etc
    With OutlookMail
        
        .Display
        .To = Email_To
        .CC = Email_CC
        .BCC = Email_BCC
        .Subject = EmailSubject & CurrentMonth
        .HTMLBody = "Please find attached all the things" & "<br>" & _
              "Hey" & "<br>" & _
              "Hope you are well" & "<br>" & _
              "Email Signature"
        .Attachments.Add PDFFile
        
                
        If DisplayEmail = False Then
            
            .Send
            
        End If
        
    End With
    
 
End Sub


Anyone got any ideas on how to fix it?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,813
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
To the best of my recollection. FileDialog doesn't work on a Mac. I'd suggest you have a read of Ron's pages here if you need to work with Macs as well as PCs.
 

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
149
To the best of my recollection. FileDialog doesn't work on a Mac. I'd suggest you have a read of Ron's pages here if you need to work with Macs as well as PCs.
Thanks for that mate will check it out, looks like you need to install a script file for it all to work, long story short is do not use MACs for this function, when will people learn!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,607
Messages
5,573,193
Members
412,513
Latest member
PayneEdward
Top