Hello
I have created an excel workbook with some macros in order to improve the work and decrease the time.
the workbook is good to send the e-mail with macros but when the other people try to click the ( Save File As Pending ) it gives me an error
I need to make the file works in booth PC's
1- technical support fill the info, then send , then save & clear to erase the information & generate another order No.
2- Fleet service when receive first step is to save as pending. in the section i created in the macro.
3- After order done he will open the file from the folder as per the name and fill the information then send , and save to close the order.
4- technical support receive the file and fill the remarks and save to close >
after that print the file to make it in the Truck file.
can any one help me.
my problem is how can I make this file work with macro after name changed and to save in the path as per my path
Thanks
This is my macros
Sub NextInvoice()
Range("O8").Value = Range("O8").Value + 1
Range("U11:AA16").ClearContents
Range("M11:O16").ClearContents
Range("A11:H16").ClearContents
Range("C22:AB31").ClearContents
Range("O17:O18").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Technical Support Job Order Pending\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
NextInvoice
End Sub
Sub Email_CurrentWorkBook_Hoobers()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_FlatBed()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Others()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Hoobers_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK4").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_FlatBed_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Others_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub SaveInvWithNewName_FleetService()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Fleet Service Job Order Close\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub SaveInvWithNewName_Pending()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Fleet Service Job Order Pending\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub SaveInvWithNewName_Close()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Technical Support Job Order Close\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub RunExcelMacro()
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("D:\Breakdown Sheet\Breakdown Sheet.xlsm")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Run the target macro
xl.Run "MyMacro"
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Sub
I have created an excel workbook with some macros in order to improve the work and decrease the time.
the workbook is good to send the e-mail with macros but when the other people try to click the ( Save File As Pending ) it gives me an error
I need to make the file works in booth PC's
1- technical support fill the info, then send , then save & clear to erase the information & generate another order No.
2- Fleet service when receive first step is to save as pending. in the section i created in the macro.
3- After order done he will open the file from the folder as per the name and fill the information then send , and save to close the order.
4- technical support receive the file and fill the remarks and save to close >
after that print the file to make it in the Truck file.
can any one help me.
my problem is how can I make this file work with macro after name changed and to save in the path as per my path
Thanks
This is my macros
Sub NextInvoice()
Range("O8").Value = Range("O8").Value + 1
Range("U11:AA16").ClearContents
Range("M11:O16").ClearContents
Range("A11:H16").ClearContents
Range("C22:AB31").ClearContents
Range("O17:O18").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Technical Support Job Order Pending\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
NextInvoice
End Sub
Sub Email_CurrentWorkBook_Hoobers()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_FlatBed()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Others()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Hoobers_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK4").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_FlatBed_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub Email_CurrentWorkBook_Others_FleetService()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim MyWb As Workbook
Set MyWb = ThisWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now get the extension of the file
'below line will return the extension
'of the file
FileExt = "." & LCase(Right(MyWb.Name, Len(MyWb.Name) - InStrRev(MyWb.Name, ".", , 1)))
'Now append a date and time stamp
'in your new file
TempFileName = Range("AK3").Value
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
MyWb.SaveCopyAs FileFullPath
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "abc@s.com"
.BCC = "abc@s.com"
.Subject = Range("AK2").Value
.Body = "Hi"
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Send 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now delete the temp file from the temp folder
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Sub SaveInvWithNewName_FleetService()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Fleet Service Job Order Close\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub SaveInvWithNewName_Pending()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Fleet Service Job Order Pending\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub SaveInvWithNewName_Close()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "D:\Technical Support Job Order Close\Inv" & Range("O8") & Range("AI1") & Range("U11").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub
Sub RunExcelMacro()
Dim xl As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("D:\Breakdown Sheet\Breakdown Sheet.xlsm")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Run the target macro
xl.Run "MyMacro"
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Sub
