I made a program to export a an excel file, edit it, and then attach it to an outlook email. Everything works half the time but the other half, i get an error "Run-time error '1004': Methos 'Columns'of object'_Global'failed", clicking debug brings me to my code "Columns("B:B").Delete Shift:=xlToLeft", any ideas as to why this works sometimes and why other times it doesn't, and is their a way to fix it?
Code:
Code:
Public Function excelExportNewParts()
DoCmd.OutputTo acOutputQuery, "qNewPartTemplate", acFormatXLSX, "G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx", False
Dim xlapp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xldata As Excel.Range
Dim ExcelPath As String
Dim rowNo As Long
ExcelPath = CurrentProject.Path & ""
Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.Workbooks.Open("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx")
Set xlSheet = xlBook.Worksheets("qNewPartTemplate")
xlapp.Visible = True
xlSheet.Select
xlSheet.Activate
'With ActiveSheet
Columns("B:B").Delete Shift:=xlToLeft
Range("B1:B1") = "Description"
Cells.Select
Selection.ClearFormats
Columns("F:F").NumberFormat = "0.00"
'End With
xlBook.Save
xlBook.Close
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlapp = Nothing
' Outlook email
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookAttach As Outlook.Attachment
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To = "email"
.Body = "Hello," & vbNewLine & vbNewLine & "Could you please enter these when you get a chance?" & vbNewLine & vbNewLine & "-Thank you!"
' Set the Subject of the message.
If Not IsMissing("New Parts") Then
.Subject = CStr("New Parts")
End If
' Add attachments to the message.
If Not IsMissing("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx") Then
Set objOutlookAttach = .Attachments.Add("G:\Tooling\Requests Sent\TBE Enter" & Format(Now(), "MM-DD-YYYY") & "TBE.xlsx")
End If
End With
objOutlookMsg.Display
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Function