Posts by RichardW

    Re: Skip comfirmation when sending email


    Is your email a pop3 account or is it Exchange? Thats where I came unstuck when trying to implement an Excel application for a university and the IT fella's aren't always keen to help some non IT with his latest Excel app. Maybe Google some more about CDO and Exchange if thats the issue, otherwise its a bit hard to help when we can't replicate your circumstances.
    Regards
    Richard


    ps If its not compiling then thats where you need to start

    Re: Skip comfirmation when sending email


    Something funny about the code I pasted, looks like it doubled up in those lines for some unknown reason, have a look at the last link I posted. Yep everytime I try it wraps it with email quotes,
    I think you have a spelling error, it should be smtp not smpt "Simple Mail Transfer Protocol"

    Re: Skip comfirmation when sending email


    Found this code at http://gallery.technet.microso…b2-4b43-9748-cb108ce6e523 supplied by Ron Nixon that worked for me when I used my ISP and email address. I'm using win7 64bit


    Re: Skip comfirmation when sending email


    Prior to using ClickYes I tried this, from memory it bypasses Outlook so you don't get a record of sent files and I had issues getting it to work on a business network that would have been using Exchange, it worked great at home through a pop3 account



    Re: Skip comfirmation when sending email


    I have used ClickYes in a project, it works well, here is some code I used to automate starting & stopping


    Regards
    Richard


    Re: Lookup To Read the Last Value to the Right


    Can't say, your second image is still fuzzy but it looks like you have typed it correctly. Maybe Excel is being funny, close it down and restart. I have both 2010 & 2007, works on both. Try some variation like running another LOOKUP command and then insert Kris's formula. All yours!

    Re: Vlookup To Read the Last Value to the Right


    CanRaps I stuffed up, the above doesn't work because you don't know which row will have your return value.
    By saying COUNTA(A3:I3) just counts the 3rd row, not the row where the result is.
    Sorry I got excited too but I haven't given up, I'll keep looking.


    Richard

    Re: Vlookup To Read the Last Value to the Right


    For your attached example try COUNTA(A3:I3) in the COL_INDEX_NUM


    I put the VLOOKUP in cell R3 ............=VLOOKUP(R3,A3:I13,COUNTA(A3:I3),FALSE)


    As long as there are no blanks in your range it should work

    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs & table


    So why not use a template that has the bookmarks placed where you want your content, everytime you want to update the document just rewrite it from Excel, have as much variable content in Excel as you can, have content that never changes in the template, have purposely named bookmarks in the right sections of your template.
    If you have different content at different times write code that allows a selection process ie a userform with checkboxes. Send only the text, charts & tables you want to their respective bookmarks, at worst you have to go into the finished document and do a little formatting ie delete blank pages where the bookmarks wern't populated. Consider also a system where you could have numerous templates with the bookmarks placed in different ways or order to select from.
    Thats just my opinion, I find I develop a system, get it going and then look for ways to improve it. In this way you learn a bit more about the complete picture rather than trying to develop the perfect system straight up and then realising issues. I have no doubt there is scope to build a system thats smart about collecting and assigning range names to bookmarks.
    The code I provided will do what you need, you just have to build in some If statements around selection of content.
    If I had time I would setup Mr Plows code and step through it to understand where you are having issues with that line of code, strName is obviously not getting a value earlier on in the procedure.
    Hope this helps.
    Richard

    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs & table


    Your homework? tut tut tut
    Attached is the Excel workbook with new code in Module 2 and a word template that must live in the same folder. NOTE you must rename Fruit1.doc to Fruit1.dotx. I couldn't see the file with the new extension in the attachment portal and had to rename it to Fruit1.doc
    This is slightly different in that it uses a template. Make a copy of the template, it is easy to end up with an open file when your code crashes and does not close the Word file, always manually open and close the Word file after a crash to ensure its closed properly.
    You can add error trapping and any other functionality as needed.
    You'll note how different my simple code is compared to Mr Plows. He obviously had a need to do what he did, your trying to adapt his code without understanding it caused you grief. That said someone better than me would likely critique my code, we're all learning.


    Regards
    Richard


    The code for those interested:


    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs & table


    Trim(strName) takes out any spaces from the string strName but it can only do that if strName has been assigned a value, so ask how does strName get its value. You need to step through the code one line at a time using the F8 function key on your keyboard, when you mouse over a variable you should see a value appear or you can bring up the Locals window to see what has been assigned to each variable at each stage of the program. If you are still having problems attach a file so I can get a better picture of what you are trying to do.


    Richard

    I have written an Excel Sales application that calculates delivery costs as parts are added. The program takes information from and writes to 7 textboxes. Everything works fine in the english version but when it's run on French XP and French Office 2003(in France) the calculation does not work. The output is rounded instead of decimal and multiplying a textbox by a range value does not work.
    We checked the VBA Forms 2.0 reference and made sure the dll file FM20.dll was the same version. We even put FM20ENU.dll into C:\Windows\System32 next to the FM20FRA.dll file but it made no difference.
    I have tried using Var(textbox1) & CDBl(textbox1) to format the output of the textboxes. Could it be an issue with declaring a value as Double?


    Attached is a smaller version of the program section I am having trouble with. Code is in Sheet1. Any help greatly appreciated.


    Below is the code from the attached file.

    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs & table


    I had a project where I created a Report with data from Excel and this code worked well. There should be enough to give you some hints on the format.


    Code
    Set ScoreTitle = Range("ScoreTitle1")
        objDoc.Bookmarks.Item("ScoreTitle").Range.InsertAfter ScoreTitle
        appWrd.Selection.Goto What:=wdGoToBookmark, Name:="ScoreDistributionChart"
        ThisWorkbook.Sheets("Score Distribution").ChartObjects(1).Copy
        appWrd.Selection.Paste
        appWrd.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter



    Code
    appWrd.Selection.Goto What:=wdGoToBookmark, Name:="BookmarkName"
            ThisWorkbook.Sheets("SheetName").Activate
            Range("A1").Select
            ActiveCell.CurrentRegion.Copy
            appWrd.Selection.Paste
            appWrd.Selection.Tables(1).Rows.Alignment = wdAlignRowCenter

    Re: Microsoft Visual Basic Freeze up


    On the Excel ribbon there is a quick access toolbar, on the RH side there is a drop down, selectiing More Commands opens the "Excel Options" where you can choose to add more commands to the quick access toolbar. There is a dropdown at the top with the option of "All Commands".
    If you also can't access the Visual Basic Editor using Alt F11 or any of the other links from the Developers Tab then maybe the easiest fix is to do a reinstall of Excel.

    [ATTACH=CONFIG]36489[/ATTACH]