Several users in the office are receiving "Microsoft Visual Basic: 400" errors when running the following macro within excel:
Is this something to do with the range I've set for the 'tempfilename' and '.subject' line?
Any ideas how to resolve this while still keeping the macro in tact?
Code:
Sub Mail_workbook_Outlook_1()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
'TempFilePath = "p:\"
'Check if P: Drive exists else use c:
If Dir("P:/temp/") <> "" Then ' To check if folder exist or not
'MsgBox ("P: Drive exists!")
TempFilePath = "P:\temp\"
Else
'MsgBox ("Will use C: Drive!")
TempFilePath = "c:\temp\"
End If
TempFileName = IIf(Worksheets("User Set Up").Range("C10") <> "", Worksheets("User Set Up").Range("C10") & " " & Worksheets("User Set Up").Range("C11"), Worksheets("Winnipeg H-O Users").Range("C10") & " " & Worksheets("Winnipeg H-O Users").Range("C11")) & " User Setup Request" & " " & Format(Now, "dd-mmm-yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
'On Error Resume Next
'OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = IIf(Worksheets("User Set Up").Range("C10") <> "", Worksheets("User Set Up").Range("C10") & " " & Worksheets("User Set Up").Range("C11"), Worksheets("Winnipeg H-O Users").Range("C10") & " " & Worksheets("Winnipeg H-O Users").Range("C11")) & " User Setup Request"
.Body = "The form is attached."
.Attachments.Add wb2.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
wb2.Close SaveChanges:=False
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
wb1.Close SaveChanges:=False
End Sub
Is this something to do with the range I've set for the 'tempfilename' and '.subject' line?
Any ideas how to resolve this while still keeping the macro in tact?