Saving excel file on server and emailing it through Lotus Notes

[johncash]

Hi,

I am right now working on a production spreadsheet macro , which will save the file after each shift-> save it on the server-> email to the manager and becomes blank again.

I had started on the path of closing the excel file after it saves but seems like not an good idea.So now i am looking for it to save the file, send email and sheet becoming blank again.

I am not a pro when it comes to macro, i am learning it right now. If some one could help me out. I will be very grateful to him.

Private Sub CommandButton1_Click()

Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename(“R:\ENGR\Shared\Engi neers\Prod\Shift A - Date.xls”,”Workbook(*.xls),*.xls”,,”Enter Date:”)


If TheFile=False Then
MsgBox “Why Did You Cancel?Try Again.”
Else
MsgBox “Thank You, Have a Nice Day”
End If
ActiveWorkbook.SaveCopyAs TheFile
ActiveWorkbook.Close (False)
End Sub

[zeddy]

Hi

Here's a part of what you might need. This just saves a copy of the file using the current date in the filename.
I wouldn't trust a User to enter a date! They could put the wrong date in or use a wrong date format.
The code uses yyyy-mm-dd as this always automatic sorting of files in date order in the destination folder.

The code uses SaveCopyAs, leaving the template file with whatever name you started with.
You would need to link other routines to Clear data, Email file, Close file.
I would recommend that these be assigned to separate 'buttons'.
Code for saving file:

Sub saveProductionFile()
        
zFolder = "R:\ENGR\Shared\Engineers\Prod\"  '<- define server folder here
zFilePrefix = "Shift A @"                   '<- set Shift details here
zDate = Format(Now, "yyyy-mm-dd")       'e.g. 2012-03-17; today's date (reverse order)
zFile = zPrefix & zDate & ".xls"        'e.g. "Shift A @2012-09-17.xls
zFileSaveAs = zFolder & zFile           'e.g. "R:\ENGR\..\Prod\Shift A @2012-09-17.xls"
Application.DisplayAlerts = False       'ignore any overwrite warnings if already done
ThisWorkbook.SaveCopyAs zFile           'save copy of current file with today's date
Application.DisplayAlerts = True        'turn alerts back on just in case
saywhat = "Done!"                       'define message
saywhat = saywhat & vbCr & vbCr                     'add 2 lines to message
saywhat = saywhat & "File saved as: " & zFile       'add text
saywhat = saywhat & vbCr & vbCr                     'add 2 lines to message
saywhat = saywhat & "in folder location:" & vbCr    'add text
saywhat = saywhat & zFolder                         'add text
zBoxtitle = "PRODUCTION FILE"           'define message box heading
zButtons = vbOKOnly + vbInformation     'define message box buttons
response = MsgBox(saywhat, zButtons, zBoxtitle) 'display message box

End Sub

zeddy

[duthiet]

John:

Welcome to the Lounge. Below is Code I found on the Internet. It works fine on my set-up. What it will do is create a new blank e-mail with the ActiveWorkbook as the attachement.
The copy sent is the ActiveWorkbook so make double sure the VBA doce has the saved version as an exact copy of the ActiveWorkbook.

The below does not Insert the To: ; CC:, or Subject. It also does not create any language in the Body. You will need to add CODE for those items.

You could also modify to allow for the sending, but in my experience that causes more problems than it solves. In other words, making the User see and review the e-mail prior to sending is a good thing.

Hope this helps.


Sub Mailer()
Dim Notes As Object
Dim Maildb As Object
Dim objNotesDocument As Object
Dim objNotesField As Object
Dim stattachment As String
stattachment = ActiveWorkbook.FullName
Set Notes = CreateObject("Notes.NotesSession")
Set Maildb = Notes.GETDATABASE("", "")
Maildb.OPENMAIL
Set objNotesDocument = Maildb.CREATEDOCUMENT
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", mysubject)
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", mysendto)
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")
'here's the attaching bit
objNotesField = objNotesField.EmbedObject(1454, "", stattachment)
Call objNotesField.APPENDTEXT(myBody)
Call objNotesDocument.REPLACEITEMVALUE("Body", myBody)
Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
Call WorkSpace.EDITDOCUMENT(True, objNotesDocument)
AppActivate "Lotus Notes"
End Sub

TomD