Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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:) -- 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=502488 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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? "Amber_D_Laws" wrote: 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:) -- 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=502488 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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 -------------------- -- 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=502488 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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. "Amber_D_Laws" wrote: 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 -------------------- -- 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=502488 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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 -- 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=502488 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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. ) "Amber_D_Laws" wrote: 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 -- 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=502488 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable value not populating
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 :) -- 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=502488 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a summary of variable size. | Excel Discussion (Misc queries) | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Populating a cell with the current value of a variable | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |