Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Variable value not populating

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Amber_D_Laws - 18 Jan 2006 15:46 GMT
Once more into the breech dear friends...

Contrary to what I thought, my problem with the variable for sending
the email has not been resolved. However, I have narrowed down what is
going on, I just don't know how to fix it.

Please see the snipet below:

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.
 '
 'Step 1 - Selects the quote sheet as the active sheet
 Sheets("QUOTE").Select
 '
 '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
--------------------

Using the "Locals window" I steped through the code, and after this
line, the variables had not been populated with a value. So, by the
time I got to...

Code:
--------------------
   '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
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(olMailItem)
 With OutMail
 .To = CtEA
 .CC = ""
 .BCC = ""
 .Subject = "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" & ".xls")
 .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
--------------------

Where the variables are called, there is no value to return, and so I
end up with the error message saying that the To, CC, or BCC must be
populated with something.

The the cells that the code should be reading have values calculated
from formulas, but in a diffrent module, the very same cells are used
in variables with the same name with total success.

Any ideas why the values are not being read? I am at a loss.

Amber:)

Signature

Amber_D_Laws

Toppers - 18 Jan 2006 16:28 GMT
Amber,
                 I tried the code as posted and it worked i.e. sent an
e-mail. I ssume from the posting some code is omitted so check you aren't
selecting another worksheet (other than "Quote)" before assigning the value
to ctEA.

Did you print (Debug)  Range("O25") or just ctEA?

> Once more into the breech dear friends...
>
[quoted text clipped - 91 lines]
>
> Amber:)
Amber_D_Laws - 18 Jan 2006 18:17 GMT
Thanks for responding Topper,

I am glad to know that the code dosen't contain some fatal flaw, and
that it is working for somebody. I have not used the Debug Print
command. Let me post all the code, and maybe you can find the problem.
I have been looking at it for days, and my eyes are crossing backwards
from it.

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.
 '
 'Step 1 - Selects the quote sheet as the active sheet
 Sheets("QUOTE").Select
 '
 '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 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"
 '
 '
 '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
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(olMailItem)
 With OutMail
 .To = CtEA
 .CC = ""
 .BCC = ""
 .Subject = "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" & ".xls")
 .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
--------------------

Signature

Amber_D_Laws

Toppers - 18 Jan 2006 19:47 GMT
Amber,
              It again ran successfully BUT when I looked at the value of
TO in the Locals Window it appeared not to be set, although it was.

Don't use the Locals Window: step through the macro using PF8 and look at
the value of the variables by placing the mouse over the variable name.

Re-confirm: I set Range("O25") in worksheet "QUOTES" to the TO -email
address (my own) and it was sent (and received) OK.

> Thanks for responding Topper,
>
[quoted text clipped - 83 lines]
>   End Sub
> --------------------
Amber_D_Laws - 18 Jan 2006 20:24 GMT
Ok...now I am really confused. Although, I am comforted that the coding
is not incorrect.
What could possibly cause it to be non functional for me, but function
correctly for you? Could some part of the worksheet itself be
corrupted, or God forbid, my Excel program?

Any suggestions?
Amber

Signature

Amber_D_Laws

Toppers - 19 Jan 2006 07:22 GMT
Amber,
              I confess to having no idea! If you want, you could send me
the whole workbook (if you think that would help) and I'll look at it here.

(Toppers@johntopley.fsnet.co.uk)

> Ok...now I am really confused. Although, I am comforted that the coding
> is not incorrect.
[quoted text clipped - 4 lines]
> Any suggestions?
> Amber
Amber_D_Laws - 25 Jan 2006 17:15 GMT
Hey Toppers,

Sorry for the long responce time, the lab got crazy for a few days. I
will send the file to you via email.

Thanks again!
Amber :)

Signature

Amber_D_Laws

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.