RunTime Error 91 Object Variable or With Blokc variable not set

[jlyn123]

Hello,

I am getting this error but I don't know what I am missing. I used this same code in another form and did not get this error?

Below is part of the code.

Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String, Payee As String
Dim xlObj As Excel.Application
Dim wkbk As Object
Dim strUser As String
Dim strAttachment As String
Dim OutlApp As Object
Dim acOutputxl As Object
Dim DoCmd As Object
Dim acExportQualityPrint As Object

DoCmd.OutputTo acOutputxl, "ExcelWorkbook(*.xlsx)", "C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx", False, "", , acExportQualityPrint

 

[PatOBrien198]

Where is the value of "strUser" set?

you might want to check the value of:

"C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx"

to make sure that all of the \\s are in the correct places.

 

[Joe4]

With that error message, I doubt that your error is coming from that part of the code, but rather a loop or With or If block elsewhere.

However, I do see a few potential issues with your last line you posted above.

I think you need another slash after "users", i.e.

[COLOR=#333333]"C:\users[/COLOR][COLOR=#ff0000]\[/COLOR][COLOR=#333333]"[/COLOR]

and possible after "desktop", i.e.

[COLOR=#333333]"\desktop[/COLOR][COLOR=#ff0000]\[/COLOR][COLOR=#333333]"[/COLOR]

Otherwise, if both "users" and "desktop" are folder names, they are going to be blended in with the next folder name.

Also, you appear to be using the variable strUser without ever setting it to anything.

A little recommendation. If you store your file name in a variable, you can easily use a MsgBox to ensure it looks complete and correct, i.e.

Dim fName as String

fName = [COLOR=#333333]"C:\users\" & strUser & "\desktop\" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx"
[/COLOR]MsgBox fName
[COLOR=#333333]DoCmd.OutputTo acOutputxl, "ExcelWorkbook(*.xlsx)", fName, False, "", , acExportQualityPrint[/COLOR]

 

[steve the fish]

You are definitely missing some slashes.

 

[jlyn123]

I lots some of the slashes when I copied from code.

It does contain the slashes after users and desktop and strUser is defined as such = Environ("Username")

I did the message box and the flow looks correct- but I am still getting same error message???


C:\users\U1113779\desktop\ACH transfers021218.xlsx

 

[Joe4]

Is that really all your code?
If not, please post the rest.

 

[jlyn123]

No it is not. I only posted where my issue was. I haven't had an issue generating the email and attaching the PDF but I cant seem to get the excel document to attach to the email as well.

Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String, Payee As String
Dim xlObj As Excel.Application
Dim wkbk As Object
Dim strUser As String
Dim strAttachment As String
Dim OutlApp As Object
Dim acOutputxl As Object
Dim DoCmd As Object
Dim acExportQualityPrint As Object


Title = Worksheets("ACH Transfers").Range("A1") & " - " & DateTime.Now

strUser = Environ("Username")

Title = Worksheets("ACH Transfers").Range("A1")
Payee = Worksheets("ACH Transfers").Range("A2")
Title = Title & " - " & Payee

PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
strAttachment = "C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx"

DoCmd.OutputTo acOutputxl, "ExcelWorkbook(*.xlsx)", strAttachment, False, "", , acExportQualityPrint



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


On Error Resume Next
Set xlObj = GetObject(, "Excel.Application")
If Err Then
Set xlObj = CreateObject("Excel.Application")
IsCreated = True
End If
xlObj.Visible = True
On Error GoTo 0

Set wkbk = xlObj.Workbooks.Open("C:\users" & strUser & "\desktop" & "ACH Transfers" & Format(Date, "mmddyy") & ".xlsx")



wkbk.Save
wkbk.Close
xlObj.Quit
Set xlObj = Nothing

With OutlApp.CreateItem(0)

.Subject = Title
.To = Worksheets("ACH Transfers").Range("J2")
.Body = "REQUESTOR tasks:" & vbLf _
& "1. Complete the Treasury Controllership Funding Request." & vbLf _
& "2. Email request to APPROVER to review." & vbLf _
& "3. NOTE: Do NOT email (To: or cc to IACTreasuryBanking@mmc.com. The APPROVER is the only person who should send an approval to this address." & vbLf & vbLf _
& "APPROVER tasks:" & vbLf _
& "1. Please review the attached file." & vbLf _
& "2. Forward your approval along with this form to IACTreasuryBanking@mmc.com" & vbLf & vbLf _
& "NOTE: Any request without proper approval will not be processed." & vbLf
.Attachments.Add PdfFile
.Attachments.Add strAttachment
.Display

End With

Kill PdfFile

Kill strAttachment

If IsCreated Then OutlApp.Quit

Set OutlApp = Nothing

End Sub

 

[Joe4]

I have only seen "DoCmd.OutputTo" used in Access VBA.
Where do you get this bit of code from?

 

[Norie]

Where are you running this code?

DoCmd is from Access VBA and if you were running this code in Access you wouldn't need to have this declaration.

Dim DoCmd As Object

 

[jlyn123]

Funny thing is I am running this in excel and it works in one spreadsheet but not in another?