2012-03-08, 19:27 #1
- Join Date
- Mar 2012
- Thanked 0 Times in 0 Posts
Saving excel file on server and emailing it through Lotus Notes
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.”
MsgBox “Thank You, Have a Nice Day”
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2012-03-09, 11:24 #2
- Join Date
- Mar 2002
- Newcastle, UK
- Thanked 171 Times in 167 Posts
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
2012-03-09, 14:39 #3
- Join Date
- Nov 2002
- New York, New York, USA
- Thanked 11 Times in 11 Posts
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.
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("", "")
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 objNotesDocument.REPLACEITEMVALUE("Body", myBody)
Set WorkSpace = CreateObject("Notes.NotesUIWorkspace")
Call WorkSpace.EDITDOCUMENT(True, objNotesDocument)
AppActivate "Lotus Notes"