Variable not working as email address

[Amber_D_Laws]

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, Dave, anyone...Thanks in advance!

Please Login or Register  to view this content.

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

[Ron de Bruin]

Hi Amber

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


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


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> 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]

the "QUOTE" sheet

Hi Amber

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


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


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > 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]

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!

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > 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
>

[Ron de Bruin]

>> >> > 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > 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"
>> >> <[email protected]> wrote
>> in
>> >> message
>> >> news:[email protected]...
>> >> >
>> >> > 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
>

[Amber_D_Laws]

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]

Bump to top of list

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Amber_D_Laws]

No problem...sending by email

[Ron de Bruin]

Typo

..Suject = "Your quote is ready."

must be

..Subject = "Your quote is ready."



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


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Amber_D_Laws]

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.

Thank You !

[Ron de Bruin]

You are welcome


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


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>