Results 1 to 3 of 3
  1. #1
    New Lounger
    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”
    End If
    ActiveWorkbook.SaveCopyAs TheFile
    ActiveWorkbook.Close (False)
    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Newcazzle, UK
    Thanked 651 Times in 619 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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    New York, New York, USA
    Thanked 20 Times in 20 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.

    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("", "")
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts