Solved

VBA Conditional Statement in .HTMLBody

Posted on 2014-04-07
4
429 Views
Last Modified: 2014-04-07
I am usung VBA code in Microsoft Access to send an email. In the .HTMLBody Tag I have a field that I wish to use an If or Case Statement.

So if " & Forms!PXF7085!PXF7085A!HearingDate & " is NULL then " & Forms!PXF7085!PXF7085A!ArbitrationDate & " ELSE " & Forms!PXF7085!PXF7085A!MediationDate & "

Basically I want to populate this field with whichever field isn't NULL



With objMail
        'Set body format to HTML
        .BodyFormat = olFormatHTML
        .To = "Gaudiosi, Thomas S <tsgaudiosi@ahpis.com>; Moore II, Stu <smoore@hpix-ins.com>; Mellott, Curtis, J. <cmellott@hpix-ins.com>; Albright, Nancy <nalbright@hpix-ins.com>; Detweiler, Karl L. <kldetweiler@hpix-ins.com>; Fee, Richard F. <rfee@hpix-ins.com>; Gaudiosi, Nicholas S. <nsgaudiosi@ahpis.com>; Hammann, Emily Jordan <EJHammann@hpix-ins.com>; Jones, D. Scott <SJones@ahpis.com>; Marcavage, Robert J. <rmarcavage@hpix-ins.com>; Scheffel, Kristen <KMScheffel@hpix-ins.com>; Schleider, Brian S. <bsschleider@ahpis.com>; Toczylowski, Regina M. <RTroy@ahpis.com>; Trojecki, Joseph <JTrojecki@hpix-ins.com>; Zook, Tammy W. <TWZook@hpix-ins.com>"
        .CC = "Coleman, Karen A. <kcoleman@hpix-ins.com>; Sassano, Jennifer B. <jsassano@hpix-ins.com>; Burk, Michael J. <mburk@hpix-ins.com>; Capp, Rita <rcapp@hpix-ins.com>; Wentle, Nicole M. <nwentle@hpix-ins.com>"
        .Subject = "" & Forms!PXF7085!PXF7085A!SubClaimant & " vs " & Forms!PXF7085!PXF7085A!SubInsured & " / " & Forms!PXF7085!PXF7085A!ClaimNo
        .HTMLBody = "<table><tr><td align=left colspan=8></td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Claimant:</b></font> " & Forms!PXF7085!PXF7085A!Claimant & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Insured:</b></font> " & Forms!PXF7085!PXF7085A!Insured & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Group:</b></font> " & Forms!PXF7085!PXF7085A!GroupName & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Policy:</b></font> " & Forms!PXF7085!PXF7085A!Policy & "</td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Trial Date:</b></font> " [b]& Forms!PXF7085!PXF7085A!HearingDate &[/b] "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Loss Date:</b></font> " & Forms!PXF7085!PXF7085A![Loss Date] & "</td><td width=10></td>" _
                    & "<td align=left colspan=3><font color=000080><b>Notice Date:</b></font> " & Forms!PXF7085!PXF7085A![Report Date] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Allegation:</b></font> " & Forms!PXF7085!PXF7085A!Allegation & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Indemnity Reserves:</b></font> " & Forms!PXF7085!PXF7085A!IndemRsv & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Venue:</b></font> " & Forms!PXF7085!PXF7085A!Venue & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Examiner:</b></font> " & Forms!PXF7085!PXF7085A!Examiner & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Plaintiff Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!PlaintiffFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Defense Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!DefenseFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Company Position:</b></font> " & Forms!PXF7085!PXF7085A![Company Position] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Judge:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Co-defense:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Update:</b></font> <br><br><br><br></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Case Summary:</b></font></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>For internal users, please click on the link below for more information:</b></font></td></tr></table> "
         .Display
         '.Send
    End With

Open in new window

0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39983196
Hi,

pls try

With objMail
        'Set body format to HTML
        .BodyFormat = olFormatHTML
        .To = "Gaudiosi, Thomas S <tsgaudiosi@ahpis.com>; Moore II, Stu <smoore@hpix-ins.com>; Mellott, Curtis, J. <cmellott@hpix-ins.com>; Albright, Nancy <nalbright@hpix-ins.com>; Detweiler, Karl L. <kldetweiler@hpix-ins.com>; Fee, Richard F. <rfee@hpix-ins.com>; Gaudiosi, Nicholas S. <nsgaudiosi@ahpis.com>; Hammann, Emily Jordan <EJHammann@hpix-ins.com>; Jones, D. Scott <SJones@ahpis.com>; Marcavage, Robert J. <rmarcavage@hpix-ins.com>; Scheffel, Kristen <KMScheffel@hpix-ins.com>; Schleider, Brian S. <bsschleider@ahpis.com>; Toczylowski, Regina M. <RTroy@ahpis.com>; Trojecki, Joseph <JTrojecki@hpix-ins.com>; Zook, Tammy W. <TWZook@hpix-ins.com>"
        .CC = "Coleman, Karen A. <kcoleman@hpix-ins.com>; Sassano, Jennifer B. <jsassano@hpix-ins.com>; Burk, Michael J. <mburk@hpix-ins.com>; Capp, Rita <rcapp@hpix-ins.com>; Wentle, Nicole M. <nwentle@hpix-ins.com>"
        .Subject = "" & Forms!PXF7085!PXF7085A!SubClaimant & " vs " & Forms!PXF7085!PXF7085A!SubInsured & " / " & Forms!PXF7085!PXF7085A!ClaimNo
        strDate = IIf(CStr(Forms!PXF7085!PXF7085A!HearingDate & "") = "", IIf(CStr(Forms!PXF7085!PXF7085A!ArbitrationDate & "") = "", Forms!PXF7085!PXF7085A!MediationDate))
        .HTMLBody = "<table><tr><td align=left colspan=8></td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Claimant:</b></font> " & Forms!PXF7085!PXF7085A!Claimant & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Insured:</b></font> " & Forms!PXF7085!PXF7085A!Insured & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Group:</b></font> " & Forms!PXF7085!PXF7085A!GroupName & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Policy:</b></font> " & Forms!PXF7085!PXF7085A!Policy & "</td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Trial Date:</b></font> [b]" & strDate & "[/b] </td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Loss Date:</b></font> " & Forms!PXF7085!PXF7085A![Loss Date] & "</td><td width=10></td>" _
                    & "<td align=left colspan=3><font color=000080><b>Notice Date:</b></font> " & Forms!PXF7085!PXF7085A![Report Date] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Allegation:</b></font> " & Forms!PXF7085!PXF7085A!Allegation & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Indemnity Reserves:</b></font> " & Forms!PXF7085!PXF7085A!IndemRsv & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Venue:</b></font> " & Forms!PXF7085!PXF7085A!Venue & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Examiner:</b></font> " & Forms!PXF7085!PXF7085A!Examiner & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Plaintiff Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!PlaintiffFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Defense Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!DefenseFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Company Position:</b></font> " & Forms!PXF7085!PXF7085A![Company Position] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Judge:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Co-defense:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Update:</b></font> <br><br><br><br></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Case Summary:</b></font></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>For internal users, please click on the link below for more information:</b></font></td></tr></table> "
         .Display
         '.Send
    End With

Open in new window

Regards
0
 

Author Comment

by:mburk1968
ID: 39983260
See screenshot...
4-7-2014-10-31-34-AM.png
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39983475
HI,

let's try

With objMail
        'Set body format to HTML
        .BodyFormat = olFormatHTML
        .To = "Gaudiosi, Thomas S <tsgaudiosi@ahpis.com>; Moore II, Stu <smoore@hpix-ins.com>; Mellott, Curtis, J. <cmellott@hpix-ins.com>; Albright, Nancy <nalbright@hpix-ins.com>; Detweiler, Karl L. <kldetweiler@hpix-ins.com>; Fee, Richard F. <rfee@hpix-ins.com>; Gaudiosi, Nicholas S. <nsgaudiosi@ahpis.com>; Hammann, Emily Jordan <EJHammann@hpix-ins.com>; Jones, D. Scott <SJones@ahpis.com>; Marcavage, Robert J. <rmarcavage@hpix-ins.com>; Scheffel, Kristen <KMScheffel@hpix-ins.com>; Schleider, Brian S. <bsschleider@ahpis.com>; Toczylowski, Regina M. <RTroy@ahpis.com>; Trojecki, Joseph <JTrojecki@hpix-ins.com>; Zook, Tammy W. <TWZook@hpix-ins.com>"
        .CC = "Coleman, Karen A. <kcoleman@hpix-ins.com>; Sassano, Jennifer B. <jsassano@hpix-ins.com>; Burk, Michael J. <mburk@hpix-ins.com>; Capp, Rita <rcapp@hpix-ins.com>; Wentle, Nicole M. <nwentle@hpix-ins.com>"
        .Subject = "" & Forms!PXF7085!PXF7085A!SubClaimant & " vs " & Forms!PXF7085!PXF7085A!SubInsured & " / " & Forms!PXF7085!PXF7085A!ClaimNo
        
        If CStr(Forms!PXF7085!PXF7085A!HearingDate & "") <> "" Then
            strDate = CStr(Forms!PXF7085!PXF7085A!HearingDate)
        ElseIf CStr(Forms!PXF7085!PXF7085A!ArbitrationDate & "") <> "" Then
            strDate = CStr(Forms!PXF7085!PXF7085A!ArbitrationDate)
        Else
            strDate = CStr(Forms!PXF7085!PXF7085A!MediationDate)
        End If

        .HTMLBody = "<table><tr><td align=left colspan=8></td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Claimant:</b></font> " & Forms!PXF7085!PXF7085A!Claimant & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Insured:</b></font> " & Forms!PXF7085!PXF7085A!Insured & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Group:</b></font> " & Forms!PXF7085!PXF7085A!GroupName & "</td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Policy:</b></font> " & Forms!PXF7085!PXF7085A!Policy & "</td></tr>" _
                    & "<tr><td align=left><font color=000080><b>Trial Date:</b></font> [b]" & strDate & "[/b] </td><td width=10></td>" _
                    & "<td align=left><font color=000080><b>Loss Date:</b></font> " & Forms!PXF7085!PXF7085A![Loss Date] & "</td><td width=10></td>" _
                    & "<td align=left colspan=3><font color=000080><b>Notice Date:</b></font> " & Forms!PXF7085!PXF7085A![Report Date] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Allegation:</b></font> " & Forms!PXF7085!PXF7085A!Allegation & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Indemnity Reserves:</b></font> " & Forms!PXF7085!PXF7085A!IndemRsv & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Venue:</b></font> " & Forms!PXF7085!PXF7085A!Venue & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Examiner:</b></font> " & Forms!PXF7085!PXF7085A!Examiner & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Plaintiff Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!PlaintiffFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Defense Firm/Attorney:</b></font> " & Forms!PXF7085!PXF7085A!DefenseFirmAttorney & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Company Position:</b></font> " & Forms!PXF7085!PXF7085A![Company Position] & "</td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Judge:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Co-defense:</b></font> </td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Update:</b></font> <br><br><br><br></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>Case Summary:</b></font></td></tr>" _
                    & "<tr><td align=left colspan=8><font color=000080><b>For internal users, please click on the link below for more information:</b></font></td></tr></table> "
         .Display
         '.Send
    End With 

Open in new window

0
 

Author Closing Comment

by:mburk1968
ID: 39983639
Awesome! Thank You!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 87
Access2016 ignore hyphen in data entry or searches 5 70
sort Multi-dimensional array 6 45
make inkscape draw names programatically 5 21
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question