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.

Sub Mail_Small_Text_CDO()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant
 
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


 
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
 
    With iMsg
        Set .Configuration = iConf
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = """me"" <[email protected]>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With
 
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub

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?
Thanks.

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.

part01 = "Automated message for "
    part02 = ":" & Chr(10) & Chr(10) & "The project " & Chr(34)
    part03 = Chr(34) & " with Open Item number "
    part04 = " has passed its Target Construction Approval (TCA) date of "
    part05 = ". Please update the TCA, and if necessary, the customer required date as well."
    part06 = Chr(10) & "Thank You." & Chr(10) & Chr(10) & "If you have received this email in error, please contact me."
    
    Dim myOLApp As Object
    Set myOLApp = CreateObject("Outlook.Application")
    
    Dim projReminder As Object
    Set projReminder = myOLApp.CreateItem(olMailItem)
    
    With projReminder
        .to = fName & "." & lName & "@MyCompany.com"
        .Subject = "Project TCA overdue"
        .Body = part01 & fName & " " & lName & part02 & projName & part03 & OINum & part04 & TCADate & part05 & part06
    End With
    projReminder.send

Can you send an email from Excel without actually attaching the current workbook?
I've looked through the help files, and every way of sending an email seems to entail also attaching the workbook.

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.

-Simon

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.
Thanks.

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.
Thanks.

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.

Thanks.

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:

Private Sub cmdOK_Click()
    ' The method used herein to check for the existence of a sheet with the new
    ' name is modelled from one written by www.OzGrid.com
    Dim wSheet As Worksheet
    If (cboMonth = "") Then
        MsgBox "Invalid month." & Chr(10) & "Please choose a month from the pull-down list."
        Exit Sub
    End If
    If (Not IsNumeric(txtYear) Or txtYear > 2050 Or txtYear < 1950) Then
        MsgBox "Invalid year." & Chr(10) & "Year must be between 1950 and 2050."
        Exit Sub
    End If
    
    On Error Resume Next
    Set wSheet = Sheets(cboMonth & " " & txtYear)
    
    If wSheet Is Nothing Then
    Sheets("Month-endTemplate").Copy After:=Sheets("Project Plan")
    ActiveSheet.Unprotect
    ActiveSheet.Visible = True
    ActiveSheet.Name = cboMonth & " " & txtYear
    Range("B14:D25,G26,A33:G37,A40:G44,C45,E45,G45,A48:G52,A55:G59,B6:D6,C7:D7,B8:D8,B9:D9,F7:G7,F8:G8,F9:G9").Select
    Selection.Locked = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Unload Me
    Set wSheet = Nothing
    On Error GoTo 0
    
    Else
    MsgBox "A worksheet with that date already exists." & Chr(10) & "Please choose a different date.", vbOKOnly, "Sheet name taken"
    Set wSheet = Nothing
    On Error GoTo 0
    End If
    
End Sub

Re: Add sheets Macro - check duplicate name

In my code (the second code window), not the OzGrid code.
It's the 8th line, just after the End If.

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:

Sub DoesSheetExist()
     '''''''''''''''''''''''''''''''''''''
     'Written by www.OzGrid.com
     
     'Test to see if a Worksheet exists.
     '''''''''''''''''''''''''''''''''''''
     
    Dim wSheet As Worksheet
     
    On Error Resume Next
    Set wSheet = Sheets("Sheet1")
    If wSheet Is Nothing Then 'Doesn't exist
        MsgBox "Worksheet does not exist", _
        vbCritical, "OzGrid.com"
        Set wSheet = Nothing
        On Error GoTo 0
    Else 'Does exist
        MsgBox "Sheet 1 does exist", _
        vbInformation, "OzGrid.com"
        Set wSheet = Nothing
        On Error GoTo 0
    End If
End Sub

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

Private Sub cmdOK_Click()
    Dim wSheet As Worksheet
    If (Not IsNumeric(txtYear) Or txtYear > 2050 Or txtYear < 1950) Then
        MsgBox "Invalid year"
        Exit Sub
    End If
    
    Set wSheet = Sheets(cboMonth & " " & txtYear)
    If wSheet Is Nothing Then
    Sheets("Month-endTemplate").Copy After:=Sheets("Project Plan")
    ActiveSheet.Unprotect
    ActiveSheet.Visible = True
    ActiveSheet.Name = cboMonth & " " & txtYear
    Range("B14:D25,G26,A33:G37,A40:G44,C45,E45,G45,A48:G52,A55:G59,B6:D6,C7:D7,B8:D8,B9:D9,F7:G7,F8:G8,F9:G9").Select
    Selection.Locked = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Unload Me
    Else
    MsgBox "A worksheet with that name already exists. Please choose a different name.", vbOKOnly, "Sheet name taken"
    End If
End Sub

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?
Thanks.

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.
Thanks.

Private Sub cmdOK_Click()
    If (Not IsNumeric(txtYear) Or txtYear > 2050 Or txtYear < 1950) Then
        MsgBox "Invalid year"
        Exit Sub
    End If
    Sheets("Month-endTemplate").Copy After:=Sheets("Project Plan")
    ActiveSheet.Unprotect
    ActiveSheet.Visible = True
    ActiveSheet.Name = cboMonth & " " & txtYear
    Range("B14:D25,G26,A33:G37,A40:G44,C45,E45,G45,A48:G52,A55:G59,B6:D6,C7:D7,B8:D8,B9:D9,F7:G7,F8:G8,F9:G9").Select
    Selection.Locked = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Unload Me
End Sub

Re: Wrap text and Merge cells without Shrink to fit

OK, I've continued my discussion here.

Re: Skip a row after so many characters

Hi,

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.

Thanks.

Re: Wrap text and Merge cells without Shrink to fit

laythss,

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.

Thanks.

Re: Coding custom data validation

That worked perfectly, thank you.

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.
Thanks.

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


Else 
      'Rest of code here