|  | emailing worksheet problem | | |
| | | danny |  |
|  Posted: Thu Jul 24, 2008 6:18 pm Post subject: emailing worksheet problem |  |
| |  | |
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 |
| |
| | | Wigi | |
| | Posted: Thu Jul 24, 2008 6:31 pm Post subject: RE: emailing worksheet problem | |
| | | |
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,
LINK).
--
Wigi
LINK = Excel/VBA, soccer and music
"danny" wrote:
| Quote: | 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
|
|
| |
| | | danny | |
| | Posted: Thu Jul 24, 2008 7:03 pm Post subject: RE: emailing worksheet problem | |
| | | |
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
"Wigi" wrote:
| Quote: | 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,
LINK).
--
Wigi
LINK = Excel/VBA, soccer and music
"danny" wrote:
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
|
|
| |
| | | Ron de Bruin | |
| | Posted: Thu Jul 24, 2008 9:16 pm Post subject: Re: emailing worksheet problem | |
| | | |
Hi Danny
If a basic example from my site is not working check out
LINK
But first try this macro
LINK
--
Regards Ron de Bruin
LINK
"danny" <danny@discussions.microsoft.com> wrote in message news:E3892F2D-9124-450F-A0CB-8254837FA6C1@microsoft.com...
| Quote: | 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
"Wigi" wrote:
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,
LINK).
--
Wigi
LINK = Excel/VBA, soccer and music
"danny" wrote:
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
|
|
| |
| | | danny | |
| | Posted: Mon Aug 04, 2008 7:52 pm Post subject: Re: emailing worksheet problem | |
| | | |
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
"Ron de Bruin" wrote:
| Quote: | Hi Danny
If a basic example from my site is not working check out
LINK
But first try this macro
LINK
--
Regards Ron de Bruin
LINK
"danny" <danny@discussions.microsoft.com> wrote in message news:E3892F2D-9124-450F-A0CB-8254837FA6C1@microsoft.com...
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
"Wigi" wrote:
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,
LINK).
--
Wigi
LINK = Excel/VBA, soccer and music
"danny" wrote:
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
|
|
| |
|
|
