Avatar of SteveL13
SteveL13
Flag for United States of America asked on

Loop in Access VBA code and make line not appear in email if criteria exists

This is the code I am working with below.    But for example, if rs.Fields("Qty2") is null then this should not appear in the email:

            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>

And if rs.Fields("Qty3") is null then this should not appear in the email:

 strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>

etc. for the other two quantities 4 & 5.

Here is my current code:

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            strBody = strBody & "Size: " & rs.Fields("Size") & "<br>"
            strBody = strBody & "Stock: " & rs.Fields("Description") & "<br>"
            strBody = strBody & "Colors: " & rs.Fields("InkColors") & "<br>"
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br>
            strBody = strBody & rs.Fields("Qty1") & " = " & Format(rs.Fields("TotalCostQty1") + rs.Fields("TotalCostQty1") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
<br>"
            
            rs.MoveNext
            
        Loop
    .HTMLBody = strBody & "</html>"
    .send

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Jim Dettman (EE MVE)

Paste this in you DB:

'---------------------------------------------------------------------------------------
' Procedure : IsBlank
' DateTime  : 09/07/2005 16:37
' Author    : WEC
' Purpose   : Tests a variable for Null or Zero-Length String
'---------------------------------------------------------------------------------------
Function IsBlank(ByVal varValue As Variant) As Boolean

          Const Routine = "IsBlank"
          Const Version = "1.0.0"

10        On Error Resume Next
          
20        IsBlank = True
30        IsBlank = (varValue = "" Or IsNull(varValue))

End Function

Open in new window


Then:

If Not IsBlank(rst.Fields("Qty2")) then
strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
End If

Jim.
SteveL13

ASKER
Almost perfect but I'm getting the "=" in the email for each line that should be blank..

--Steve
Jim Dettman (EE MVE)

I don't understand how....the entire line is skipped.

 Post what you have.

Jim.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SteveL13

ASKER
In a module (was that where is was supposed to put it?):

'---------------------------------------------------------------------------------------
' Procedure : IsBlank
' DateTime  : 09/07/2005 16:37
' Author    : WEC
' Purpose   : Tests a variable for Null or Zero-Length String
'---------------------------------------------------------------------------------------
Function IsBlank(ByVal varValue As Variant) As Boolean

          Const Routine = "IsBlank"
          Const Version = "1.0.0"

10        On Error Resume Next
          
20        IsBlank = True
30        IsBlank = (varValue = "" Or IsNull(varValue))

End Function

Open in new window


And in the onclick event of the command button: (this is all of the code in the onclick)

Private Sub cmdEmailEstimate_Click()

   Dim olApp As Object
   Dim objMail As Object
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strBody As String
   Dim strsql As String
   setdb = CurrentDb
   


   On Error Resume Next 'Keep going if there is an error
   Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

    If Err Then 'Outlook is not open
       Set olApp = CreateObject("Outlook.Application") 'Create a new instance
    End If
  'Create e-mail item
   Set objMail = olApp.CreateItem(olMailItem)

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = Me.txtContactEmail
     .Subject = "Estimate # " & [txtEstID] & " - Dated " & [txtEstDate]
     '.HTMLBody = "<htmltags>Regular Hours = " & RegHrs & "<br>Overtime Hours = " & OThrs & "<br>Total Hours = " & totHrs & "<br></htmltags>"
     strBody = "<html>Date: " & [txtEstDate] & "<br>Dealer: " & [txtDealerName] & "<br>Contact: " & [txtContactName] & "<br><br> Thank you for the opportunity to provide the following quotation: <br><br>"


   'Loop through query results starts here...
    Set rs = CurrentDb.OpenRecordset("qryEstimateDetailReport")

    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            strBody = strBody & "Size: " & rs.Fields("Size") & "<br>"
            strBody = strBody & "Stock: " & rs.Fields("Description") & "<br>"
            strBody = strBody & "Colors: " & rs.Fields("InkColors") & "<br>"

            strBody = strBody & rs.Fields("Qty1") & " = " & Format(rs.Fields("TotalCostQty1") + rs.Fields("TotalCostQty1") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            
            If Not IsBlank(rst.Fields("Qty2")) Then
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty3")) Then
            strBody = strBody & rs.Fields("Qty3") & " = " & Format(rs.Fields("TotalCostQty3") + rs.Fields("TotalCostQty3") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty4")) Then
            strBody = strBody & rs.Fields("Qty4") & " = " & Format(rs.Fields("TotalCostQty4") + rs.Fields("TotalCostQty4") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If
            
            If Not IsBlank(rst.Fields("Qty5")) Then
            strBody = strBody & rs.Fields("Qty5") & " = " & Format(rs.Fields("TotalCostQty5") + rs.Fields("TotalCostQty5") * rs.Fields("OverallMarkUp"), "##.00") & "<br><br>"
            End If
            
            rs.MoveNext
            
        Loop
    .HTMLBody = strBody & "</html>"
    .send

    MsgBox "This estimate has been emailed to the contact email address."
    End If
    End With

End Sub

Open in new window

Jim Dettman (EE MVE)

Steve,

This:

            If Not IsBlank(rst.Fields("Qty2")) Then
            strBody = strBody & rs.Fields("Qty2") & " = " & Format(rs.Fields("TotalCostQty2") + rs.Fields("TotalCostQty2") * rs.Fields("OverallMarkUp"), "##.00") & "<br>"
            End If

Open in new window


 Looks fine.  I don't see where the equals sign would be coming in.

 What I would suggest is:

1. put a STOP at line 40 of your code.
2. execute.
3. When you hit the stop, call up the debug window (Ctrl/G) and type:

 ? strBody

 and a return.   You'll see the body of the message as it stands.

 Press F8 to continue stepping through the code.   You can stop at any point and printout strBody again and you should see the code skip over the statement for the qty's that are blank.

 Press F5 to get out of step mode and continue execution.

Jim.
SteveL13

ASKER
I tried that and didn't seem to accomplish anything.  Here is a copy/paste of what I'm getting in the email:

Date: 5/1/2014  (this is not meant to be today's date and is ok)

Dealer: ABC Company
Contact: George Washington

Thank you for the opportunity to provide the following quotation:   (there is only one quantity in the first "group"

Size: #10
Stock: #10 Regular - 24lb. White Wove - Diagonal Seam
Colors: CMYK
250 = $42.80
=
=
=
=

Size: #10
Stock: #10 Regular - 24lb. Classic Laid - Antique Gray
Colors: Black
250 = $49.21
500 = $91.92
1000 = $177.33
2000 = $348.16
3000 = $519.00
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SteveL13

ASKER
Sorry, and I need the equal sings to go away on blank lines.
Jim Dettman (EE MVE)

<<I tried that and didn't seem to accomplish anything. >>

 In what way?   You should have been able to step through the code line by line with F8.   You should have been able to see it skip over putting a line in the e-mail if the qty was blank

 But it seems your qty fields must have something other than a zero length string or a null in them.

 Leave the stop in at 40, and when you hit it, bring up the debug window if not already up (Ctrl/G), and type:

? asc(rs.Fields("qty2"))

 followed by a return

also do:

? Len(rs.Fields("qty2"))

and tell me what you get.


Jim.
SteveL13

ASKER
Jim,

I have done what you suggested and get two errors.  I have attached two screen shots so you can see exactly what I am getting.
Asc Error
Len Error
Your help has saved me hundreds of hours of internet surfing.
fblack61
SteveL13

ASKER
Note... when I did ? asc(rs.Fields("qty2")) at line 200 I get an invalid use of null error.
SteveL13

ASKER
And at line 220 I get:

? len(rs.Fields("qty2"))
Null
Jim Dettman (EE MVE)

You got me...any way you can create a small sample DB with just the table(s) and the query your using to pull the data.

The last indicates that the field is NULL, which means the NZ()  (NUll to Zero) function should have worked fine.   I didn't understand why it's not.

Jim.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SteveL13

ASKER
Attached.  This has me more puzzled than you.
Sample-DB.zip
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question