My first function, looking for tips on format

[vapid2323]

Hey guys,

I just put together my first Function

Anyhow it works just fine but the email that it sends out can look kinda messy (see the quote below).

Site: SITE NAME
COI: 999999-9
Prepared By: John Johnny

Issues found in the SMR:
Error: This report was recived 9 days late.
<EMPTY LINE>
<EMPTY LINE>
<EMPTY LINE>
<EMPTY LINE>
Error: User Failed to add Monitored By field

Any tips on how to make the code not show the Null Strings when creating the emails?

Also any genral tips on my code? I know it works but I am sure I can improve.

Function SMRErrChk()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim dtToday As Date
    Dim dt24hLate As String
    Dim str24hLateErr As String
    Dim strSiteErr As String
    Dim VisitDate As Date
    Dim CompletionDate As Date
    Dim SiteName As String
    Dim PreparedByErr As String
    Dim MonitoredByErr As String
    Dim PreparedBy As String
    Dim MonitoredBy As String
    Dim COI As String
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim Sendemail As Boolean
 
Set db = CurrentDb
Set rst = db.OpenRecordset("qrySMRFormErrCheck", dbOpenDynaset, [dbSeeChanges])
'Set rst = db.OpenRecordset("tblSMRForm", dbOpenDynaset, [dbSeeChanges])
dtToday = Now()
str24hLateErr = "On-time"
Do While Not rst.EOF
VisitDate = rst!VisitDate
CompletionDate = rst!CompletionDate
SiteName = rst!SiteName
COI = rst!COI
PreparedBy = rst!PreparedBy
MonitoredBy = rst!MonitoredBy
Sendemail = False
'########################## Check for VisitDate VS Completion Date ########################
If DateDiff("d", VisitDate, CompletionDate) > 1 Then
    dt24hLate = DateDiff("d", VisitDate, CompletionDate)
    str24hLateErr = "Error: This report was recived " & dt24hLate & " days late."
    Sendemail = True
End If
'########################## Check for Site Name ########################
If SiteName = "Other" Then
    strSiteErr = "Error: Site Name Missing"
    Sendemail = True
ElseIf SiteName = "[Select Site Here]" Then
    strSiteErr = "Error: Site Name Missing"
    Sendemail = True
End If
'########################## Check for PreparedBy ########################
If PreparedBy = "[Enter your name and title]" Then
    PreparedByErr = "Error: User failed to add Prepared By in the form"
    Sendemail = True
End If
'########################## Check for MonitoredBy ########################
If MonitoredBy = "[Enter your first and last name here]" Then
    MonitoredByErr = "Error: User Failed to add Monitored By field"
    Sendemail = True
End If
'########################## SEND EMAIL ########################
If Sendemail = True Then
     ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")
   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add("NAME") 'd
      objOutlookRecip.Type = olTo
      ' Add the CC recipient(s) to the message.
      'Set objOutlookRecip = .Recipients.Add("")
      'objOutlookRecip.Type = olCC
      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Error report for " & COI & " " & PreparedBy
      .Body = "Site: " & SiteName & vbCrLf & "COI: " & COI & vbCrLf & "Prepared By: " & PreparedBy & vbCrLf & vbCrLf & "Issues found in the SMR:" & vbCrLf & str24hLateErr & vbCrLf & strSiteErr & vbCrLf & PreparedByErr & vbCrLf & MonitoredByErr
      .Importance = olImportanceHigh  'High importance
      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End If
MonitoredBy = vbNullString
MonitoredByErr = vbNullString
PreparedBy = vbNullString
PreparedByErr = vbNullString
SiteName = vbNullString
strSiteErr = vbNullString
rst.MoveNext
Loop
End Function

 

[pbaldy]

My tendency would be to build the body as I went along, like:

If PreparedBy = "[Enter your name and title]" Then
    strBody = strBody  & "Error: User failed to add Prepared By in the form" & vbCrLf
    Sendemail = True
End If

where strBody was a string variable eventually used in the email:

.Body = strBody

If you want to stay with your method, you can use an IIf() to test whether each variable contains anything, and only add the vbCrLf if it does.

 

[vapid2323]

Ahh, good tip! Thanks!

 

[pbaldy]

Happy to help!