[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

VBA Conditional Statement in .HTMLBody

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
mburk1968
Asked:
mburk1968
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
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
 
mburk1968Author Commented:
See screenshot...
4-7-2014-10-31-34-AM.png
0
 
Rgonzo1971Commented:
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
 
mburk1968Author Commented:
Awesome! Thank You!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now