I'm trying to add an If-Then statement to my VBA in order to create two different email texts based on whether a specific field is populated in a table.
If no data is in the [VendorID/UIN] field in the t1stNoticeEmails table, I'd like this text:
"Please provide a current remit-to address as soon as possible so we can resend the check(s) to the intended recipient(s). The funds from this check will remain as a charge against the FOAPALs utilized in the transaction until this matter is resolved."
If there is data in the [VendorID/UIN] field in the t1stNoticeEmails table, I'd like this additional text:
"In addition, the Vendor ID associated with this transaction may need to be updated. Please contact Vendor Maintenance."
Here's the code:
Sub FirstEmail_IncorrectAddress_ReviewVBA()
Dim rst As DAO.Recordset
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim rst2 As DAO.Recordset
Dim strTableBeg As String
Dim strTableBody As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strTableHeader As String
Dim strFntEnd As String
Dim CheckNum As String
Dim NameOfRecipient As String
Dim StrSQL1 As String
Dim NameSpaceOutlook As Outlook.Namespace
gPARAttachment = "S:\UPAY\Z_NewStructure\SupportOperations\Projects\Returned Checks\Email Text\PaymentActionRequestForm.pdf"
'SEND FIRST NOTICE EMAILS'
'------------------'
Set rst2 = CurrentDb.OpenRecordset("select distinct ContactEmails from t1stNoticeEmails WHERE CheckReturnReason = 'IncorrectAddress'")
If rst2.RecordCount = 0 Then 'checks if recordset returns any records and continues if records found and exits if no records found
Exit Sub
End If
rst2.MoveFirst
'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
'Do Until rst2.EOF
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
'Define format for output
strTableBeg = "<table border=1 cellpadding=3 cellspacing=0>"
strTableEnd = "</table>"
strTableHeader = "<font size=3 face='Calibri'><b>" & _
"<tr bgcolor=#4DB84D>" & _
td("CheckNumber") & _
td("PayeeName") & _
td("VendorID") & _
td("DocNo / ERNo / PONo") & _
td("Amount") & _
td("CheckDate") & _
td("OriginalCheckAddress1") & _
td("OriginalCheckAddress2") & _
td("OriginalCheckCity") & _
td("OriginalCheckState") & _
td("OriginalCheckZip") & _
"</tr></b></font>"
strFntNormal = "<font color=black face='Calibri' size=3>"
strFntEnd = "</font>"
Set rst = CurrentDb.OpenRecordset("SELECT * FROM t1stNoticeEmails where ContactEmails='" & rst2!ContactEmails & "' AND CheckReturnReason = 'IncorrectAddress' Order by FullName asc")
If rst.RecordCount = 0 Then
rst2.Close
Set rst2 = Nothing
Exit Sub
End If
rst.MoveFirst
NameOfRecipient = rst!FullName
CheckNum = rst!CheckNumber
'Build HTML Output for the DataSet
strTableBody = strTableBeg & strFntNormal & strTableHeader
Do Until rst.EOF
strTableBody = _
strTableBody & _
"<tr>" & _
"<TD nowrap>" & rst!CheckNumber & "</TD>" & _
"<TD nowrap>" & rst!FullName & "</TD>" & _
"<TD nowrap>" & rst![VendorID/UIN] & "</TD>" & _
"<TD nowrap>" & rst![DocNo / ERNo / PONo] & "</TD>" & _
"<TD align='right' nowrap>" & Format(rst!AmountDue, "currency") & "</TD>" & _
"<TD nowrap>" & rst!OriginalCheckDate & "</TD>" & _
"<TD align='left' nowrap>" & rst!OriginalCheckAddress1 & "</TD>" & _
"<TD align='left' nowrap>" & rst!OriginalCheckAddress2 & "</TD>" & _
"<TD align='left' nowrap>" & rst!OriginalCheckCity & "</TD>" & _
"<TD align='left' nowrap>" & rst!OriginalCheckState & "</TD>" & _
"<TD align='left' nowrap>" & rst!OriginalCheckZip & "</TD>" & _
"</tr>"
rst.MoveNext
Loop
'rst.MoveFirst
strTableBody = strTableBody & strFntEnd & strTableEnd
'rst.Close
'Set rst2 = CurrentDb.OpenRecordset("select distinct ch_email from t_TCard_CH_Email")
'rst2.MoveFirst
Call CaptureIABodyText
With objMail
'Set body format to HTML
.To = rst2!ContactEmails
.BCC = gIAEmailBCC
.Subject = gIAEmailSubject & " - Check# " & CheckNum & " - " & NameOfRecipient
.BodyFormat = olFormatHTML
.HTMLBody = .HTMLBody & gIABodyText
.HTMLBody = .HTMLBody & "<HTML><BODY>" & strFntNormal & strTableBody & " </BODY></HTML>"
.HTMLBody = .HTMLBody & gIABodySig
.SentOnBehalfOfName = "UP-ARS@uillinois.edu"
.Display
'.Send
End With
rst2.MoveNext
'Loop
rst.Close
Set rst = Nothing
rst2.Close
Set rst2 = Nothing
End Sub
Aucun commentaire:
Enregistrer un commentaire