Posts by prism

    Re: formula linking to closed workbook with variable name


    The problem might have something to do with me having to add quotation marks within the brackets in order to add the date part of the formula into the file name...this was the only way i knew of to get excel to take in the date formula without using those characters as part of the filename.

    Re: formula linking to closed workbook with variable name


    Thank you kindly for the quick response, Norie.


    If I highlight and press F9 on:


    -- "Daily Totals " & TEXT(DATE(YEAR(BE6),MONTH(BE6),DAY(BE6)),"dd.mm.yy") & ".xls" --


    it does return the name of the file I am looking for.


    changing the date portion to your suggestion (many thanks for that by the way) TEXT(BE6,"dd.mm.yy") also gives me the correct file name and makes the formula nice and short, but the cell still unfortunately returns an #N/A

    This is not a VBA question, I am trying to do this with a formula. This was originally supposed to be a quick thing, but has regretably become quite frustrating. I am trying to link a row of cells in workbook A, to display the contents of certain "total" cells of workbooks B (There is a separate workbook B with a different total for each day...so the link formula must allow for the file name of workbook B to be written with a date variable). A regular formula with the date of the file typed in, works fine. It is only when I try and replace the date with a variable formula, that I have problems.


    Here is the formula with the date of the file manually entered:


    =(ABS(VLOOKUP("Total",'C:\Files\xyz\[Daily Totals 19.09.05.xls]Totals Report'!$B$8:$K$25,5,FALSE))/1000)


    It works fine...now when I replace the date with the following, this is where the problems come in and the cell will display "#N/A" (note: BE6 is where the date is pulled from in workbook A.


    =(ABS(VLOOKUP("Total",'C:\Files\xyz\["Daily Totals " & TEXT(DATE(YEAR(BE6),MONTH(BE6),DAY(BE6)),"dd.mm.yy") & ".xls"]Totals Report'!$B$8:$K$25,5,FALSE))/1000)


    If this is something that anyone has delt with and found a solution to...PLEASE lend me a hand if you could. Hopefully my syntax is wrong somewhere, or there is a workaround for this.

    Re: sending files with lotus notes


    this command will do what I've been trying to get it to do:


    Code
    Set workspace = CreateObject("Notes.NotesUIWorkspace") 
    Call workspace.EDITDOCUMENT(True, MailDoc)



    then if i take out the lines that send the mail and save it...i have it up on the screen to edit or do whatever with


    if anyone else uses this for anything...be aware it will cause an error if you have lotus notes minimized when you run the macro...i have no idea why that happens but it works for the most part so im happy

    Re: Email : Viewing Lotus Notes E-Mail Before Sending


    I saw your post and I've been trying to work my way around this too. If you still want to get the mail to be up on the screen so you can edit it, use this command:


    Code
    Set workspace = CreateObject("Notes.NotesUIWorkspace")
    Call workspace.EDITDOCUMENT(True, MailDoc).GOTOFIELD("Body")


    then delete the send and the save to sent folder or the save to draft folder...this will just bring it up for you to see

    Re: sending files with lotus notes


    Actually...if anyone knows how to get the mail up on the lotus window that would be a great help, as that was what I originally had wanted to do; I just wanted to get the mail put together and then have it up on the lotus notes screen so I could see it before I hit send.

    Re: sending files with lotus notes


    It seems that I've been able to solve my problem. By taking out the lines that send the mail and save it to the sentbox, and then adding the line:


    Call MailDoc.Save(True, False)


    I am able to have the mail saved in the drafts folder with all the options I selected still applied. This way I can make sure everything is ok before I send it out. Many thanks to anyone who had a look at this and tried to sort it out.

    I am having a problem with getting a macro working that, from excel, attaches and emails files through lotus notes. I am new to vba, so I just pieced together other peoples' code and added a few things to get it working the way I wanted. The macro works and the mails go out fine and get saved int he right places, but the problem is that I want to be able to view the mail that's created before it sends...just so I know I didnt make a mistake and I'll send the mails manually. As it is now, everything gets done behind the scenes and if i stop the mail from sending...it still won't actually let me see it in lotus notes. If anyone has any advice it would be greatly appreciated. The code I am using, for the most part, is as follows: