Posts by Amapola188

    Hi Ger Plante


    Thanks for that. The message box shows the file name but not the path name. Does that mean there's something wrong with the path?


    I actually already had a message box and that one will show the entire path:
    MsgBox sh1.[B5].Value & "\Meetings\Kick-Off Meeting Minutes - " & sh1.[B1].Value & ".pdf"
    The results looks okay to me. I have to admit it's not how I would contruct a file name but I'm working with somebody else's data and can't change it.


    Also, my file is on a shared drive. Does that matter in any way?


    Thanks. Christine

    Good Morning, I have a macro that I'm adapting to send a range of reminders out of an Excel sheet. I'm trying to attach a file and I've never had any issues before but with this one, I get an error operation failed and I can't see where the problem is. The file I'm attaching is on a shared drive. But have put a test.docx on my desktop and tried that and still get the same error. The rest of the code works fine, i.e. if I convert the Add.Attachment line into a comment, the code runs through. I've also inserted a message box with the file coordinates and they seem fine to me.


    The file name has 2 variables: the project folder and the file name as these will vary for each project. - B5 contains the path for the project folder and B1 the project name.


    I have to admit that even though I have done a number of macros, I consider myself quite pedestrian with VBA. I'm hoping somebody will see where the code goes wrong.


    Thanks!
    Christine, Auckland


    A new month, same problem. I have incorporated the above into my code and played around with it a bit. I don't know whether it works because I get a runtime error 91 on the exact line.
    Runtime 91 is if a variable hasn't been set and I can't see the issue. ws has been declared and the .Name bit is part of the object model. I fail to see what it's problem is!


    My code below. Does anybody have any idea? I'm starting to think this may just not be possible.



    Thanks, Christine

    Good Afternoon
    I'm compiling a monthly report, adding data monthly to make it YTD. I have one to two sheets per month with the data which I'm adding every month. I then analyse the data with a Pivot Table.


    I have a macro that combines all sheets in a workbook but need to find a way to tell the macro to leave specific sheets out. Else I either need to recreate the Pivot Table each month, or combine the data manually.


    I first thought by hiding the sheet the macro would ignore the sheets but that doesn't work. Then I thought that maybe I could tell it to only combine the visible sheets. But my code isn't right. When stepping through my code, it also doesn't realibly hide the sheets and I have no idea why that would be.


    Thanks for any ideas. Maybe there is another property that could define which sheets should be combined?


    Re: Copy Excel range into email - Type mismatch error


    Good Morning


    Thanks, Norie. I have used Ron de Bruin's function and mostly, the code works fine, that is, it generates the email with everything in its right place. But I still get an empty error message box at the end. - When I step through the code, this seems to occur in cleanup function. Since this is On Error, I'm not sure why it even goes there?


    Here's my code now:


    Otherwise, code does what I need it to do so I'm quite happy. Thanks for your help!


    Christine
    Auckland

    Good Afternoon


    I have another macro throwing me an error. I would like to do the following: Open Outlook template, change subject, add attachment and add a specific range into the email body. 1 to 3 seem to work but code will baulk at the last bit. - When I step through the code, the code chokes on ".HTMLBody = Replace(OutMail.HTMLBody, strPlaceHolder, strTable)" and it give me a TypeMismatch error.


    My VBA is really very pedestrian so I'm quite stuck. I have tried to look the error up but since the items aren't declared, it doesn't seem to apply?


    I have reviewed Ron de Bruin's site but it seems to me what he does is have only the Excel Range in the HTML email body. (And apart from the fact that I'm loath to use code I don't understand at all!). I only want to add the range to the pre-written email. It will need to retain the formatting as well but that's an issue I don't even want to think about yet. When I do it manually, it's a simply copy and paste job. How hard can it be?


    The code also doesn't execute the date formatting correctly. It's displayed 28/04/2017. At a pinch that will work, though I would prefer 28 April 2017.



    Any pointers will be greatly appreciated.


    Thanks,
    Christine
    Auckland

    Re: Run-time error 424 - object required


    Thanks for that.


    I think it was the sh1 issue which is declared in the other code. - I actually got around the problem by putting "Format(sh1.Cells(r, 4).Value, "d mmmm YYYY")" straight where it needed to go. - I was generating an email and needed to put a date from the excel sheet (cell(r,4)) into the email but wanted it formatted the proper way.


    Works fine now, so thanks everybody for your suggestions.


    Christine
    Auckland

    Good Afternoon - I get a run-time error very early in my code. The surprising thing is that I use the same bit of code in another macro and it works just fine.


    It breaks on the last line of code here.



    Originally SeminarDate was declared as a string but from what I read, error 424 means that something is the wrong type or it's misspelt. So I changed it to As Date but the error persists.


    As I said above, I'm using the same code in another macro. Macro 1 (where it works) does a mailmerge and generates a PDF. Macro 2 (this one where it breaks) is sending an email, based on Outlook template.


    I'm stumped. Does anybody know what else it could be? Any help would be much appreciated. Thanks.


    Christine
    Auckland

    Good Afternoon


    I have a macro that generates an email from a line in Excel. I use this macro in various workbooks and now I have one, where it doesn't work.


    I have stepped through it (F8) line by line and when it comes to the ".To" line, it does seem to execute that line but then not move one. Because I use this code in other worksheets and it works perfectly, I'm stumped.


    Any ideas, I would be grateful! Thanks, Christine


    Good Afternoon - started this thinking it shouldn't be hard but can't find a solution.


    My spreadsheet shows nutritional information. I need to stick to certain values, less than 600 calories, less than 800 mg Sodium and less than 5 g Saturated Fat. I have these values at the top. And I have conditional formatting applied to either of these values in the list.


    Now I want to highlight the menu item where either of these limits is exceeded. Been playing around but nothing I do works.


    Any ideas? Since it's public information, I have attached the workbook.


    No urgency on this. Thanks.


    Christine
    Auckland
    forum.ozgrid.com/index.php?attachment/56040/

    Re: Type mismatch error on previously working VBA code


    Can't leave off it so I have started from scratch. The error is in this line:

    Code
    strRec = "Receipt: 64013000" & sh1.Cells(r, 12) & vbNewLine & vbNewLine


    And for the life of me, I can't see what's wrong with it. I have 3 lines like this:

    Code
    strReq = "Requisition: 64011000" & sh1.Cells(r, 6) & vbNewLine
        strPO = "PO: 64012000" & sh1.Cells(r, 7) & vbNewLine
        strRec = "Receipt: 64013000" & sh1.Cells(r, 12) & vbNewLine & vbNewLine


    Only strRec is playing up. On the upside, having modified the code for the e-mail body as follow (disabling above line), it works again. Only I want that line in of course!


    Code
    .Body = strbody & vbNewLine & vbNewLine & _
                    "Please find attached " & sh1.Cells(r, 5) & " invoice " & sh1.Cells(r, 2) & " " & sh1.Cells(r, 3) & " for " & curIncGST & "(" & sh1.Cells(r, 8) & ") for payment." & vbNewLine & vbNewLine & _
                    strReq & _
                    strPO & _
                    strRec & _
                    "Thank you." & vbNewLine & vbNewLine & _
                    Signature


    Can anybody see anything in that line that makes it spit the dummy?

    Re: Type mismatch error on previously working VBA code


    Thanks, Roy, for the suggestion. My confusion is because so far the code worked perfectly. It worked fine on Friday and on Monday I start having problems. That is the really odd thing.


    I have the same macro in the 2012 spreadsheet and it works perfectly there. Admittedly, the first error that came up is that the e-mail body was empty. The type mismatch error came up when I re-configured the content for the e-mail body. I hadn't touched the attachment.


    So the macro works in the 2012 workbook. I copied it into the 2013 workbook and the e-mail body is empty ... This is the original code: