Good morning all,
I am currently using the With Outmail statement and the .HTMLbody tag to compose an HTML-formatted email. It works fine, however, since it is HTML, the end user is going to get a long email with much of it not needed. Here is the current code:
Code
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = EscalationsForm.cmbArea
.CC = ""
.BCC = EscalationsForm.tbSubmitterEmail
.Subject = "Case # " & tbCaseID & "; " & cmbWhichEscalation & " from: " & EscalationsForm.cmbDistrict & " District; " & EscalationsForm.cmbEscalation_Type & " " & EscalationsForm.cmbF50CorrectionTypeMain & " " & EscalationsForm.cmbF50NextAction
.HTMLbody = "The following email is a Receipt of the Escalation you just submitted. Your New Case Number is <b>" & tbCaseID & "</b>. The HRSSC will respond to this Escalation by close of business on " & Format(DateExpected, "Long Date") & ".<br><H2>Submission Information:</h2>" & "<b>Date Sent: </b>" & EscalationsForm.tbTodaysDate & "<br><b>Escalation Type: </b>" & EscalationsForm.cmbEscalation_Type & "<br><b>Area: </b>" & EscalationsForm.cmbArea & "<br><b>District: </b>" & EscalationsForm.cmbDistrict & "<br><br><b>Submitter's Name: </b>" & EscalationsForm.tbSubmitterName & "<br><b>Phone: </b>" & EscalationsForm.tbSubmitterPhone & "<br><b>E-mail/ACE ID: </b>" & EscalationsForm.tbSubmitterEmail _
& "<br><h3><font color=blue>Escalation to Rescind Offers:</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbRescReq & " - " & EscalationsForm.tbRescReqTitle & "<br><b>Applicant's Name: </b>" & EscalationsForm.tbRescApplicantName & "<br><b>Description: </b>" & EscalationsForm.tbRescDescription _
& "<br><h3><font color=blue>Escalation for Correction to Support Team (Requisition and/or eJPR):</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbCRSTReq & " - " & EscalationsForm.tbCRSTReqTitle & "<br>" & "<h4>Please Add The Following Information:</h4>" & "<b>Name: </b>" & EscalationsForm.tbCRSTNameAdd & "<br><b>EIN: </b>" & EscalationsForm.tbCRSTEINAdd & "<br><b>Support Team Role: </b>" & EscalationsForm.cmbCRSTSupportAdd & "<br>" & "<h4>Please Remove The Following Information:</h4>" & "<b>Name: </b>" & EscalationsForm.tbCRSTNameRem & "<br><b>EIN: </b>" & EscalationsForm.tbCRSTEINRem & "<br><b>Support Team Role: </b>" & EscalationsForm.cmbCRSTSupportRem & "<br><br><b>Additional Details: </b>" & EscalationsForm.tbCRSTAdditional _
& "<br><h3><font color=blue>Escalation for eJPR Correction/Second Request/Request to Cancel Posting:</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbCeJPRReq & " - " & EscalationsForm.tbCeJPRReqTitle & "<h4>Option Chosen (TRUE = Change Requested; FALSE = No Change Needed):</h4>" & "<br><b>Contact Information: </b>" & EscalationsForm.chkeJPRContact & "<br>" & EscalationsForm.tbeJPRContact & "<br><b>Location: </b>" & EscalationsForm.chkeJPRLocation & "<br>" & EscalationsForm.tbeJPRLocation & "<br><b>Posting Date: </b>" & EscalationsForm.chkeJPRPosting & "<br>" & EscalationsForm.tbeJPRPosting & "<br><b>Requisition Title: </b>" & EscalationsForm.chkeJPRRequisition _
& EscalationsForm.tbeJPRRequisition & "<br>" & "<h4>eJPR Not Released:</h4><b>Date eJPR Submitted: </b>" & EscalationsForm.tbeJPRDateSubmitted & "<br>" & "<h4>Request to Cancel Posting:</h4><b>Reason for Cancellation: </b>" & EscalationsForm.cmbCeJPRR2Cancel & "<br><br><b>Additional Details: </b>" & EscalationsForm.tbCeJPRAdditional _
& "<br><h3><font color=blue>Escalation for Pre-Hire List Correction:</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbPHLReq & " - " & EscalationsForm.tbPHLReqTitle & "<br><h4>Which Option Describes Your Issue?</h4><b>Late Initial Pre-Hire List: </b>" & EscalationsForm.opCPHLLatePHL & "<br><b>Pre-Hire List Does Not Include All Applicants Originally Requested: </b>" & EscalationsForm.opCPHLMissing & "<br><b>Late Subsequent Pre-Hire List: </b>" & EscalationsForm.opCPHLLateSubPHL & "<br><b>If True, Date Requested: </b>" & EscalationsForm.tbCPHLDate & "<br><br><b>Additional Details: </b>" & EscalationsForm.tbPHLAdditional _
& "<br><h3><font color=blue>Escalation for Interview Results:</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbIntRReq & " - " & EscalationsForm.tbIntRReqTitle & "<h4>Which Option Best Describes Your Issue:</h4><b>Interview Results Submitted - Selections and Offers Have Not Been Made: </b>" & EscalationsForm.opIntROffersNotMade & "<br><br><b>Selections Not Made According to Selecting Officials/Interviewer's Expectations: </b>" & EscalationsForm.opIntRIncorrectSelections & "<br><b>If True, Details: </b>" & EscalationsForm.tbIntRDetails & "<br><br><b>Incorrect Number of Vacancies Filled: </b>" & EscalationsForm.opIntRVacanciesFilled & "<br><b>If True, Correct Number of Vacancies: </b>" & EscalationsForm.tbIntRCorrectVacancies & "<br><b>If True, Reason Vacancies Changed: </b>" & EscalationsForm.cmbIntRVacancies & "<br><br><b>Application Rejected in Error: </b>" & EscalationsForm.opIntRRejectedInError _
& "<br><b>Applicant's Name: </b>" & EscalationsForm.tbIntRApplicantNameRej & "<br><b>Reason Rejected in Error: </b>" & EscalationsForm.cmbIntRRejectedReason & "<br><br><b>Additional Comments/Details: </b>" & EscalationsForm.tbIntRAdditional _
& "<br><h3><font color=blue>Escalation for Unprocessed Hire Actions:</h3></font><h4>Applicant Details:</h4><b>Applicant's Name: </b>" & EscalationsForm.tbUHAApplicantName & "<br><b>Requisition #: </b>" & EscalationsForm.tbUHAReq & "<br><b>Applicant's EIN: </b>" & EscalationsForm.tbUHAEIN & "<br><b>Last 4 of Applicant's SSN: </b>" & EscalationsForm.tbUHALast4SSN & "<br><b>Applicant's Position #: </b>" & EscalationsForm.tbUHAPosition & "<br><b>Applicant's Effective Date: </b>" & EscalationsForm.tbUHAEffectiveDate & "<br><b>Which Form Are You Attaching to Correct: </b>" & EscalationsForm.cmbUHAForms _
& "<br><h3><font color=blue>Escalation for Form 50 Corrections:</h3></font><h4>Employee's Details:</h4><b>Employee's Name: </b>" & EscalationsForm.tbF50ApplicantName & "<br><b>Requisition #: </b>NB" & EscalationsForm.tbF50Req & "<br><b>Employee's EIN: </b>" & EscalationsForm.tbF50EIN & "<br><b>Position #: </b>" & EscalationsForm.tbF50Position & "<br><b>Employee's Effective Date: </b>" & EscalationsForm.tbF50EffectiveDate & "<br><br><b>Primary Action: </b>" & EscalationsForm.cmbF50CorrectionTypeMain & "<br><b>Secondary Action: </b>" & EscalationsForm.cmbF50NextAction & "<br><b>Final Action, If Needed: </b>" & EscalationsForm.cmbF50FinalAction & "<br><br><b>Additional Details: </b>" & EscalationsForm.tbF50Additional _
& "<br><h3><font color=blue>Escalation for Relative Standing Correction:</h3></font><b>Bid Cluster: </b>BC" & EscalationsForm.tbRSCBidCluster & "<br><b>Effective Date: </b>" & EscalationsForm.tbRSCEffectiveDate & "<br><b>Applicant's Name: </b>" & EscalationsForm.tbRSCNameCorrection & "<br><b>EIN: </b>" & EscalationsForm.tbRSCEINCorrection & "<h4>Corrections</h4><b>Current Seniority Info: <br>Date: </b>" & EscalationsForm.tbRSCCurrentDate & "<b>Rank: </b>" & EscalationsForm.tbRSCCurrentRank & "<b>Craft: </b>" & EscalationsForm.cmbRSCCurrentCraft & "<br><br><b>Expected Seniority Info: <br>Date: </b>" & EscalationsForm.tbRSCExpectedDate & "<b>Rank: </b>" & EscalationsForm.tbRSCExpectedRank & "<b>Craft: </b>" & EscalationsForm.cmbRSCExpectedCraft _
& "<h4>Initial Calculation</h4><b>Please Select The Crafts That Require Calculation:<br><br>City Carrier Assistants: </b>" & EscalationsForm.chkRSCCraftCA & "<br><b>PSE Clerks: </b>" & EscalationsForm.chkRSCCraftCK & "<br><b>Mail Handler Assistants: </b>" & EscalationsForm.chkRSCCraftMH & "<br><b>PSE Maintenance: </b>" & EscalationsForm.chkRSCCraftMN & "<br><b>PSE Motor Vehicle Services: </b>" & EscalationsForm.chkRSCCraftMV & "<br><b>Rural Carrier Assistants: </b>" & EscalationsForm.chkRSCCraftRU & "<br><br><b>Reason for the Request: </b>" & EscalationsForm.cmbRSCReason & "<br><br><b>Additional Comments/Details: </b>" & EscalationsForm.tbRSCAdditional
.Display
' .Send 'or use .Display
.Attachments.Add EscalationsForm.tbRescDocAttached.Value & EscalationsForm.tbUHADocAttached.Value & EscalationsForm.tbRSCDocAttached.Value
If tbRescDocAttached.Value <> "" Then
.Attach tbRescDocAttached.Value
ElseIf tbUHADocAttached.Value <> "" Then
.Attach tbUHADocAttached.Value
ElseIf tbRSCDocAttached.Value <> "" Then
.Attach tbRSCAttached.Value
End If
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End WithWhat I am attempting to do is to use an If Else Statement combined with a String to use the field "cmbEscalation_Type" to determine which String the .HTMLBody tag should use. This way, I can make a different .HTMLbody formatted email for each of the choices in the cmbEscalation_Type combobox. I started off with the first line and I'm getting a "Compile error: Object Required" Error. It's pointing to the last value in the string, "EscalationsForm.tbRescDescription" I haven't even dealt with the change in conditions yet, but here is the code that generates that error:
Code
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim RescindEmail As String
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set RescindEmail = "The following email is a Receipt of the Escalation you just submitted. Your New Case Number is <b>" & tbCaseID & "</b>. The HRSSC will respond to this Escalation by close of business on " & Format(DateExpected, "Long Date") & ".<br><H2>Submission Information:</h2>" & "<b>Date Sent: </b>" & EscalationsForm.tbTodaysDate & "<br><b>Escalation Type: </b>" & EscalationsForm.cmbEscalation_Type & "<br><b>Area: </b>" & EscalationsForm.cmbArea & "<br><b>District: </b>" & EscalationsForm.cmbDistrict & "<br><br><b>Submitter's Name: </b>" & EscalationsForm.tbSubmitterName & "<br><b>Phone: </b>" & EscalationsForm.tbSubmitterPhone & "<br><b>E-mail/ACE ID: </b>" & EscalationsForm.tbSubmitterEmail _
& "<br><h3><font color=blue>Escalation to Rescind Offers:</h3></font>" & "<b>Requisition Information: </b>NB" & EscalationsForm.tbRescReq & " - " & EscalationsForm.tbRescReqTitle & "<br><b>Applicant's Name: </b>" & EscalationsForm.tbRescApplicantName & "<br><b>Description: </b>" & EscalationsForm.tbRescDescription
On Error Resume Next
With OutMail
.To = "EscalationsForm.cmbArea
.CC = ""
.BCC = EscalationsForm.tbSubmitterEmail
.Subject = "Case # " & tbCaseID & "; " & cmbWhichEscalation & " from: " & EscalationsForm.cmbDistrict & " District; " & EscalationsForm.cmbEscalation_Type & " " & EscalationsForm.cmbF50CorrectionTypeMain & " " & EscalationsForm.cmbF50NextAction
.HTMLbody = RescindEmail
.Display
' .Send 'or use .Display
.Attachments.Add EscalationsForm.tbRescDocAttached.Value & EscalationsForm.tbUHADocAttached.Value & EscalationsForm.tbRSCDocAttached.Value
If tbRescDocAttached.Value <> "" Then
.Attach tbRescDocAttached.Value
ElseIf tbUHADocAttached.Value <> "" Then
.Attach tbUHADocAttached.Value
ElseIf tbRSCDocAttached.Value <> "" Then
.Attach tbRSCAttached.Value
End If
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = NothingAny help with the first part or even the conditional portion is greatly appreciated by this VBA Novice + (Still a novice, but gaining experience, lol). Thanks!
