Posts by slickity

    Re: Send mail without attaching workbook

    OK, the plot has just thickened. About 3 minutes after I posted the above, I received the email that should have been generated by that code, even though the code had crashed on the .Send line. Any thoughts? The code crashed, yet still sent the email.

    Re: Send mail without attaching workbook

    OK, I just checked that link you gave. I'm just trying to test his code first, to see if it will work for me, so here is a direct paste of his code, unmodified, except for the removal of some comments.

    When I run this, I get "Run-time error'-2147220960 (80040220) The 'SendUsing' configuration value is invalid" at the line ".Send". Can anyone explain why? Thanks.

    Re: Generate emails from Crystal Reports

    See, that's what I like to hear. If I could cut Crystal right out of this process, I'd be delighted. However, I don't know anything about querying from Excel. Can you tell me a little, or probably better yet, direct me towards a crash-course tutorial on it?

    Re: Send mail without attaching workbook

    Hi, royUK My question is regarding sending an email from within Excel, so I thought the the Email/ Excel help forum was the place to put it. Now that you're pointing it out though, I think it would be better placed in the VBA section since I am trying to implement this VBA, so if possible, could you please move it? Thanks.

    In the meantime chrisxs5, what I'm trying to send is an email that is partially pre-written, but contains some values from the spreadsheet I'm in.

    And I have a question now. This will have a loop that will typically send 30 to 80 emails, and I don't want to have to hit Yes for every one when I get that warning box from Outlook. Is there some work-around for this? Like maybe sending the email without actually going through Outlook.

    As for Dave's guide, I read it, but it seemed all his code involved attaching the workbook. Below is my current code, which just generates an email, without attaching.

    OK, Here's what I'm trying to do:

    Run a Crystal Report, which queries an Oracle Server, and returns a list of projects that are falling behind, along with the name of the project manager corresponding to each. I then want to generate an email from each which will basically say "Dear So & So, your project blank is falling behind."

    I've spent two days straight trying to find a way to do this automatically, and have found none, which blows me away since I think it's a really obvious thing to want to do.

    However, having found none, I'm now going to try to write some VBA to at least partially automate the process. Here's what I'm looking at doing:

    -Manually export the report from Crystal to Excel (can this be automated?)
    -Have VBA macro go through the Excel version and for each project, send an email through Outlook to that person, with the message mentioned above.

    I don't have any specific code questions right now, because I think I can do this fairly easily. What I would like to know though is, what are your oppinions on this approach? Is there an easier way to do this? And to anyone with Crystal Reports experience, is there in fact some way to do this just with Crystal, eliminating the need to screw around in VBA?

    Thanks for reading this rather long post.


    Re: Strange doubling-over of shapes

    OK, I just partially figured this one out myself. For some reason the default, in this file only, is for shapes to have shadows. So I added the line "Selection.ShapeRange.Shadow.Visible = msoFalse" into each piece of shape creation code.

    However, I'm still curious why this became the default, and how can I change it?

    Also, in stead over having the above line repeated many times, is there a way to wholeshale change-out for all shapes?

    Some thing like "Shapes.SelectAll.Shadow.Visible = msoFalse"?
    I know there's code to select all shapes, but I don't remember it now.

    I have one file here where if I draw a shape, say, a line, I get a double of that line, slightly offset from it. Changing lineweight seems to affect both lines, while changing line-color, only affects one. Also, using the BuildFreeForm shape will give both the free form you draw, and a straighline connecting the begin and end points.
    Anyone seen this before?
    The drawing is done with a Macro, which is why it's in this section. But, if I hand-draw a line in this file, the same thing happens. However, if I copy & paste the macro code into an earlier version of the file, it does not do this, so there is obviously something about this file. I've attached a picture to show what I'm talking about.

    Re: Add sheets Macro - check duplicate name

    I'm not getting the error I mentioned earlier. The code I posted last is my working code. I was just wondering if there is a problem with accessing the value of a combo box without actually using ".value" because someone earlier made it sound like that was a problem. However, it has so far worked fine for me.

    I have a combo box in a user form, and I want to limit its values those already in its list. I've set MatchRequired to True, but the error message is just generic. Is there some manual way to check for a match in the code, and pop up your own message?

    I searched the forums, and the closest thing I could find was here.


    Re: Add sheets Macro - check duplicate name

    It is a combo box named cboMonth. I've just been using its name as is to access its value. This has worked fine for me so far. Are there any special cases where this will not work?

    Also, I actually now have this working as follows:

    Re: Add sheets Macro - check duplicate name

    OK, I just tried to use the method from the other thread that ShoMeister referred me to.

    Here's a copy of the code that is posted there:

    Here's how I've tried to adapt it (when I''ve got this working, I will cite in comments.)

    The above gives me the run-time error 9: " Subscript out of range" on the line "Set wSheet = Sheets(cboMonth & " " & txtYear)."

    Any ideas?
    Also, can someone explain all the On Error Resume Next stuff?

    Hi folks,

    I have a User Form that adds a new sheet and names it based on a month and a year that you input in the form. How do I set it up to check for that name existing before creating the sheet, so that if the user tries to create two of the same month-year, it won't crash.
    Here's my current code. I need to add a check between the "End If" and "Sheets..." lines.

    Re: Skip a row after so many characters


    I originally started another thread on a similar topic, and Laythss linked to this thread. This thread is close to what I am looking for, so I will be continuing my questions here.
    A link to my original thread is here.

    With regard to the above code posted by laythss, is there a way to incorporate this code into a loop that would run until all the text had been put into the sheet, so that you would not have to have an upper limit on how long the input text could be?

    I haven't tried this yet myself, but I'm going to soon, so I thought I'd throw this question out so if I don't get anywhere with it, maybe someone else will have some ideas.


    Re: Wrap text and Merge cells without Shrink to fit


    I looked at the thread you linked to, and that's much closer to what I'm looking for: VBA code that will dynamically expand the text area as needed.
    Is it OK if I post some questions in that thread now, because there are a few more small things I'd like to do that are not in that explanation.

    Is it possible to use "Wrap text," and "Merge cells" without "Shrink to fit"?

    I want a merged-cell, text-wrapped text entry area that will start at certain size (to suggest to the user the amount of text he/she should enter), but which will grow downward to allow the user to input more than that amount of text.

    Unfortunately, due to the arrangement of the other data on the sheet, I can't just take one cell and stretch it way out then put on wrap text.

    Any ideas greatly appreciated.


    Hi folks,

    I have a macro that uses values in a few fields. The values in these fields need to be of a certain type (eg, real numbers, etc.), otherwise I get run-time errors (type mistmatch, specifically). However, due to the nature of the sheet, I can't just use data verification in the sheet.
    Is there some way to check these ranges right at the beginning of a macro to make sure they're the right kind, and exit the sub if they're not?
    For example, if a certain range contains any of #N/A, #DEF, #VALUE, etc., I want to exit the sub immediately.

    Here is the approach I was trying to use. This still gives me a type mistmatch error when doing that first check. Note: this is not actual code, as I have deleted what I had before, which is why I am not using the "Code" tag.

    If (Range("D12").Value = "#N/A") then
        MsgBox "Field contains invalid data"
        Exit Sub
    End if
          'Rest of code here