Save file to Specific Directory - then EMAIL
 

I am not sure what I am doing wrong with the code below. I need to
specifically state in the code what network directory (N:) the file is to be
saved to. The below for saving to specific directory is not working. Please
Help. Thanks

Private Sub CommandButton1_Click()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim MyPath As String
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook
MyPath = "\\N:\Business Process Engineering\PROJECTS\TEST"
'MyPath = "\\N:\Personnel\Employee Exit Forms - Initial"
fname = Application.GetSaveAsFilename(TextBox7.Value & " - Exit Form " &
Format$(TextBox1.Value, "mmddyyyy"), _
fileFilter:="Excel Files (*.xls), *.xls")
If fname < False Then
Wb.SaveAs fname
'Wb.Close False
Set Wb = Nothing
Else
Set Wb = Nothing
End If
' ActiveWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Employee Exit Form - Complete within 3 Business Days of
Receipt"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add '("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Save file to Specific Directory - then EMAIL
 

If you recorded this as a macro you would not get the \\ at the start of the
folder list - could be this is the problem
--
If the post is helpful, please consider donating something to an animal
charity on my behalf .......... and click Yes


"LRay67" wrote:

I am not sure what I am doing wrong with the code below. I need to
specifically state in the code what network directory (N:) the file is to be
saved to. The below for saving to specific directory is not working. Please
Help. Thanks

Private Sub CommandButton1_Click()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim MyPath As String
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook
MyPath = "\\N:\Business Process Engineering\PROJECTS\TEST"
'MyPath = "\\N:\Personnel\Employee Exit Forms - Initial"
fname = Application.GetSaveAsFilename(TextBox7.Value & " - Exit Form " &
Format$(TextBox1.Value, "mmddyyyy"), _
fileFilter:="Excel Files (*.xls), *.xls")
If fname < False Then
Wb.SaveAs fname
'Wb.Close False
Set Wb = Nothing
Else
Set Wb = Nothing
End If
' ActiveWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Employee Exit Form - Complete within 3 Business Days of
Receipt"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add '("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Save file to Specific Directory - then EMAIL
 

This should answer many/all questions about automation-emailing with Excel:
http://www.rondebruin.nl/sendmail.htm

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Paul" wrote:

If you recorded this as a macro you would not get the \\ at the start of the
folder list - could be this is the problem
--
If the post is helpful, please consider donating something to an animal
charity on my behalf .......... and click Yes


"LRay67" wrote:

I am not sure what I am doing wrong with the code below. I need to
specifically state in the code what network directory (N:) the file is to be
saved to. The below for saving to specific directory is not working. Please
Help. Thanks

Private Sub CommandButton1_Click()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim MyPath As String
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook
MyPath = "\\N:\Business Process Engineering\PROJECTS\TEST"
'MyPath = "\\N:\Personnel\Employee Exit Forms - Initial"
fname = Application.GetSaveAsFilename(TextBox7.Value & " - Exit Form " &
Format$(TextBox1.Value, "mmddyyyy"), _
fileFilter:="Excel Files (*.xls), *.xls")
If fname < False Then
Wb.SaveAs fname
'Wb.Close False
Set Wb = Nothing
Else
Set Wb = Nothing
End If
' ActiveWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Employee Exit Form - Complete within 3 Business Days of
Receipt"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add '("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub