CODEWITHSUNDEEP
vbaexceloutlooksubroutine
CRUTER
CRUTER

Reputation: 866

Excel VBA: Stops Working at Outlook Email Creation Subroutine

I asked this question the other day and thought I found the answer, but now Excel keeps going into it's 'Not Responding' mode. My issue is that the program runs fine on some computers, but bombs out on others.

Background: This program gets run after the user fills in an Excel form and clicks submit. The program validates the data, manipulates the data, creates an email via Outlook, and finally saves and closes the document.

The program gets run on many co-workers' computers at work. We all run on Windows 7, Office 2013, but some of us have different laptops (HP, Dell, etc.)

Here is the call to the subroutine, which is the line of code that excel starts to 'Not Respond' - FYI, this is NOT a debugging stop:

Call EmailApprovalP1(GeneralSection, ItemSection, VendorSection, MarketSection, Part1Section, wbLog, wbPCD, resubmission)

Here is the beginning of the subroutine being called:

Public Sub EmailApprovalP1(ByRef GeneralSection As Object, ByRef ItemSection As Object, ByRef VendorSection As Object, ByRef MarketSection As Object, ByRef Part1Section As Object, ByRef wbLog As Workbook, ByRef wbPCD As Workbook, ByRef resubmission As Boolean)

'Declare In Memory
Dim i As Integer
Dim toEmail As String
Dim subject As String
Dim body As String
Dim OL As New Outlook.Application
Dim olMail As Outlook.MailItem
Set olMail = OL.CreateItem(olMailItem)

If I stop the code on Call EmailApprovalP1() then press F8, Excel stops responding. Does anyone have any ideas on what could be causing this?

UPDATED 2-3-15

CODE THAT WORKED ON ALL COMPUTERS

Dim OL As Object
Set OL = CreateObject("Outlook.Application")
Dim olMail As Object
Set olMail = OL.CreateItem(olMailItem)

Upvotes: 0

Views: 2048

Answers (3)

CRUTER
CRUTER

Reputation: 866

Declaring in Memory as Object instead of Outlook.Application made the difference. Thanks for pointing me in the right direction @Eugene & @Jeanno!

Below is the code that worked:

Dim OL As Object
Set OL = CreateObject("Outlook.Application")
Dim olMail As Object
Set olMail = OL.CreateItem(olMailItem)

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49397

First of all, make sure that you added a reference to Outlook before running the code. You need to do that on each PC where you have to get the code running. The How to automate Outlook from another program article describes all the required steps for automating Outlook from another applications.

If it doesn't help, try to Step Into (F8) the function and find the exact line of code which causes the issue.

Upvotes: 1

Jeanno
Jeanno

Reputation: 2859

Use CreateObject instead of New. New is known to cause some instability issues.

Dim OL As Outlook.Application
Set OL = CreateObject("Outlook.Application")

Upvotes: 1

Related Questions