Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Crashes Running VBA Code

Posted on 2014-07-21
9
Medium Priority
?
760 Views
Last Modified: 2014-07-28
The below code crashes excel 99% percent of the time and I'm not sure why. It will open the groupwise email client but then excel will crash. Since the email message does open, I just close excel but it's a real issue. Any ideas why it may be happening? Thanks!

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long



Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lngResponse As Long
Dim URL As String, Email As String, Subj As String, body As String, Msg As String
Dim TheDate As String
Dim TheTime As String
Dim Greeting As String
Dim TrgtRow As Long

    'RB, Q: should the range extend to row 27 (as below) or to row 26 as stated in the question?
    If Intersect(Target, Range("F2:F27")) Is Nothing Then
        Exit Sub
    Else

        TrgtRow = Target.Row
        Email = Range("$E$" & TrgtRow).Value
        TheDate = Format(Date, "Long Date")
        TheTime = Format(Time, "Medium Time")

        ' Determine greeting based on time
        Select Case Time
            Case Is < 0.5: Greeting = "Good Morning"
            Case Is >= 0.7083: Greeting = "Good Evening"
            Case Else: Greeting = "Good Afternoon"
        End Select

        lngResponse = MsgBox("You are about to send an email with a link to course information. Would you like to continue?", vbYesNo)
        If lngResponse = vbYes Then
            'Replace spaces with %20 (hex)
            Subj = "Course Information for " & Range("$D$" & TrgtRow).Value
            Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")

            'in EE Q, if F2:F26 (general course categories)
            Msg = "Please visit the following link to view information and register for the course you requested: " _
                  & "%0D%0A" & "%0D%0A" & Range("$L$" & TrgtRow).Value
            '& "%0D%0A" _
             & "%0D%0A" & " If we can be of further assistance, please contact us." & " Thank you."

            If Target.Address = "$F$27" Then
                ''in EE Q, if instersect range is F7 (with an option to only send the above code - this range (F7) _
                 ''will include a specific course and can include a course book but want to give the user an option _
                 ''to not include the course book link if student doesn't need or has course book):
                'Msg = "Please visit the following link to view information and register for the course you requested: " _
                 & "%0D%0A" & "%0D%0A" & Range("$L$" & TrgtRow).Value
                lngResponse = MsgBox("Do you want to include the course book link?", vbYesNo)
                If lngResponse = vbYes Then
                    Msg = Msg & "%0D%0A" & "%0D%0A" & "Your course books can be viewed here: " _
                          & "%0D%0A" & "%0D%0A" & Range("$M$" & TrgtRow).Value
                    '& "%0D%0A" & "%0D%0A" & " If we can be of further assistance, please contact us." & " Thank you."
                End If
            End If
            Msg = Msg & "%0D%0A" & "%0D%0A" & " If we can be of further assistance, please contact us." & " Thank you."

'            'in the original code:
'            Msg = "Please visit the following link to view information and register for the course you requested: " _
'                  & "%0D%0A" & "%0D%0A" & Range("$L$" & TrgtRow).Value & "%0D%0A" _
'                  & "%0D%0A" & "Your course books can be viewed here: " & "%0D%0A" & "%0D%0A" & Range("$M$" & TrgtRow).Value _
'                  & "%0D%0A" & "%0D%0A" & " If we can be of further assistance, please contact us." & " Thank you."
            Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
            'Replace carriage returns with %0D%0A (hex)
            Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

            'Create the URL
            URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Greeting & "," & "%0D%0A" & "%0D%0A" & Msg & "%0D%0A" & "%0D%0A"

            'Execute the URL (start the email client)
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

        End If
    End If

End Sub

Open in new window

0
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40210591
Does Excel give any error message? Have you tried adding a DoEvents line after line 73?
0
 
LVL 1

Author Comment

by:tracyms
ID: 40210598
LCID:   1033
skulcid:   1033

Above is error, and how do I add a "DoEvents" line? Thanks.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40210670
No points for me...
RE: DoEvents..

After the following pair of lines (71-72)
            'Create the URL
            URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Greeting & "," & "%0D%0A" & "%0D%0A" & Msg & "%0D%0A" & "%0D%0A"

Open in new window

type in
DoEvents
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 71

Expert Comment

by:Qlemo
ID: 40211178
What you see isn't an error message, it is the output from something telling you which language is used. 1033 is en-US (see http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx).

What do you mean with "Excel crashes"?
0
 
LVL 1

Author Comment

by:tracyms
ID: 40211691
See attached screenshot for crash when I double click link to send email (see last column in screenshot). The email client does open with the message but excel crashes. The "DoEvents" didn't work, it still crashed when I added it.
Crash.pdf
0
 
LVL 1

Author Comment

by:tracyms
ID: 40211703
Actual message when crashes:

Message.JPG
0
 
LVL 1

Accepted Solution

by:
tracyms earned 0 total points
ID: 40212367
I added  "ActiveSheet.Range("A2").Select"  to the end of the code:

  'Execute the URL (start the email client)
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

        End If
    End If

  ActiveSheet.Range("A2").Select

End Sub

It hasn't crashed yet - found information at this link after more research:

http://social.msdn.microsoft.com/Forums/office/en-US/20e73d3c-1fea-4073-8b22-751b88e3410e/excel-2010-xlsm-code-crashing?forum=exceldev
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 40212986
Strange workaround. But if it helps ...
0
 
LVL 1

Author Closing Comment

by:tracyms
ID: 40223724
I searched the net and found a solution that seems to be working. I appreciate everyone's help!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question