Solved

VBA Conditional Statement in .HTMLBody

Posted on 2014-04-07
4
421 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
  • 2
  • 2
4 Comments
 
LVL 49

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 49

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

770 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