Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great job
at it. So first let me say thank you to him for being so patient, and
for explaining things clearly. Some of the code I will post is Ron's,
and if you have come across this post by way of a search on email.
Please see his site first; it may help to clear things up for you. (See
the bottom of this post for links to Ron's sites.) However, patient as
he has been, I can't expect him to continue to hold my hand on this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a lot of
unnecessary steps have been eliminated. This gist of it is for the
button to trigger the protection of one sheet (the quote itself), copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save the
file.
I am stuck at the sending. For some reason the debugger when stepping
through this, stops at the .Send, and tells me that the .To, .CC, or
..BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option, and saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote has been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and is now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo, "Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please see the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward to doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name & ".xls"
MsgBox "The quote has been successuflly emailed to the address listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name & ".xls"
MsgBox "The quote has been saved in a form suitable for email." & Chr(13) & "Please use the file named : " & ThisWorkbook.Name & " when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Hi Amber

In which sheet is
CtEA = Range("O25").Text


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
news:Amber_D_Laws.21qxey_1137446101.7542@excelforu m-nospam.com...

Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great job
at it. So first let me say thank you to him for being so patient, and
for explaining things clearly. Some of the code I will post is Ron's,
and if you have come across this post by way of a search on email.
Please see his site first; it may help to clear things up for you. (See
the bottom of this post for links to Ron's sites.) However, patient as
he has been, I can't expect him to continue to hold my hand on this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a lot of
unnecessary steps have been eliminated. This gist of it is for the
button to trigger the protection of one sheet (the quote itself), copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save the
file.
I am stuck at the sending. For some reason the debugger when stepping
through this, stops at the .Send, and tells me that the .To, .CC, or
BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option, and saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote has been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and is now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo, "Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please see the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward to doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been successuflly emailed to the address listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been saved in a form suitable for email." & Chr(13) & "Please use the file named : " & ThisWorkbook.Name &
" when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

the "QUOTE" sheet


Ron de Bruin Wrote:
Hi Amber

In which sheet is
CtEA = Range("O25").Text


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote in
message
news:Amber_D_Laws.21qxey_1137446101.7542@excelforu m-nospam.com...

Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great
job
at it. So first let me say thank you to him for being so patient,
and
for explaining things clearly. Some of the code I will post is
Ron's,
and if you have come across this post by way of a search on email.
Please see his site first; it may help to clear things up for you.
(See
the bottom of this post for links to Ron's sites.) However, patient
as
he has been, I can't expect him to continue to hold my hand on this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a lot
of
unnecessary steps have been eliminated. This gist of it is for the
button to trigger the protection of one sheet (the quote itself),
copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save the
file.
I am stuck at the sending. For some reason the debugger when
stepping
through this, stops at the .Send, and tells me that the .To, .CC, or
BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option, and
saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote has
been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and is
now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo,
"Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please see
the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward to
doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and
Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been successuflly emailed to the address
listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been saved in a form suitable for email." &
Chr(13) & "Please use the file named : " & ThisWorkbook.Name &
" when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

If your QUOTE sheet is not active you have this problem

Select the sheet first before you define them
Now you do after you define the strings


'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select

'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
...

the "QUOTE" sheet


Ron de Bruin Wrote:
Hi Amber

In which sheet is
CtEA = Range("O25").Text


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote in
message
news:Amber_D_Laws.21qxey_1137446101.7542@excelforu m-nospam.com...

Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great
job
at it. So first let me say thank you to him for being so patient,
and
for explaining things clearly. Some of the code I will post is
Ron's,
and if you have come across this post by way of a search on email.
Please see his site first; it may help to clear things up for you.
(See
the bottom of this post for links to Ron's sites.) However, patient
as
he has been, I can't expect him to continue to hold my hand on this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a lot
of
unnecessary steps have been eliminated. This gist of it is for the
button to trigger the protection of one sheet (the quote itself),
copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save the
file.
I am stuck at the sending. For some reason the debugger when
stepping
through this, stops at the .Send, and tells me that the .To, .CC, or
BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option, and
saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote has
been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and is
now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo,
"Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please see
the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward to
doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and
Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been successuflly emailed to the address
listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been saved in a form suitable for email." &
Chr(13) & "Please use the file named : " & ThisWorkbook.Name &
" when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

My god, could it really be that simple....
So what is the logic behind why it works one way, and not the other.

Thanks again!


Ron de Bruin Wrote:
If your QUOTE sheet is not active you have this problem

Select the sheet first before you define them
Now you do after you define the strings


'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select

'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote in message
...

the "QUOTE" sheet


Ron de Bruin Wrote:
Hi Amber

In which sheet is
CtEA = Range("O25").Text


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote
in
message
news:Amber_D_Laws.21qxey_1137446101.7542@excelforu m-nospam.com...

Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great
job
at it. So first let me say thank you to him for being so patient,
and
for explaining things clearly. Some of the code I will post is
Ron's,
and if you have come across this post by way of a search on
email.
Please see his site first; it may help to clear things up for
you.
(See
the bottom of this post for links to Ron's sites.) However,
patient
as
he has been, I can't expect him to continue to hold my hand on
this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a
lot
of
unnecessary steps have been eliminated. This gist of it is for
the
button to trigger the protection of one sheet (the quote itself),
copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save
the
file.
I am stuck at the sending. For some reason the debugger when
stepping
through this, stops at the .Send, and tells me that the .To, .CC,
or
BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual
email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option,
and
saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote
has
been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and
is
now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo,
"Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please
see
the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward
to
doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and
Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been successuflly emailed to the address
listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been saved in a form suitable for email."
&
Chr(13) & "Please use the file named : " & ThisWorkbook.Name &
" when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws


------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

CtEA = Range("O25").Text
This use always O25 on the activesheet

You can select the QUOTE sheet first or include the sheet name
CtEA = sheets("QUOTE ").Range("O25").Text



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
news:Amber_D_Laws.21r06y_1137449701.8604@excelforu m-nospam.com...

My god, could it really be that simple....
So what is the logic behind why it works one way, and not the other.

Thanks again!


Ron de Bruin Wrote:
If your QUOTE sheet is not active you have this problem

Select the sheet first before you define them
Now you do after you define the strings


'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select

'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote in message
...

the "QUOTE" sheet


Ron de Bruin Wrote:
Hi Amber

In which sheet is
CtEA = Range("O25").Text


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws"
wrote
in
message
news:Amber_D_Laws.21qxey_1137446101.7542@excelforu m-nospam.com...

Hello again,

Same quote module, same button, new issue.

Ron de Bruin has been helping me with this one, and doing a great
job
at it. So first let me say thank you to him for being so patient,
and
for explaining things clearly. Some of the code I will post is
Ron's,
and if you have come across this post by way of a search on
email.
Please see his site first; it may help to clear things up for
you.
(See
the bottom of this post for links to Ron's sites.) However,
patient
as
he has been, I can't expect him to continue to hold my hand on
this
one, so I am opening the question back up to the forum.

Now on to the meat of the matter...

Thanks to Ron this code has been cleaned up considerably, and a
lot
of
unnecessary steps have been eliminated. This gist of it is for
the
button to trigger the protection of one sheet (the quote itself),
copy
it, then via a yes/no msg box either send it as a single sheet
attachment, and save the file under a unique name, or just save
the
file.
I am stuck at the sending. For some reason the debugger when
stepping
through this, stops at the .Send, and tells me that the .To, .CC,
or
BCC must have a name in them. Well, initially I started with a
variable in the .To spot. However, I tested it with an actual
email
address between the quotes, and the same message appeared.

Please help! Ron, Dan, anyone...Thanks in advance!


Code:
--------------------
Private Sub CommandButton2_Click()
'
'
'Secures the quote, emails it to the client via a user option,
and
saves the file
'in an emailable form for later use.
'
'
'Prep 1 - Declares the varibles
Dim PMNm As String
Dim CtNm As String
Dim CtEA As String
Dim Answer
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim WBok As Workbook
Dim StDt As String
'
'
'Prep 2 - Defins the varibles
CtNm = Range("O20").Text
CtEA = Range("O25").Text
PMNm = Range("I12").Text
'
'
'Step 1 - Selects the quote sheet as the active sheet
Sheets("QUOTE").Select
'
'Step 2 - Prepares the quote sheet to be emailed
ActiveSheet.Unprotect Password:="STLMOB@900"
With ActiveSheet.UsedRange.Cells
.Locked = True
.FormulaHidden = True
End With
ActiveSheet.Columns("AD:AI").Hidden = True
ActiveSheet.Protect Password:="STLMOB@900"
ActiveWorkbook.Save
'
'
'Step 3 - Displays a message informing the user that the quote
has
been protected and gives
'them an option to email the quote now
Answer = MsgBox("The quote has been successfully protected and
is
now safe to email." & _
Chr(13) & "Would you like to send the quote now?", vbYesNo,
"Security Placement Complete!")
'
'Defines the behavior for the Yes and No buttons
If Answer = vbYes Then
Application.ScreenUpdating = False
Sheets("QUOTE").Copy
Set WBok = ActiveWorkbook
With WBok
.SaveAs "Prepared Quote - " & ThisWorkbook.Name & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = CtEA
.CC = ""
.BCC = ""
.Suject = "Your quote is ready."
.Body = "The quote you requested is ready for your review.Please
see
the attached document." _
& Chr(13) & "Thank you for choosing STL Mobile, we look forward
to
doing business with you." _
& Chr(13) & "Regards," & Chr(13) & PMNm
.Attachments.Add WBok.FullName
.Attachments.Add ("X:\FEE SCHEDULE & QUOTE MODULE\STL Terms and
Conditions")
.Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been successuflly emailed to the address
listed.", 64, "Process Complete."
Else
ActiveWorkbook.SaveAs Filename:="X:\FEE SCHEDULE & QUOTE
MODULE\Created Quotes\" & "Email Version of " & ThisWorkbook.Name &
".xls"
MsgBox "The quote has been saved in a form suitable for email."
&
Chr(13) & "Please use the file named : " & ThisWorkbook.Name &
" when emailing the client.", 64, "Process Complete."
End If
End Sub
--------------------


Links to Ron's Sites:
http://www.rondebruin.nl - general info.
http://www.rondebruin.nl/sendmail.htm -specifically about
sending email


--
Amber_D_Laws


------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
View this thread:
http://www.excelforum.com/showthread...hreadid=501819



--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Ok. That fixed the first problem, and I continued stepping through.
I got to .Body...
and I have another error message.
"Object doesn't support this property or method."


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Bump to top of list


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Hi Amber

Send me private what you have now and I look at it.

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
news:Amber_D_Laws.21r0vy_1137450602.1882@excelforu m-nospam.com...

Ok. That fixed the first problem, and I continued stepping through.
I got to .Body...
and I have another error message.
"Object doesn't support this property or method."


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

No problem...sending by email


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Typo

..Suject = "Your quote is ready."

must be

..Subject = "Your quote is ready."



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
news:Amber_D_Laws.21sf4a_1137515705.1862@excelforu m-nospam.com...

No problem...sending by email


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

Well I just feel like an idiot! I can't believe it was that simple. I
scratched my head all day yesterday on that one.

Thanks Ron...What would I do without you.

I will keep you posted. I am almost through with this thing, so
hopefully, nothing else will stump me.

To Dave Peterson who was the first to help me when I arrived on the
forum, and to you Ron, who has helped me even with the silly stuff like
this, I owe my eternal thanks.

T h a n k Y o u !


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819

You are welcome


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" wrote in message
news:Amber_D_Laws.21sicy_1137519902.6893@excelforu m-nospam.com...

Well I just feel like an idiot! I can't believe it was that simple. I
scratched my head all day yesterday on that one.

Thanks Ron...What would I do without you.

I will keep you posted. I am almost through with this thing, so
hopefully, nothing else will stump me.

To Dave Peterson who was the first to help me when I arrived on the
forum, and to you Ron, who has helped me even with the silly stuff like
this, I owe my eternal thanks.

T h a n k Y o u !


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=501819