Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Conditional Statement in .HTMLBody

Posted on 2014-04-07
4
Medium Priority
?
463 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 53

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 53

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Suggested Courses

879 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