mardi 30 janvier 2018

Adding If-Then statement to email text

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