emailing worksheet problem

D

danny

all,

i use the following code to gather email addresses and then email a
worksheet to the gathered addressees. this routine works great in debug
mode. when i attempt to execute without the debugger it fails miserably.
any ideas?

thanks in advance for your help,

danny

Sub SendActiveWorkSheet()

Dim strEmailTo() As String
Dim strSubject As String
Dim strPrompt As String
Dim strWorksheetIndex As String
Dim intCounter As Integer
Dim intEmailCount As Integer
Dim intAnswer As Integer

intCounter = 2
intEmailCount = 0
strSubject = "EOM Spreadsheet/Report"

On Error GoTo ErrorExit

strWorksheetIndex = ActiveSheet.Name

intAnswer = MsgBox("You are about to email " & strWorksheetIndex & "
worksheet..." & vbCrLf & _
"Is this correct?", vbYesNo + vbQuestion, "Emailing " &
strWorksheetIndex)

If intAnswer = vbNo Then Exit Sub

Worksheets("Email Addresses").Activate

Cells(intCounter, 1).Select

Do While Cells(intCounter, 1) <> ""

intEmailCount = intCounter - 1

ReDim Preserve strEmailTo(1 To intEmailCount)

strEmailTo(intEmailCount) = Cells(intCounter, 1)
strPrompt = strPrompt & Cells(intCounter, 1) & vbCrLf
intCounter = intCounter + 1

Loop

If intEmailCount < 1 Then GoTo ErrorExit

intAnswer = MsgBox(strWorksheetIndex & "will be sent to: " & vbCrLf &
strPrompt & "Is this correct?", vbYesNo + vbQuestion, "Email Addresses")
If intAnswer = vbNo Then Exit Sub
ThisWorkbook.Sheets(strWorksheetIndex).Copy
With ActiveWorkbook
.SendMail Recipients:=Array(strEmailTo), Subject:=strSubject
.Close SaveChanges:=False
End With
Exit Sub

ErrorExit:
MsgBox "Warning! Email has not been sent!", vbCritical
End Sub
 
W

Wigi

Where does the code error out?

Start with the basics. Could you get simple email code examples to work? You
could look at the nice examples on the website of Ron De Bruin,
).
 
D

danny

wigi,

thanks for taking the time to reply.

i've been to ron's site, both today, and in the past. i've used his
examples as a model to what i've produced.

something goes sideways when i hit the .SendMail method.

when i step through the routine and monitor the task manager, EOMReports
excel instance is "running", book1 excel instance is "running" (i've made a
copy of the worksheet i want to email) and an instance of outlook is
"running". the email is sent successfully..

however, running the routine without the debugger, EOMReports excel instance
is "running", book1 excel instance is "not responding", 2 instances of
"Microsoft Excel - Book 1" are "not responding", Microsoft Office Outlook
instance is "running" and execution hangs.

thanks,

danny
 
D

danny

hey ron,

just getting to circle back on this.

i've reviewed link one and all seems in order.

i have done a copy/paste of the code found at the second link you
include...still, no joy. would you have any other suggestions?

thanks!

danny
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top