talytech
12-18-2008, 01:24 PM
I'm trying to make a reference to my Userform named "CSSProcessing" from a function. What's the syntax for that? For example, my code returns an error on the following statement:
If UserForms!CSSProcessing![chkAccepted].Value = True And UserForms!CSSProcessing![chkAccepted].Value = False Then
exceptapprvd = "ACCEPTED"
Here is the full code:
myName = ""
For I = 20 To lastrow
'Get Fields
ctrfname = Cells(I, "b").Value
ctrlname = Cells(I, "f").Value
ctrmid = Cells(I, "e").Value
ctrSSN = Cells(I, "i").Value
ctrDOB = Cells(I, "j").Value
ctrVendor = Cells(I, "k").Value
If myName = "" Then
myName = UCase(ctrfname) & Chr(32) & UCase(ctrlname) & ", " & ctrVendor
Else
myName = myName & Chr(13) & UCase(ctrfname) & Chr(32) & UCase(ctrlname) & ", " & ctrVendor
End If
Next
If UserForms!CSSProcessing![chkAccepted].Value = True And UserForms!CSSProcessing![chkAccepted].Value = False Then
exceptapprvd = "ACCEPTED"
theBody = "An exception request for temporary facilities or systems access has been ACCEPTED and processed on " & Date & "for the following individuals:"
theBody = theBody & Chr(13) & myName
theBody = theBody & Chr(13) & "This exception grants contractors a one-time 15 day exception for a temporary access badge or temporary systems access."
theBody = theBody & " If the individual is being issued a badge, please set the expiration date on the temporary access badge issued to 15 days beyond the badge activation date."
ElseIf CSSProcessing![chkAccepted].Value = False And UserForms!CSSProcessing![chkAccepted].Value = True Then
exceptapprvd = "REJECTED"
theBody = "An exception request for temporary facilities or systems access was processed and REJECTED on " & Date & "for the following individuals:"
theBody = theBody & Chr(13) & myName
End If
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
.Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject
Set ToContact = .Recipients.Add("e3utbl") '
.Body = theBody '''' Set ToContact = .Recipients.Add("r7uwta")
.Send ' sends the e-mail message (puts it in the Outbox)
End with
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
End Function
If UserForms!CSSProcessing![chkAccepted].Value = True And UserForms!CSSProcessing![chkAccepted].Value = False Then
exceptapprvd = "ACCEPTED"
Here is the full code:
myName = ""
For I = 20 To lastrow
'Get Fields
ctrfname = Cells(I, "b").Value
ctrlname = Cells(I, "f").Value
ctrmid = Cells(I, "e").Value
ctrSSN = Cells(I, "i").Value
ctrDOB = Cells(I, "j").Value
ctrVendor = Cells(I, "k").Value
If myName = "" Then
myName = UCase(ctrfname) & Chr(32) & UCase(ctrlname) & ", " & ctrVendor
Else
myName = myName & Chr(13) & UCase(ctrfname) & Chr(32) & UCase(ctrlname) & ", " & ctrVendor
End If
Next
If UserForms!CSSProcessing![chkAccepted].Value = True And UserForms!CSSProcessing![chkAccepted].Value = False Then
exceptapprvd = "ACCEPTED"
theBody = "An exception request for temporary facilities or systems access has been ACCEPTED and processed on " & Date & "for the following individuals:"
theBody = theBody & Chr(13) & myName
theBody = theBody & Chr(13) & "This exception grants contractors a one-time 15 day exception for a temporary access badge or temporary systems access."
theBody = theBody & " If the individual is being issued a badge, please set the expiration date on the temporary access badge issued to 15 days beyond the badge activation date."
ElseIf CSSProcessing![chkAccepted].Value = False And UserForms!CSSProcessing![chkAccepted].Value = True Then
exceptapprvd = "REJECTED"
theBody = "An exception request for temporary facilities or systems access was processed and REJECTED on " & Date & "for the following individuals:"
theBody = theBody & Chr(13) & myName
End If
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail message
With olMailItem
.Subject = "EXCEPTION REQUEST FOR TEMPORARY FACILITIES ACCESS (" & exceptapprvd & ")" ' message subject
Set ToContact = .Recipients.Add("e3utbl") '
.Body = theBody '''' Set ToContact = .Recipients.Add("r7uwta")
.Send ' sends the e-mail message (puts it in the Outbox)
End with
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
End Function