Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving excel file on server and emailing it through Lotus Notes

    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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 169 Times in 165 Posts
    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:
    Code:
    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

  4. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    231
    Thanks
    0
    Thanked 11 Times in 11 Posts
    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

Posting Permissions

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